Avatar of Austrian_Des
Austrian_Des
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Suggest approach to replication strategy for reporting database SQL2000.

Hi All,

I've struggled to think of a sensible, descriptive title for this question but hopefully the following explanation will help!

I am using SQLServer 2000.

I have 2 Databases.
The 1st database (I call it ‘Staging’) contains about 30 tables.
The 2nd database (I call it ‘Reporting’) contains about 200 tables.

The 30 tables in Staging are identical in structure to their counterparts in Reporting.

My objective, on a routine basis (hourly would be fine), is to make the 30 tables in Reporting contain the same rows of data as in Staging.

The constraints on this are as follows:

1.      The Reporting tables must always contain data. The data can be out of date but the tables must never be empty.
2.      The Staging tables must be allowed to be updated, inserted, deleted and truncated as often as required.

I would like to know the best way to approach this issue. I have tried many approaches but all failed for one reason or another. I intentionally won’t list the approaches I have tried as it could be that my approach was correct but the implementation was wrong. I’d like this question to start from a clean sheet.

Thanks

Des
Microsoft SQL Server

Avatar of undefined
Last Comment
Austrian_Des

8/22/2022 - Mon
jogos

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

ASKER
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
jogos

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)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Austrian_Des

ASKER
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?
jogos

<<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.
Austrian_Des

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jogos

<<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.
Anthony Perkins

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.
Austrian_Des

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anthony Perkins

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.
Austrian_Des

ASKER
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!
Anthony Perkins

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Austrian_Des

ASKER
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?
Anthony Perkins

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.
ASKER CERTIFIED SOLUTION
Austrian_Des

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Austrian_Des

ASKER
This was the only solution that met the requirements.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23