Review: SQL Source Control

As a software developer, my primary focus and love in life is writing code. For the most part, managing the version of a code file over iterations is a solved problem. We have amazing systems such as Git, Subversion, Team Foundation Server, etc.

On the database side of the house, we could not typically say the same. How do you version a database? For some projects, we would maintain a master "development" database that all developers had access to. If changes needed to be made, it would hurt all developers for a short period of time until the correct code got committed. This did not work well though for long running feature branches.

There were database projects. A developer could manage and redeploy the database anytime new changes were pushed into source control. That was a lot to manage, and took developers out of the database tools.

Why could there not be another tool that allowed a development team to harness their existing source control systems, but make database changes a part of the workflow? Enter SQL Source Control by Redgate.

The Tagline: What Does It Do?

Redgate SQL Source Control helps SQL Server developers manage database schema changes. It plugs into SSMS and works in combination with your existing source control system, allowing you to manage changes to the database just like they were SQL files in source control.

Features:

• Blue blobs – visual indicator on your database objects of what has changed
• Get Latest – Share versions of the database with your colleagues by pulling in changes from version control
• Object history – view changes made to an individual object to assist in collaboration and troubleshooting.
• Reference data – View version configuration data, as well as schema objects.

Getting Started

I recently started using SQL Source Control in a new project where I was going to be working with a distributed team. Each member of the team would be responsible for managing their own database to support feature changes and bugs.

The first action of adding SQL Source Control to the project was to load it in SQL Server Management Studio, and link it with an existing database.

SQL Source Control provides two primary ways to add a database into source control. The "Link to my source control system" will tell SQL Source Control to do most of the heavy lifting. You can select a new folder underneath an already cloned repository, and SQL Source Control will provide the management for you. It is also possible to "Link to a working folder", which would allow you to choose a folder on your machine. Then if you need to commit changes or get the latest updates, you can work directly out of a folder on your disk. This is a fantastic feature if you’re not using Git or other supported source control systems.

Another decision you will need to make is if SQL Source Control should manage the database as a "dedicated" database or a "shared" database. In a dedicated scenario, all developers manage their own copy of the database (which is what we do). In a shared database, all developers would work on a single shared database.

What is happening behind the scenes?

After hacking on the database for a few minutes, I am ready to commit my changes into source control. SQL Source Control will analyze the current structure of the database, including tables, stored procedures, view, etc. It will then provide a contextual view of what changes were made.

If you are familiar with other Redgate tools, such as SQL Compare or SQL Data Compare, this interface will feel like second nature.

A great touch is SQL Source Control will identify potential breaking changes so I can warn other developers.

After adding a commit message, I can commit the changes to source control. One problem I have with the workflow here is that typically I want to have database changes and code changes as part of a single commit. In order for me to achieve this, I have to stage my other changes and then perform a final commit within SQL Source Control. I would love to see a "write changes to disk" feature, allowing me to control the commit a bit better.

On disk, the working folder simply contains the .SQL files for rebuilding the database. SQL Source Control is able to combine these files and compare with the current database.

Getting latest simply pulls the latest version of the repository and applies the changes in the working folder to the linked database.

Other Features

A useful feature that I have not used yet in a development environment is Object Locking. The idea is that if I am working on the same database of another developer, it is possible for me to lock different objects in the database while I am making changes. A small script needs to be run at the server-level to turn on this feature.

Migrations are not currently supported by my source control system (Git), although according to the Redgate website full support is not far away, and in the Redgate documentation it lets you override default comparison behaviour and handle data properly e.g. making a column non-nullable.

Conclusions

For our team, SQL Source Control has taken the complex task of managing the state of a database during the iterations of building a new product and made it simple. Each developer can work on new features or bugs without worrying about forgetting to communicate database changes to the team.

While my experience has only been with using SQL Source Control with Git, I do not think I would run into any issues with Subversion or Team Foundation Server. I highly recommend checking out SQL Source Control if your team needs an easy to use tool for managing SQL databases during a project lifecycle.