Moving Migrations up a Gear with SQL Source Control 5

Database development processes traditionally lag behind application development, so much so that databases are often considered a blocker to Agile and the hardest part of DevOps. Database changes are seen as so risky that a whole job has been invented – that of DBA – to minimize screw-ups.

That’s probably why, when we released SQL Source Control in 2010, it was seen as a game-changer. We’ve helped thousands of teams improve their processes and it cleared up the fog around who changed what, when, and why by applying the concepts of application version control to database development.

It still didn’t, though, fully address the thing that makes databases such a pig in the first place: data. Users could track schema changes, but if moving data around was an essential part of the change, there was an issue. That still had to live in the wild, outside the scope of source control.

Enter the world of migrations in SQL Source Control 3

When we launched SQL Source Control 3 in 2012, it allowed users to take schema changes and ‘cover’ them with a migration script which told the system exactly how the change should be made. Take splitting a column – you could create two new columns, move the data, and drop the old one. And it worked.

Most of the time.

Problem was, a rapidly growing group of users couldn’t use it. Because v3 precluded branching and merging, it ruled out the increasingly popular distributed version control offered by systems like Git.

At the time we designed it, there was still a debate over how databases should be designed and whether development should allow branching and merging. While the debate still isn’t settled, what is clear is that users want to be able to branch application code – and apply the same practices to the database.

Meeting all of those demands, and making database development as easy as application development, has been the aim of SQL Source Control 5.

Enter the real world of migrations in SQL Source Control 5

Looking back, the road to version 5 has been long and hard. Even with the ever-popular SQL Compare engine, it was hard to design a way to store and apply migrations, without an enormous learning curve.

SQL Source Control 5 (due to be released by the end of May) cracks the problem by bringing SQL scripts into the development process. It allows you to use your existing branching practices, adopt whichever version control system you prefer, and do migrations as well. And we’re pretty excited about it at Redgate:

“I’m thrilled by the recent changes in SQL Source Control. Improved support for version control systems such as Git now allows me to manage all my database development tasks from within SSMS. Even more recently, I really like the changes I’ve seen to migration scripts, as I can now also manage jobs and other instance related items objects as scripts and include them in a deployment, something to get excited about.” – Steve Jones

What’s actually changing?

Behind the scenes, we’ve changed the storage format of migrations. The new solution is based on storing changes as “blocks” which can be deployed in order, using a comparison tool like SQL Compare or, when needed, migration scripts that can be tailored to precise requirements.

Quite simply, it gives you a simple, repeatable way to create migration scripts, commit them, and use them in database deployments. With it, you’re able to:

  • Work with Git, as well as all other version control systems
  • Merge when working with multiple branches and flag up conflicts in migration scripts
  • Create data-only migration scripts (scripts that aren’t linked to specific schema changes)
  • Commit database changes alongside your application code
  • Deploy even when not connected to the code repository

See an example of how it’s used in practice in this short video:

What’s next?

The changes to migration scripts are big, so we’ve been checking the solution continually with users. At the end of last year we ran a private alpha to check the technical solution, and since February we’ve been working with users in a public beta of SQL Source Control 5.

We’ve used the feedback to improve the usability of migrations yet further and we’ll be releasing the results publicly in the next few weeks. To learn more about how we’ve improved migration scripts, read this blogpost by team member Owen Priestley.

You can also add any feature requests and suggestions to the SQL Source Control UserVoice page.

Finally, what does this really mean for the database world?

This release means that the bedrock of version control can now be applied to virtually every database development process. 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, which is really exciting.