SQL Source Control

SQL Source Control is a tool that enables version control for databases in SQL Server Management Studio. I joined the SQL Source Control team to document the overhaul of the migrations feature in the software.

Migrations is a particularly difficult feature to document, as each user approaches the task with a different mental model, and often has a different understanding of when and how to use the feature. I worked closely with the UX designer on the team to design and test workflows, and maintained a dialogue with users throughout the beta period.

How we’ve improved migrations in SQL Source Control 5

Originally published on the SimpleTalk blog, April 2016

Migration scripts, which were first introduced in SQL Source Control 3, let you assign custom SQL to specific schema changes. This custom SQL replaces the relevant part of the deployment script, which is generated by the SQL Compare engine when you perform a deployment.

With a migration script, you gain complete control over how changes are handled, which makes it possible for your teammates to get the latest changes from source control without the risk of losing data.

Why we're taking a new approach

The first implementation of migration scripts wasn’t perfect, and a lot of our users encountered roadblocks. There was no support for Git or branching and merging, and migrations had to be linked to a separate repository location from your application code.

In 2014, we released a beta version of migrations — dubbed Migrations V2 beta — to address some of these problems. However, that solution was hard to use; you needed to write complex scripts, and make them compatible with any version of your database.

Feedback told us that this solution didn’t work for everyone; even the most experienced SQL developers found errors creeping into their deployments.

Instead of continuing down that path, we decided to drop the Migrations V2 beta, and improve on our original approach.

What's actually changing?

The new migrations feature splits the deployment script into blocks of changes.

Regular changes, which don’t risk data loss and can be safely handled by the SQL Compare engine, are added to compare blocks. These blocks use the normal deployment script, which is automatically generated.

Each migration script, however, is added to its own migrations block. These blocks contain the SQL you or your teammates have written to handle specific changes, which could be otherwise misinterpreted by the SQL Compare engine.

Blocks are deployed in the order changes are made, so you can be sure that deployments will accurately reflect your development process.

image-center

How is this an improvement?

Because changes are split into these blocks, you’re given the best of both worlds: a SQL Compare deployment script by default, with the option of using migrations to manually take control of certain changes when you need to.

SQL Source Control 4 supports Git, but its current migrations solution doesn’t. This was another roadblock for a lot of our users.

In SQL Source Control 5, migrations will support Git, along with branching and merging. Previous versions of migrations required the database schema to be in a specific state, which made branching and merging impossible. With the new approach, merging is fully supported, and conflict resolution is simple.

When working with states in source control, it’s easy to get an overview of how the schema looks at a given time. This benefit extends to our new migrations feature — writing a migration script is easier when you know the state the schema will be in when the migration script runs.

You can also use this fine-grained control to write data-only migration scripts. This gives you the benefits of a state-based approach for all of your schema changes, while your data is handled exactly the way you want.

How this affects existing migrations users

You’ll be able to automatically upgrade migration scripts created using the original migrations feature and continue to use them. However, if you’re using scripts created with the Migrations V2 beta, they won’t be supported after you install SQL Source Control 5. You need to deploy those scripts to all environments before you install the new beta.

Get involved

To help us build the best solution we can, we’re looking for feedback from as many users as possible. We’ve already tested the technical solution with a private alpha, and we’ve recently made a public beta version available to download. That’s where you come in.

Using the beta, you can create, commit and deploy migration scripts to your databases. We’ll use your feedback to improve the usability of migrations and shape the future of SQL Source Control, ahead of the public release in late spring. This is your chance to really have some input on where we take these latest improvements, so don’t be shy!

PDF

Working with migration scripts

Part of the SQL Source Control 5 documentation

What are migration scripts?

When you deploy changes committed to version control, the SQL Compare engine generates a deployment script to update the target database. You can use a migration script to add custom SQL to a specific point in this deployment script.

Migration scripts are necessary to avoid data loss when making certain schema changes. To achieve this, the migration script intervenes to make data changes occur at the right point of the deployment.

In most cases, you only need to write SQL for the data changes in the migration script. Schema changes are committed separately and deployed as normal.

Creating a migration script

To create a new migration script:

From the Object Explorer, select the database you want to add a migration script to.
From the toolbar, select SQL Source Control.
The SQL Source Control window opens.
Go to the Migrations tab.
Select the type of migration script, depending on your development process and the changes you're making.
In the Name field, enter a name for the script.
In the editor window, write SQL to make the required changes.
Click Save & Close.
Commit the changes to version control.

Always commit a new migration script immediately after saving it.

Making changes to your database schema between saving and committing migration scripts can cause errors during deployment.

When you deploy this revision from version control, or use Get latest in SQL Source Control on another machine, the migration script will run as part of the deployment.

For more information, see How migration scripts work

Editing migration scripts

You can edit or delete existing migration scripts from the Migrations tab in SQL Source Control:

From the Object Explorer, select a database with migration scripts.
From the toolbar, select SQL Source Control. The SQL Source Control window opens.
Go to the Migrations tab.
Expand Existing migration scripts. Migration scripts on the remote repository are listed.
In the Actions column, click View / Edit next to a migration script.
Edit the script to make the required changes.
Click Save & Close.
Go to the Commit changes tab and commit the updated migration script.

Once committed, the updated migration script is used in all future deployments.

Guidelines for editing migration scripts:

Don't create new object dependencies. This is likely to cause errors during deployment.
Don't add/remove DDL changes. This might create an invalid state in version control.
If you edit the syntax of DDL changes, the resulting schema must stay the same.

Deploying with migration scripts

We recommend using SQL Compare to deploy changes to production, as you have the opportunity to review the deployment script before it's deployed.

It is possible to use the Get latest function in SQL Source Control to deploy these changes, however we don't recommend linking your production database directly to source control.

Dependencies

When you create a migration script that includes uncommitted schema changes, SQL Source Control automatically includes any dependencies.

Deselecting any of these dependencies during the deployment stage will cause the deployment to fail.

PDF