Hi, I'm using your SQL Comparison SDK in a tool to synchronize a production database periodically to a test database.
The production database contains 185 tables and is around 20 GB in size. Most of the tables are linked to other tables using foreign key relations.
Because of the size of the database, the step about comparing the tables from the source with the destination takes a few hours. I use the method "CompareDatabases" of the class "ComparisonSession" for his.
I added visual feedback about the compare progress in the tool by using the "StatusEventHandler" delegate. By this I can see that the comparison is done table-by-table, alphabetically.
After the comparison is complete, a synchronization script is generated by using the method "GetMigrationSQL" of the "SqlProvider" class.
Finally, this script is executed on the destination database by using the method "ExecuteBlock" of the "BlockExecutor" class.
Pretty straightforward, just like your examples, I think.
But after executing the synchronization script, the destination database contains some foreign key issues.
I found out that this is caused by records that were added to tables of the production (source) database during the comparison step, and for which these records have foreign key relations to tables that were already compared (because the tables are compared alphabetically?).
How can I address this issue?
One possible solution would be to first create a full backup of the production database, and use that as the source of the comparison.
But I was hoping I missed something in your API, which could address this issue without having to create full backups, because this takes a lot of time and disk space.
Some extra information:
- All tables are included in the table mappings
- I enabled the SQL options "DisableKeys", "DropConstraintsAndIndexes", "DisableTriggers", "DDLTriggerDisable" and "ReseedIdentity"
- I use the comparison SDK patch version 10.0.0.170 (file version of "RedGate.SQLDataCompare.Engine.dll" is 10.2.4.113)