Migration scripts
Published 31 July 2019
SQL Change Automation projects use a migration script driven approach to deployment (as opposed to the model driven approach of SQL Source Control or SSDT projects). Migrations capture a set of incremental changes to your database and you can then use those scripts to deploy to your other environments in a consistent way. The same scripts will be run in the same order on each environment, giving you repeatable deployments through to production.
Deployment
When deploying a SQL Change Automation project, each migration is checked and executed only if it has not run on the target database before. Each database keeps track of which migrations have been run against it. This is recorded in the migration log table.
To ensure that your project stays synced with your development, test and production environments, we strongly recommend following the immutability principle with migrations. Once a migration has been deployed, the script file should not be removed from the project or otherwise modified. For example, if a migration that adds a column has already been shipped to the test environment, but you later decide the column wasn't needed, then a new migration should be added to the project to drop the column, rather than removing/modifying the original file.
When a migration has not yet been deployed to any environment other than your own it should be safe to delete the script. For example, if the migration was generated directly from your development database, or you wrote the migration yourself and only applied it locally. You then have the option to regenerate the migration (or write a new one by hand).
Configuration
Folder structure for migrations
Sub-folders can help organize your migration scripts in ways which match your workflow.
Note that file prefixes need not be unique. This means that scripts can also be inserted into earlier locations within a folder without renaming all scripts which follow.
You cannot have both scripts and folders together. Each folder either just holds other folders or just contains migration scripts.
Name
Migration scripts can be renamed at any time, even if the script has already been deployed. This is possible because the script is always referred to by its ID, rather than the script's filename. Aside from the fact that the filename must start with a number, and end with .sql, the rest of the file content is up to you. SQL Change Automation will auto-generate a filename when the migration is added to your project, however you can then change it as wanted (for example, 0005_20170413-1318_joe.bloggs.sql can be renamed to 0005_Alter-Customer-Add-PhoneNo.sql)
ID
The header of your migration scripts contain a unique identifier for the migration. To prevent a migration being executed more than once, the ID within your migration script must be preserved. The value of the ID does not have an effect on deployment order.
Deployment order
Deployment order is determined by file path, taking into account both folder path and file name. To adjust the order of deployment, change the file's numeric prefix. If two migrations have the same number, then the rest of the filename will be used as the tie-breaker. Unless the script is renamed, the suffix will contain the date and time that the migration was originally added to the project, plus the username, for example, 20170413-1318_joe.bloggs.
As SQL Change Automation sorts the prefix as a number, rather than an alpha-numeric string, consistent padding must be used for the numeric prefix (001, 002, etc). This also makes it easy to view the order of deployment directly in the file system.
To avoid causing the project to go out-of-sync with other environments, we recommend not changing the deployment order after a migration has been deployed outside your local development environment.
Transaction handling
By default all of your project's migrations (and programmable objects) will be deployed within a single user transaction. You can disable automatic transactions by setting the TransactionHandling attribute in the migration metadata to Custom. See transaction handling for more information.
-- <Migration Id="{UniqueIdentifier}" TransactionHandling="Custom"/>Controlling execution across different environments
If you need to have some more control on how these scripts are executed across different environments you can use variables. These can allow you to run certain statements only on certain environments.
Alternatives to migrations
If you would like to have your migration executed with each modification to the script file, try using programmable objects (useful for deploying replaceable objects such as stored procedures, functions and views).
Alternatively, if you would like to perform an action each time you deploy the project, try using a pre or post-deployment script instead. These can be used for creating the database if it doesn't exist, altering database settings, or for static data.
