Mutations in target database during long running migrations

Bastiaan Molsbeck

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.

James B

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"?

Bastiaan Molsbeck

Thank you for our reply!

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?

James B

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.