Suggest approach to replication strategy for reporting database SQL2000.

SQL 2000 is already a long way ago.  Any chance this could be a trigger to upgrade to lets say 2008?

Hi Jogos,

Yep, there is a proposal on the table for an upgrade :-)

Are you saying that because it would be easier / neater / more elegant to do in 2008?

Thanks

Des

Why 2008 or even 2012? You start something new where newer versions have more advanced functions for or simply more evolved systems.

If you were trying an script-way then 2008 has a the advantage of the MERGE-command
is made for synchronising data
http://msdn.microsoft.com/en-us/library/bb510625.aspx

Look also at transactional replication
http://msdn.microsoft.com/en-us/library/ms151198(v=sql.105)

Hi Jogos,

I don't understand your 1st sentence. I currently have SQL 2000 and that’s what this question is about.

So, I can’t take advantage of the MERGE command.

As for Transactional Replication, what happens to the Reporting table when a truncate is issued on the Staging table, if that’s even allowed when the Reporting table is a subscription replicant?

If you believe this is the answer could you say a little more about how this solution would work?

<<Are you saying that because it would be easier / neater / more elegant to do in 2008?
>>
Yes, for example the MERGE-command where you can decide to only implement insert and update and maybe for delete-event update a deleted-flag in the reporting table.

Ok, so now we've established it would be easier in 2008!

It'd also be easier if I could spend a $100,000 on training or employ teams of folk to do this for me, but I can't, so I use EE ;-)

So getting back to the question, how about the Replication solution you mentioned, could this be a solution in 2000?

<<Yep, there is a proposal on the table for an upgrade :-)>>
So my suggestion is don't play with the 2000-rules now (when it can be easier/faster/cheaper) and having it to convert again it in the near future and again spend more time/money on it .... and still have an old school solution. Not able to use relevant features of sql2005, 2008, 2008R2 or even 2012.

Other examples of introduction of 2 new relevant features in 2008 for capturing/transfering changes
Change Data Capture and Change Tracking compared
http://msdn.microsoft.com/en-us/library/cc280519(v=sql.105).aspx

<<I have tried many approaches but all failed for one reason or another. >>
Maybe you can make a short overview and the main problems you had.

Note: you talk about truncation of tables, that's also not allowed when it is referenced by a FK-relation.

It would appear to me that you have these options:
1. Transactional Replication.
2. Log Shipping.
3. Another approach would be to use a Third Party tool such as Red-Gate's SQL Backup to keep the two databases in sync.

But this may depend on your Edition of SQL Server 2000 and whether the databases are on the same server or not.

Hi acperkins,

Thanks for your reply.

1. I have avoided Transactional Replication because of the need to truncate the tables in the Staging database. Does this requirement mean TR is completely out or are there work arounds?

2. I don't know about this and will investigate.

3. Not familiar with this product but will also investigate.

Both databases are on the same server which is SQL Server Standard Edition (8.00.2055(SP4))

Thanks again,

Des

I have avoided Transactional Replication because of the need to truncate the tables in the Staging database.
I am not following you here.  Why do you have to "truncate the tables" ?  All you have to do is define a database as the publisher which will replicate the schema and data to a database you have defined as the subscriber.

As per my OP I need to have the freedom to truncate / insert / delete the tables in the Staging database. As I understand it, that's not possible if the Staging database is a publisher.

Or are you saying you don't understand why I have to truncate the tables? If so, it’s not necessary for you to understand. It’s just a fixed point that’s non-negotiable as part of the solution to this question.

In order to stay focused on the element of the question that is important I have intentionally set some "fixed" constraints. As you can probably imagine the real set up is much more complicated than the scenario I have presented here. Again, I have done this to avoid adding extraneous information into the question. However it does seem that everyone wants to move the goal posts and answer along the lines of "Well if we start by changing the question, then the answer becomes much easier!"

