October 31, 2009 at 4:05 am
Hello,
I would like to discuss the solution that Im currently working on and would welcome some alternative suggestion from expert in this group.
Live server runs on SQL 2000 with data intergrity maintained only in stored procedure not explicitly defined.
New server runs on SQL 2005 with explicitly designed data integrity. Only domain features are similar between two database. Data types have changed and few fields are deleted/added in new data base.
This makes the data synchronization bit more challenging. For my development purpose I have replicated live data base in read only mode on my developed machine. First step is a success after facing few issues. I have used triggers and stored procedure (to have baseline data) on this replicated table to update my new model and do my processing on new model and update the replicated live using triggers. For eg. trigger on replicated live updates the developed with shipping reference and development server does all processing like updating status of order etc and update the replicated live with order staus.
Though this current solution seems to work but one of our part time consultant pointed out that you might end up in cycling trigger in this situation and also more stored procedure and triggers to be written to make it happen. This is valid point to be noted as it had been exhausting to write all those storedprocedure for synchronization.
Alternatively he suggested me to a solution were instead of new model physical table , I should maintain view and that should do all the work for you like data synchronization (as always views are executed when queried) and data transaformation. This really looked very promising but extra fiels are new model needed to created as table and join has to be performed on view to have exact new model version. But i need to address the relational integrity and work out the limitation of view.
This has come to the point were my incremental delivery is due next week which is first delivery showing both old and new model working together for some christmas sales :-). Though alternative solution is promising it might take time and changing all previous development at last time is not advisable.
If you have read this so far and have some suggestion , I will welcome them.
Something keeping busy for weekend 🙂
Ta
November 2, 2009 at 4:36 am
I found this post useful and thought would try this solution
http://www.red-gate.com/Products/SQL_Comparison_SDK/case_studies/synchronizing_databases.htm
Ta
November 2, 2009 at 5:46 am
Well , I guess if I limit the insert\delete trgiger to my replicated live copy and just use update trigger from my new model I, dont have to worry about trigger cyclic.
Ta
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply