Red Gate forums :: View topic - Foreign key issues after synchronizing production database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Foreign key issues after synchronizing production database

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Tue Nov 19, 2013 12:03 pm    Post subject: Foreign key issues after synchronizing production database Reply with quote

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)
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Thu Nov 21, 2013 10:57 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Thu Nov 21, 2013 3:34 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Admin
Site Admin


Joined: 15 Aug 2004
Posts: 9

PostPosted: Mon Nov 25, 2013 11:09 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Mon Nov 25, 2013 11:52 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Wed Nov 27, 2013 3:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Bastiaan Molsbeck



Joined: 26 Mar 2010
Posts: 48
Location: The Netherlands

PostPosted: Wed Nov 27, 2013 3:46 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group