Scripting Data Only Disables All Constraints

Apr 19, 2011 at 5:31 PM

When using the Migration Wizard only to script out all data (in Advanced Settings, "Script Table / Data" is set to "Data Only") and then apply that script data to the target database on SQL Azure, all constraints including foreign keys are disabled (WITH NOCHECK). The tool doesn't seem to make any attempt to turn them back on. The data seems to be applied in dependency order, so I don't see why the Data Only option should manipulate the constraints anyway.

Coordinator
Apr 24, 2011 at 12:08 AM


Ok, I understand. Yes, this is a BCP issue I think, which you can fix in the SQLAzureMW.exe.config file. Here is the link to BCP documentation: http://msdn.microsoft.com/en-us/library/ms162802.aspx

and specific information:

CHECK_CONSTRAINTS
Specifies that all constraints on the target table or view must be checked during the bulk-import operation. Without the CHECK_CONSTRAINTS hint, any CHECK and FOREIGN KEY constraints are ignored, and after the operation the constraint on the table is marked as not-trusted.
NoteNote
UNIQUE, PRIMARY KEY, and NOT NULL constraints are always enforced.
At some point, you will need to check the constraints on the entire table. If the table was nonempty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK constraints to the incremental data. Therefore, we recommend that normally you enable constraint checking during an incremental bulk import.
A situation in which you might want constraints disabled (the default behavior) is if the input data contains rows that violate constraints. With CHECK constraints disabled, you can import the data and then use Transact-SQL statements to remove data that is not valid.

In the SQLAzureMW.exe.config file, find this:

<add key="BCPArgsIn" value="{0} in {1} -E -n -b 10000 -a 16384"/> <!-- BCP in command parameters -->

 

change it to look like this:

<add key="BCPArgsIn" value="{0} in {1} -E -n -b 10000 -a 16384 CHECK_CONSTRAINTS"/> <!-- BCP in command parameters -->

 

and you should be good to go.

May 29, 2013 at 3:17 PM
The migration wizard is an excellent tool, but we ran into a similar issue. I think it would be important to warn users about this.

In our case all FKs in our database were (presumably) removed and then recreated with the NO CHECK option, so they because untrusted in Sql Server.

The result was that we saw much poorer query performance for a while, until we ran a schema compare on the original source and resultant migrated databases. If we had not done this schema compare we would have remained completely unaware that, in effect, the wizard was the source of our performance drop.

We recreated all our FKs with the CHECK option to restore them to trusted status, and performance picked up.

I would not be surprised if there were many databases now hosted in Sql Azure which are still seeing this unnecessary performance drop. At the very least, this should be well documented for future users.
Coordinator
May 29, 2013 at 7:34 PM
Hi,

SQLAzureMW should not drop FK constraints. Were the constraints missing or just turned off by BCP? The info you provided is great to know. As a side note, one of the reason I went with BCP default and did not add "CHECK_CONSTRAINTS" to the command was because of SQL Azure throttling (especially on large datasets). As far as advice, I can add a "Check Constraints" to the Advanced Options and in the description of the options, I can add your comments around performance. Would this help?

Thanks for the feedback,
George