SQL Source Control 5

When you develop without branches, SQL Source Control deploys migration scripts in the order they're committed.

When using branches, you'll need to manually specify which order the migration scripts are deployed in. This order is set in the deployment order file when you merge two branches with new migration scripts.

In this example deployment order file:

lines 7 and 8 are in conflict with lines 10 and 11.
lines 6, 9 and 12 are conflict-resolution markers added by Git (these vary according to your VCS and merge tools).

Example conflict

{
  "About this file": "This is a Redgate SQL Source Control metadata file. It controls the order that changes are deployed in. Learn more: http://www.red-gate.com/SOC4/order-file-more-info",
  "Deployment order": [
    {"Description":"Changes before migration script 'data only'","Type":"Compare","Id":"2016-02-14-133701 u5 auto"},
    {"Description":"Migration script 'data only'","Type":"Migration","Id":"2016-02-14-133701 u6 user"},
<<<<<<< HEAD
    {"Description":"Changes before migration script 'Rename tblUsers to tblCustomers'","Type":"Compare","Id":"2016-02-16-141421 x1 auto"},
    {"Description":"Migration script 'Rename tblUsers to tblCustomers'","Type":"Migration","Id":"2016-02-16-141421 x2 user"},
=======
    {"Description":"Changes before migration script 'Make the id column NOT NULL'","Type":"Compare","Id":"2016-02-16-153040 j7 auto"},
    {"Description":"Migration script 'Make the id column NOT NULL'","Type":"Migration","Id":"2016-02-16-153040 j8 user"},
>>>>>>> not-null
  ],
  "Conflict in this file?": "If there’s a conflict in this file, you probably want to keep both changes. Learn more: http://www.red-gate.com/SOC4/order-file-more-info"
}

This conflict has occurred because both branches added a migration script. To complete the merge, you'll need to resolve this conflict and choose how your migrations will be deployed.

In the above example conflict, you need to choose which order to deploy the migrations in:

Deploy Rename tblUsers to tblCustomers followed by Make the id column NOT NULL. This is "keep mine" followed by "keep theirs" in your merge tool.
Deploy Make the id column NOT NULL followed by Rename tblUsers to tblCustomers. This is "keep theirs" followed by "keep mine" in your merge tool.

To configure Git to automatically merge the file in this way, set DeploymentOrder.json merge=union in your .gitattributes file. To learn more, see gitattributes (git-scm).

Semantic conflicts happen when migration scripts make changes that aren't independent. Regardless of the order they're deployed in, the deployment will either fail, or the target database won't reach the expected state.

Consider an example where the migration script on one branch changes a column from NULL to NOT NULL, while the migration script on the other branch changes the column from INT to SMALLINT.

Change column to SMALLINT

-- copy the data which might be lost
SELECT * INTO archiveTable FROM someTable
GO
 
-- change the column size
ALTER TABLE someTable ALTER COLUMN someColumn SMALLINT NULL
GO

Change column to NOT NULL

-- remove NULLs from the table
UPDATE someTable SET someColumn = 0 WHERE someColumn IS NULL
GO
 
-- make the column NOT NULL
ALTER TABLE someTable ALTER COLUMN someColumn INT NOT NULL
GO

The expected end state here is for the column to be SMALLINT NOT NULL, but we'll either end up with INT NOT NULL or SMALLINT NULL.

To resolve this conflict, use a merge tool to take one of the migrations when resolving the conflict ("keep mine" or "keep theirs"). Once you've done this, recreate the second migration script in SQL Source Control.

You'll be less likely to run into this situation if you use the "start from a blank script" option when creating migration scripts. The migration script on each branch will manipulate the data but not alter the column, and SQL Compare will make the expected change to the column after your migration scripts have been deployed.

You can also resolve the conflict by taking neither of the changes in your merge tool, therefore deploying neither of the migration scripts. From there, you can create a new migration script in SQL Source Control to make both changes.

When you create a new migration script, the previous migration script on the branch becomes its parent. This doesn't apply to the first migration script you create on a branch.

To commit new migration scripts or deploy existing migration scripts, you'll need to make sure that each migration script has its parent in the branch.

In the above example, branching occurs after the second migration script. Migration scripts 3 and 4 are siblings, with migration script 2 as their parent. Here, it’s possible to commit onto and deploy either migration script 3 or 4, because migration script 2 exists on both branches.

In this example, migration script 5 is cherry-pick merged into master. Master has migration scripts 1, 2, 3 and 5. However, migration script 5's parent is migration script 4, which doesn't exist in master. Committing a new migration script to master, or deploying from master, will fail.

To commit new migration scripts or deploy migration scripts, you must be able to follow each migration script's parent back to the first migration. This applies when patching your history in any way, such as cherry-picking or rebasing. Deleting a migration script which has one or more children causes the same problem.

During commit and deployment, we step through a model of the database at the end of each block in the deployment script. Each block stores a patch representing the change that block makes to the database, and we build the model by applying these patches in order. If a migration script is missing then we can't build the model because we don't know which patch to apply next.

SQL Source Control 5

Branching and merging