How migration scripts work
Published 18 February 2016
To make sure the same migration script isn't run more than once on the same database, deployments are recorded per database in the table RedGateLocal.DeploymentMetadata. To learn more, see RedGateLocal.DeploymentMetadata.
The deployment order file (DeploymentOrder.json) defines how a deployment script is generated. By default, changes are deployed in the order they're committed to version control. If a single commit includes schema changes and a migration script, the migration script is deployed first.
A deployment script that involves migrations consists of compare blocks and migration blocks:
- Compare blocks are automatically generated. They update the schema in the target database to reflect its state when the migration script was created.
- Migration blocks contain the SQL you've written to handle specific changes.
SQL Server doesn't allow some statements to be executed in transactions (see MSDN articles: Transact-SQL Statements Allowed in Transactions and Transactions in Memory Optimized Tables).
To use these statements in migration scripts, disable transactions when generating deployment scripts in SQL Compare. To learn more, see this example.
The deployment order file
The deployment order file (DeploymentOrder.json) defines the order that migrations are added to the deployment script. By default, the deployment script is composed of changes and migration scripts in the order they were committed to version control.
You can edit the deployment order file to manually change the order changes are deployed. This is necessary to resolve a merge conflict.
In the file, migrations are labelled with the migration script name (eg Rename tblUsers to tblCustomers). Each migration is represented in two lines, where each line describes a block of changes:
- Changes before migration script (Compare block)
Updates the target database to the state expected by the migration script. These are changes committed before the migration script. - Migration script (Migration block)
Adds the SQL you've written to the deployment script immediately after the Compare block.
The SQL Compare engine uses these blocks to generate a deployment script that executes your migration SQL at the specified point.
Dealing with drift
Changes made directly to the database without being committed to version control are considered drift. When you deploy a migration script, any drift is identified and preserved until either:
a. the end of the deployment script, where it's discarded if it doesn't appear in the source
or
b. the drift conflicts with a change being deployed, at which point the change is deployed and the drift discarded
