Red Gate forums :: View topic - Mutations in target database during long running migrations
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Mutations in target database during long running migrations

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Tue Nov 01, 2011 3:04 pm    Post subject: Mutations in target database during long running migrations Reply with quote

Hi,
I have an issue that I would like some suggestions for:

I am creating an application that uses the SQL Comparison SDK 8 to compare two databases, and synchronize them after the comparison.

But when the database is large, this synchronisation process takes a lot of time, for instance 30 minutes.
Now a problem occurs when someone makes some mutations in the target database during these 30 minutes. For instance, adds records which conflict with the migration SQL (like on identity columns or unique key constraints).

Okay, the simple solution to this is just say "Do not mutate the target database during the synchronisation", but what if that is not an option?

Is there anything I can do as a developer to prevent this? Like adding an "IF NOT EXISTS" to all insert statements, etc?

Thank you in advance for your reply.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Wed Nov 02, 2011 4:21 pm    Post subject: Reply with quote

Unfortunately there isn't much that can be done. The synchronization script created internally is a result of the comparison that took place - if the databases are subsequently changed either before or during the sync process, then obviously the validity of the script cannot be guaranteed.

Are these data changes or schema changes that are being made? SQL Compare (and thus the SDK) will support drop/create rather than alter for stored procedures in 9.5 but I don't think there's an equivalent for the Data Compare side.

So really, the short answer is to not allow any other changes to the DB during the sync process, maybe by scheduling it to run "out of hours"?
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Nov 02, 2011 5:00 pm    Post subject: Reply with quote

Thank you for our reply!

I was a bit afraid you were going to answer this. Smile
The changes are data changes, by the way.

I indeed already suggested that the sync process needs to be scheduled at night, to prevent this from happening.

I also was thinking in changing the synchronisation process, that not the entire database is synced in a single batch, but table by table.
This should minimize the chance that this error occurs.
What do you think?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Wed Nov 02, 2011 5:03 pm    Post subject: Reply with quote

You could certainly amend your SDK project to compare just one table and then sync it.
The only issue here is if you have tables that have relationships between them. For instance, you won't be able to insert a bunch of records in to "OrderDetail" when there's no related parent "Order" record for example.

But if you can establish a logical subset of tables that could all be synced together, this may work.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group