How are "Foreign keys" and "Triggers" handled?

Jun 28, 2013 at 1:10 AM
Hi

First I must appreciate your great effort. Your tool has a lot of nice features, performance is one them. Great job.

I was wondering how does your tool handle the "Foreign Keys" and "Triggers" in the Migration Wizard and the BulkUpload utility. I am interested in migrating this way:
1- Migrate the schema without FKs and Triggers.
2- Migrate the data.
3- Migrate the FKs and Triggers.

Is there a parameter in your configs or switch to handle it in such a way?

Thanks
Tariq
Coordinator
Jun 28, 2013 at 3:51 PM
Hi,

Thanks for your kind words! In regards to how I handle "Foreign Keys" and "Triggers", here is what I did. As I create the script of the database, I capture all "ALTER TABLE" commands and save them for the end of the schema migration process and then migrate the data. So, from your example above, I have:

1- Migrate the schema without FKs and Triggers.
2- Migrate the FKs and Triggers.
3- Migrate the data.

I don't have a way for you to specify the order at this time. BUT, what you can do is edit the generated script before you run against your target database. You will notice that at the end of the export process there is a "Results Summary" screen with two tabs (Result Summary and SQL Script). Select the SQL Script tab and then save the script to a file (or just copy everything to buffer and then paste into your favorite editor). In the TSQL Script, the first part of the script is all about CREATE. Do a search for "ALTER TABLE" and this will take you to the second part of the script. This will be where you want to migrate the data before the ALTER TABLE commands are run. Ok, now for the data. Go to the bottom of the TSQL Script and you should see something like this:

-- BCPArgs:10:[dbo].[Employees] in "c:\SQLAzureMW\BCPData\dbo.Employees.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:830:[dbo].[Orders] in "c:\SQLAzureMW\BCPData\dbo.Orders.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:2155:[dbo].[Order Details] in "c:\SQLAzureMW\BCPData\dbo.Order Details.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:4:[dbo].[Region] in "c:\SQLAzureMW\BCPData\dbo.Region.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:53:[dbo].[Territories] in "c:\SQLAzureMW\BCPData\dbo.Territories.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:49:[dbo].[EmployeeTerritories] in "c:\SQLAzureMW\BCPData\dbo.EmployeeTerritories.dat" -E -n -b 10000 -a 16384
GO

This is how I tell SQLAzureMW that I have data to upload. You can take all of the -- BCPArgs lines (with their GO statement) and move them just before the first ALTER TABLE statement that you found above. Once done, you can copy the script and put it back in SQLAzureMW TSQL Script box and then execute. If you have exited SQLAzureMW, no problem. Just save your modified script to a file and then run SQLAzureMW again. But this time, tell it to run TSQL without analyzing and select your saved file.

Anyway, with the modified script, you should be able to do exactly what you want. If you have any questions, let me know.

Regards,
George
Jun 28, 2013 at 4:12 PM
Thanks for the detailed explanation George. That perfectly is the answer to my question. But now I know that my question itself was trivial and lack of my knowledge about BCP. I had a wrong impression that BCP doesn't take care of the Triggers and FKs.

Thanks
Tariq