Foreign key issues after synchronizing production database

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Foreign key issues after synchronizing production database

Postby Bastiaan Molsbeck » Tue Nov 19, 2013 12:03 pm

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)
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Postby Brian Donahue » Thu Nov 21, 2013 10:57 am

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Bastiaan Molsbeck » Thu Nov 21, 2013 3:34 pm

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.
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Postby Admin » Mon Nov 25, 2013 11:09 am

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.
Admin
Site Admin
 
Posts: 9
Joined: Sun Aug 15, 2004 10:44 am

Postby Bastiaan Molsbeck » Mon Nov 25, 2013 11:52 am

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.
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands

Postby Brian Donahue » Wed Nov 27, 2013 3:43 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Bastiaan Molsbeck » Wed Nov 27, 2013 3:46 pm

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.
Bastiaan Molsbeck
 
Posts: 48
Joined: Fri Mar 26, 2010 11:12 am
Location: The Netherlands


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests