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.

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.

SQL Source Control 2.2 PROS: Insanely easy to install, deploy, and configure; doesn’t modify managed databases; removes hurdles to versioning databases; very affordable; supports all source-control endpoints (except Microsoft’s dated Visual SourceSafe)CONS: Encountered a minor problem with the order of operations when a full text index was created; advanced rollback capabilities require SQL Compare ProRATING: 5 out of 5PRICE: $295 per license for SQL Source Control; $595 per license for SQL Compare Pro; bundling and volume discounts availableRECOMMENDATION: Organizations in which SQL Server solutions are being actively developed should evaluate SQL Source Control. If they’ve been avoiding version-control systems because of implementation problems, they’ll likely find that this product addresses nearly all pain points. If they’ve implemented a system but are struggling with it, switching to SQL Source Control will likely result in tremendous productivity gains, decreased complexity, and a lower TCO.CONTACT: Red Gate Software • 866-997-0397 • www.red-gate.com

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More