Foreign key issues after synchronizing production database

Bastiaan Molsbeck

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)

Brian Donahue

We need to know what the foreign key issues are, and how you are using the SDK. Presumably you are trying a two-way sync and this is very difficult and requires a bespoke solution to decide how to deal with record conflicts, etc.

If you can describe how your sync process works (and how you *want* it to work) we can probably come up with something, but like the last couple of issues you've had I'd imagine this is going to require some consultation.

Bastiaan Molsbeck

The foreign key issue is simple: after the synchronisation when the foreign keys are created again, an error occurs about that a foreign key relationship cannot be created because of a missing parent record.
Thus a child table contains a column, containing a value which would reference to the primary key of a parent table. But that particular row of the parent table wasn't synchronized.

The cause for this is that the parent table was already compared and before the child table is compared, new records are inserted into both the parent and child table.
This happens only with large databases, because then the time to compare the tables take some time in which the source database can be modified.

Note: since the last issues I had, I changed the code to synchronize the database at once, NOT table by table like I wrote in earlier posts.
This works perfectly, until the source database is modified during the compare step of the synchronisation.

PS: it is also possible the other way around: when a parent record is deleted from the source, after the child table already was compared but the parent table not.

[Deleted User]

SQL Data Compare Engine updates data one table at a time. If there is any way to enforce referential integrity, it's by making sure all tables in the relationship are synchronized. If you've done that, I can't think of any reason why you would have this problem except that the data in the source was already violating the constraint or some sort of filter (SelectionDelegate) was in place to stop the synchronization of all records.

Bastiaan Molsbeck

Yes, all tables are included in the synchronisation.
No, the data was not already violating the constraint before synchronizing.

The reason why this problem occurs is that the comparison step takes some time, and the source database is being modified DURING this comparison.

Brian Donahue

The synchronization can't work in this environment. You're going to have to prevent updates from happening while the sync is running, or implement some sort of system that filters out records newer than the time of the start of the comparison.

Bastiaan Molsbeck

Thank you for your reply.
Now I know what the limitations are of the SDK regarding this matter.

I'll investigate what solution I will use for this issue.

Regards.