We’ve just released SQL Source Control 5 with improved migration scripts. With this release SQL Source Control now brings modern development practices (think Git and branching) together with preserving data as changes move up environments. Finally, it really is possible to automate database delivery – so I hope you’ll forgive us if we sound overexcited!

A brief migrations history

SQL Source Control enables you to version control your database schemas. When you go on to deploy the changes you’ve committed, the SQL Compare engine generates a deployment script to update the target database. Some schema changes also require data changes, which, if nobody checked them, could result in data loss. After all, the data is what makes working with databases so tricky.

Since the launch of SQL Source Control in 2010 we’ve released two solutions to the problem, each of which had its own limitations, including lack of support for Git (now the most popular version control system). But it’s a tough problem to crack, even with the bright minds we have here at Redgate!

Last year we began work on what we hoped was a single go-forward solution. During 2015 the engine technology matured, and in 2016 our beta users have been working hard to make this into what is now great solution to the problem.

How we cracked the problem

As with previous versions, the improved migration scripts solution allows you to write your own SQL to override the script generated by SQL Compare when you perform a deployment or Get latest in SQL Source Control. This means your teammates can get the latest changes from source control without risk to their local data.

The winning change is the way it works under the hood. The new migrations feature splits the deployment script into blocks of changes which are deployed in the order changes are made, so you can be sure that deployments will accurately reflect your development process.

The resulting solution is a repeatable way to create migration scripts, commit them, and use them in database deployments.

So, what does that mean for users?

This release means that the foundation of version control can now be applied to virtually every database development process. It allows you to use your existing branching practices, adopt whichever version control system you prefer, and still add in migrations when you need them.

If you’re considering database automation where the absence of migrations has been a showstopper, this is the release that really does make it possible to automate database delivery.

Common tasks which might be affected include splitting or merging columns and tables, adding a NOT NULL constraint to a column, changing the data type or size of a column, and renaming a table. Then, of course, you can make your own more complex changes for bigger refactoring.

How can I use migration scripts in SQL Source Control?

If you’re new to SQL Source Control you can try it out free for 28 days by downloading it here.

If you’re already using SQL Source Control you can get the new version via Check for updates in the Help menu of SQL Source Control inside SSMS.

You might find this example walk-through of renaming a table useful, or you can learn more about migration scripts in SQL Source Control and see other examples of where you might use them here.