|
|
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.
Note |
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.
|
|