So that I might get closer to an answer perhaps I should layout what I think would be a good solution and then perhaps someone can tell me if this is possible or not. It might also highlight where I am having difficulties.

My ideal solution:

1.      Staging database tables are modified at will and without restriction
2.      On a routine basis a “snapshot” of the Staging database is created.
3.      This snapshot does not include all the transactions required to get the tables into their current condition, just their final state.
4.      This snapshot is compared to the static tables in the Reporting database and inserts / deletes / updates are applied to the tables to make them match the Staging tables.

The reason I don’t use simple “Snapshot Replication” is that the tables in the Reporting database are truncated before the new data is applied and this breaks my “Constraint 1” – or is my understanding wrong?

I could write update / delete / insert statements for every field of every table to perform what would be a “manual merge” but the workload involved seems immense. If there is an easy way to perform this task in SQL 2000 I’d love to hear it! Are there scripting tools that can assist?

For clarity, the reason that the tables are in different databases was simply to allow for some form of replication but as that currently seems to be a non-starter, the tables could just as easily be in the same database if that would make things easier.

Finally, I understand that by setting constraints I am making things harder. If, ultimately, there is no way to achieve this task then I will accept that – It’s an allowable answer!

As per my OP I need to have the freedom to truncate / insert / delete the tables in the Staging database. As I understand it, that's not possible if the Staging database is a publisher.
You can always do a TRUNCATE, INSERT and/or DELETE in tables in a Publisher.  What would be the point otherwise of Replication?

Or are you saying you don't understand why I have to truncate the tables?
No I never said that or implied it.  That is standard.  Why would I question that?

However it does seem that everyone wants to move the goal posts
Can we get beyond this?  I sensed your frustration from the start, and that is why I decided I would contribute to this thread.  If you cannot get over it, I will just move on.

The reason I don’t use simple “Snapshot Replication” is that the tables in the Reporting database are truncated before the new data is applied and this breaks my “Constraint 1” – or is my understanding wrong?
Your “Constraint 1” has to do with the Publisher and not with the Subscriber, so i am not following you here.  With Transactional Replication all modifications are done on the replicated tables in the Publisher and no changes are made on the Subscriber.

To be honest I have never used Snapshot Replication, so it may well be what you need.  What I have used and am suggesting is Transactional Replication.  This will keep the tables continually in sync.

You can always do a TRUNCATE, INSERT and/or DELETE in tables in a Publisher.
According to Microsoft:

Why can't I run TRUNCATE TABLE on a published table?
TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables. (This is an extract from: http://msdn.microsoft.com/en-us/library/ms151740.aspx )

Which leaves Snapshot Replication. I have tried SR and the behaviour I saw was that when the snapshot is applied to the Subscriber Database the 1st action taken is for the table to be truncated and then the snapshot is inserted into the empty table. This is why I can’t use Snapshot Replication.

I have started to investigated Log Shipping which does look interesting but as I only have the Standard Edition of SQL2000 it’s not a native feature so I will need to use the Simple Log Shipper from the SQL Server 2000 Resource Kit. Although having read a number of articles it looks as though I’m going to have to do some fancy foot work to make the same instance act as both primary and secondary server …

As for my “manual merge” solution I mentioned, is there a good scripting tool that could help me achieve this?

TRUNCATE TABLE is a non-logged operation that does not fire triggers
You are absolutely right.  I had forgotten about that.

Which leaves Snapshot Replication. I have tried SR and the behaviour I saw was that when the snapshot is applied to the Subscriber Database the 1st action taken is for the table to be truncated and then the snapshot is inserted into the empty table. This is why I can’t use Snapshot Replication.
And here I am not following you.  The TRUNCATE TABLE limitation is on the Publisher and not the Subscriber.

I have started to investigated Log Shipping which does look interesting
I suspect you are going to have the same problem with TRUNCATE TABLE when using the native Log Shipping for much the same reason.  But I could be wrong.

Good luck.

This was the only solution that met the requirements.