November 27, 2009 at 2:32 am
Hello Everyone,
New database we are working now have bunch of new tables and also contains old database tables with additional fields and new data types for old fields. New model is built on SQL server 2005 and old model is built on SQL 2000.
They have tasked me to do data synchronization. I have used replication + triggers to do all the transformation between old and new database.
Since our Live server cannot provide much of the resource for the replication we have with drawn the idea of using the replication + Triggers for the data synchronization.
New idea suggested will be using view which will act as a version of new database and couple them with staging table (which holds additional fields of new model) for integrating with the application.
I assume replication will perform much better than view concept , however cost of introducing new infrastructure might become expensive. I wanted to start a discussion how this new idea will support syncronization? Examples and experiences are welcome.
Ta
November 27, 2009 at 6:50 am
Just so I understand, you are essentially trying to take data from one db and synchronise it with your hybrid? If i'm right I dont think replication could ever have cut the mustard for you.
What I would suggest is set up a staging db and some triggers (as you thought). On the db you want to take data from, create the triggers so that each time a table that you want data from is updated (field, row etc) it triggers and sends the data to the staging db (which should be set with schematic duplicates of the tables, fields etc your taking out of the db.
Then you would set a bunch of sp's to periodically flush staging to the hybrid and perform a compare. Then assuming the compare completed, cleanup your staging db.
We do this with our reporting repository and flush staging to repo once a day (fine as long as your hybrid can go hours between syncs).
If I am wrong and all you need is a standby, then use mirroring.
HTH - Have a nice weekend 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 27, 2009 at 7:08 am
Hi Adam,
Thank you for your suggesstion. However, we cannot put the trigger on Live server. This is one of the reason for using the replication. I have used the replicated database and installed the trigger on that database to synchronize my hybrid. I have also used bunch of stored procedure when initial snapshot was taken.
Have a great weekend.
Ta
November 27, 2009 at 9:15 am
So your replicating your production to then trigger your replica to then populate your hybrid.!?
Sounds a bit long winded. Obviously you cant test this stuff in production, but this is not a good long term solution.
For example when replication fails (and it will) and you need to reinitialise your replica, your going to have to compare the data from your prod, replica to your hybrid and then recreate your triggers for the hybrid population while trying not to miss any replicated data (which may occur between the re-sync and the trigger enabling).
It should work but your creating a rod for your back by using replication.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 27, 2009 at 11:47 am
Hi Adam,
Yes I share your concern. Do you any other alternative in your mind?
Ta
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply