Putting databases under version control with Redgate's SQL Source Control

Introduction

Why don't you treat your database as code? This is a question I often ask because, if you decouple dynamic data from the database structure, you're left with code and static data. So version controlling the database becomes not only possible, but offers distinct advantages.

Changes can be changed – when changes are committed to version control, the system maintains a history of those changes. Consequently, if a bug is found or you need to undo a change, you can revert to earlier versions at, literally, the touch of a button.

Changes can be tracked – sometimes you want to know who made a change, when they made it and, crucially, why they made it. Version control keeps a complete record of every change so that you know every single detail.

Changes can be shared – version control encourages teamwork by giving teams the ability to share files and stay up-to-date with the latest developments. That way, developers are always working on the latest version – and database changes can be directly related to application changes.

Changes can be branched – rather than working on a shared database, version control means developers can work on their own dedicated database so that different branches of development can be pursued. A good version control system can then manage conflicts and merge the different versions later.

Changes can be tested – with version control in place, you can deploy specific revisions straight from version control, which means you are deploying exactly the same thing into your test environment which you will deploy into your production environment, reducing errors. To further this, automated tests can be introduced earlier in the development process so that bugs are found sooner and development time is reduced.

Changes can be deployed – following on from testing, you can deploy from version control, safe in the knowledge that the changes you have made are safe for your production environment.

Don’t change the way you work – enhance the way you work

As can be seen, version control brings big advantages. It doesn’t, however, have to be at the cost of forcing you to work in a different way. If you choose the right approach to version controlling, you can plug straight into the systems you already have.

Take SQL Source Control from Redgate. Because it’s an add-in for SQL Server Management Studio which links your version control system to your database, developers and DBAs can continue to work in a way they’re already familiar with.

The added advantage is that it works with all of the most popular version control systems like Team Foundation Server, Subversion, Git, and Mercurial. When changes to the database are made, it scripts out files that represent the new state of each object and commits them to the version control system.

It’s also possible to choose either a shared or dedicated development model. In the former case, the whole team work on a single development server, and in the second each developer has their own copy of the database, downloaded directly from version control.

Inside SSMS, SQL Source Control adds a host of instantly accessible features to the interface:

• Visual elements make it easy to see at a glance what’s under version control, what’s not, and the status of each object.
• The Commit changes tab shows you what changes are ready to commit, highlights the differences down to the individual lines of SQL, and allows you to commit them to version control at the touch of a button.
• The Get latest tab gives every developer working on the database instant access to the very latest version of the database, avoiding passing around handwritten change scripts. And of course, it’s a one-click job to pull the latest changes into your database.

A big strength of the tool for me is the simplicity it offers for managing static data. Lookup tables, like a list of US states, are often used as a reference for foreign keys and can be persisted in version control. In the Object Explorer, right-click the database or table with data you want to version control and select Other SQL Source Control tasks > Link or unlink static data. A dialog box will open with the Link/unlink static data tab selected.

Select the tables you want to link and after you’ve made your selection, you can commit the data to version control. You can also use the Edit filter rules tab to specify exclusion or inclusion conditions for individual objects or all object types.

Version control is just the beginning

Once you’ve got your database under version control, Redgate offers a neat way to deploy your changes into production straight from your repository. Better still, it does so without any of the errors, risks of downtime, and unexpected problems that you might be familiar with.

Redgate’s most well-known tool, SQL Compare, can help here. It detects your development changes, along with any scripts that specify how to deploy them. It then writes the deployment scripts for you, and you can deploy from SQL Compare or copy the script to review and run later. It will also generate rollback scripts for you.

What I particularly like about SQL Source Control is that, if you’re a little more advanced in the deployment process, it opens the doors to continuous integration and automated deployment. Redgate’s SQL CI plugin is part of the DLM Automation Suite, a suite of tools from Redgate that let you apply continuous integration and release management processes to SQL Server databases.

I use it so that when changes are checked into SQL Source Control, SQL CI runs a CI process for the database with four steps – Build, Test, Sync, Publish. At the end, I have a tested NuGet package ready to use in a release management tool. My own preference is Octopus Deploy and the end result is that I can work in an agile way with databases as well as applications.

Summary

I was introduced to Redgate when I started using SQL Compare to deploy databases. When SQL Source Control came along a few years later, it made my job – and that of my colleagues – a lot easier.

Whether you work alone or in a team, version controlling your database alongside your application should be regarded as a fundamental part of the development process. Especially when tools like SQL Source Control and SQL Compare are around.

Take the first step by downloading a free trial of SQL Source Control.