Generating Migration ID for custom migration script

jaydarrenmiller

We are not quite ready yet to use the Include Table Data option in SCA, but I do have some scripted data changes that I would like to deploy using SCA.  My plan is to just add the script as the next numbered migration file.  My question is if the Migration ID in the comment at the top of the generated migration files is simply a GUID.  Can I just include a new GUID at the top of my custom migration file and have SCA pick it up appropriately for deployments?

Thanks,
Jay

danielpottenger

Hey @jaydarrenmiller ,

It's possible to do that, as long as you're using a GUID at the top of the migration script.
You're also able to seed the database using migration scripts too, though depending on the number of INSERT statements, you'll find that deployments may take longer.

You can also rename the migration script files themselves, if required, as long as they contain the numbered prefix, as SQL Change Automation uses that to determine the execution order of the scripts.

There is a bunch more information on the following page, that might be useful for you:
https://documentation.red-gate.com/sca/developing-databases/concepts/data-population/strategies-for-data-population

If you need any more help, feel free to reach out,

Many thanks,
Dan

danielpottenger

Hey @jaydarrenmiller ,

The migration scripts support data insertion, and you should be able to do this just fine, as long as the migration scripts have a valid GUID at the top.

You're also free to rename the scripts, as long as they contain the numeric prefix (i.e 001, 002, 003), as SQL Change Automation uses the prefix to determine the execution order.

Once you've applied your scripts, you can check that the __MigrationLog table within your database contains the GUID that you've used, and that both your development database, and your shadow database contain the data as expected.

For a little bit more information on this, and some potential alternatives, you might find the following link a good source:
https://documentation.red-gate.com/sca/developing-databases/concepts/data-population/strategies-for-data-population.

If you need any more help, feel free to reach out,

Many thanks,
Dan

jaydarrenmiller

Thanks Dan, this is what I needed for now.  We have been using SCA for schema and are now starting to look at using it for some data as well.  How would I go about baselining the data in a project that we already have a baseline for the schema?

danielpottenger

Hey @jaydarrenmiller ,

If a baseline script has already been generated for your schema and applied to your production databases, you can add another one within the baseline folder.

If you do this directly using our tool, it should also generate you a file name for the migration (i.e 002_20201006-1620_Joe.Bloggs.sql), and a migration id within the file.

From in SSMS, on the migrations tab, right click the baseline folder and click 'Add migration'.
From within Visual Studio, right click the baseline folder, then 'Add' -> 'New Item'.

You can add any of the statements you need to run,  to the new baseline migration script, and then when you deploy the project to your production databases, the new baseline script should run (which can be verified by checking the migration log table).

Many thanks,
Dan