SQL Data Compare 14 - How to sync DBs with Foreign Key Dependencies

Jeremy_T

I'm trying to sync a QA database down to DEV to reproduce an issue, and I'm not sure how to avoid orphaned or dirty keys based on these types of warnings after the compare runs:

 "Undeployed tables have foreign keys to table [dbo].[TableName]; the deployment script may fail when the keys are reinstated.  To skip these integrity checks and leave the database without referential integrity, use "Skip integrity checks for foreign key constraints" under Deployment Behavior." 

In my case I've got 4 tables out of a couple dozen that needs sync'd that show this warning. Is there a way to have the tool also choose the other tables with depends automatically, so that they also get sync'd over? I'd prefer not to skip the integrity checks. 

Thanks,
Jeremy

Alex B

Hi @Jeremy_T,

If you are using the SQL Compare UI, it should prompt you during the deployment wizard that there are dependent objects that need to be deployed and gives you the option to include them as well - see this page https://documentation.red-gate.com/sc/deploying-data-sources/using-the-deployment-wizard#UsingtheDeploymentWizard-Reviewdependencies (3.Review dependencies) for an image and further information.

On the command line IncludeDependencies is one of the default options, but if you have specified any options the defaults are no longer used and you would then also need to include the "IncludeDependencies" as well.

Kind regards,
Alex

Jeremy_T

Hey @Alex B,

My question was about moving data using SQL Data Compare, not bringing over schema/structure changes via SQL Compare. Both databases are already identical since dev is just a restore from a database backup of QA. What I'm wondering is how to bring down all new or edited data from qa to dev without having to worry about ending up with dirty FKs. Is there an option in SQL Data Compare to just overwrite all data on the target DB from the source easily so that they match completely, that I'm missing in the UI?

Thanks,
Jeremy

Alex B

Apologies, not sure where my brain was there.  There isn't an "include dependencies" option in SQL Data Compare.

As for making sure everything is overwritten, you would just compare all tables and then deploy - this should synchronize the source and target - this would add new data, modify existing different data and drop and data on the target that wasn't on the source. 

It looks like you may be using the comparison option to "Skip integrity checks for foreign key constraints" which, if you turned it off, I think would cause the process to fail if things were in an inconsistent state when the FK were being re-enabled after the inserts as long as you were also not using the option to not use transactions.

PeterH

Is there any update on this capability? I just bought SQL Data Compare, and want to push production data to a staging database.

I would like the tool to work a bit harder and figure out which tables are at the bottom of the dependency tree and update those first, then work its way back up so that the "lookup tables" get their updates first, and thus FK references are always kept in place during the update process.

Is this on the list of things to do, or maybe it's already been done? (*crosses fingers*)

Paul_H

I would also be interested in this.

PeterH

@Paul_H

I've found a very simple work-around. The script generated puts all the inserts at the end, so update statements fail because they're happening before the rows to which they sometimes refer are created.

So I take the option to export the deployment script to SSMS, then cut and past all the inserts (handily all blocked together) to the top of the script, so all new rows are created first, and thus available as FK references to later updates. Given it all runs in a transaction, I'm unclear why this isn't the default behaviour, but that's way above my pay grade. This means sync scripts run 100% reliably every time.

Hope this may help you.