Review: SQL Source Control 2.2

As a developer, I’m a huge proponent of using version-control systems to protect intellectual property and coordinate development efforts. Yet, my repeated attempts to leverage these systems when working with relational databases have always been fraught with problems. Consequently, I never swallowed the notion that version-control systems designed to manage simple text files could cleanly track the kinds of complex changes needed to modify code, objects, and data within SQL Server. Red Gate Software, however, has changed all that. Its SQL Source Control 2.2 software has turned most of my main complaints about version control in SQL Server on their heads.

To pull this off, Red Gate has gone above and beyond using “hooks” to detect changes and ALTER or CREATE scripts to render those changes. SQL Source Control is installed as an add-on for developer workstations running SQL Server Management Studio (SSMS) 2005 and later. It doesn’t require any modifications or changes to managed databases, as it uses comprehensive vectoring capabilities to seamlessly track and manage changes to SQL Server 2008 and SQL Server 2005 structures. (Although rarely done, it can even be used to change data, such as enumerated types or lookup values.) In this way, SQL Source Control transparently calculates everything needed to bring one instance of a SQL Server database into sync with another versioned instance of the same database. It’s ingenious really—and it takes all the pain and potential problems out of implementing version control in SQL Server.

To put a database under source control, you merely install the SQL Source Control add-on and use a wizard to specify the source control settings (e.g., repository locations, credentials). Then you specify whether the developers will be working on a single, shared copy of the versioned database or whether they’ll be working on their own dedicated (or sand-boxed) copy of the versioned database. Overall, setup and check-in/check-out operations couldn’t be easier.

To put SQL Source Control through the paces, I created a new full-text index along with an accompanying stored procedure and user with search permissions in a versioned AdventureWorks database. I also modified an existing stored procedure and changed the order of some columns in an existing table (something developers are prone to doing even if column order is technically not supposed to matter). Within seconds of each change, the Commit Changes dialog box (see Figure 1) was updated to include my most recent modifications as part of my pending commit. More important, SQL Source Control didn’t add any tracking code, triggers, or other “turd” changes to my versioned database to track changes.

Figure 1: SQL Source Control transparently tracks and manages even complex changes

After committing my changes, I ran into problems deploying them to another workstation because the CONTAINSTABLE function in my stored procedure was being applied before the full-text index was deployed and populated. I’m not too surprised that SQL Source Control ran into a problem with this operation—putting it into my test was almost unfair. (Even SQL Server replication runs into this problem.) Otherwise, all of my other changes were painlessly applied. Even the full-text problem was addressed through the UI by allowing the full-text index operation first, then allowing all other changes. As a final test, I reversed these changes, although this required the installation of Red Gate’s SQL Compare Pro on my test workstation.

DBAs using SQL Source Control will still need to scrutinize changes (and ensure there are proper backups and rollback scripts) before deploying changes into production environments. However, given how skeptical I was of using version-control systems with SQL Server prior to this review, I really can’t say enough good things about SQL Source Control. It’s insanely easy to use, deploy, and manage.

If you’re already using a version-control solution to manage your SQL Server environment, my guess is that you’ll have fewer headaches and a lower total cost of ownership (TOC) with SQL Source Control. If you’ve never used a version-control solution with SQL Server, it’s now safe to come out of the bunker and take a look because a new day has dawned and SQL Source Control actually makes versioning a painless experience.