Mutations in target database during long running migrations
Bastiaan Molsbeck
Posts: 73
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.
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.
Comments
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"?
Redgate Software
I was a bit afraid you were going to answer this. :-)
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?
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.
Redgate Software