SQL Server Management Studio has some neat tools for keeping database objects in source control, but they only work if everyone is on board. Red Gate SQL Source Control is a simple and elegant solution that doesn't depend on other developers using the snap-in... and it works.
I've been using Red Gate products since the beginning and I love them. A few years ago, I was on a team starting a new web database project and I was determined to put the objects into source control. Problem was, I was alone in my crusade. I found some neat tools in SQL Server Management Studio for keeping database objects in source control, but they only work if everyone is on board. Sad to say, my team was not and the database objects could not be controlled using the Microsoft Team Foundation Server source control system.
My next approach was to use my favorite Red Gate tool, SQL Compare, to take snapshots of the database objects. I never got obsessed enough to script the command line interface to make snapshots on some regular schedule but even my manually generated, twice daily object definition dumps were enough to help me track down what had been changed by the web developers. It was an imperfect system but it was all we had.
Some of you DBAs are already chastising me for giving the web developers ANY rights to create and modify database objects but we were a small team and we all wore multiple hats. I was, in fact, an application developer transitioning our Microsoft Access application to a web app so the database wasn't my only concern. I merely inherited the responsibility because I cared about managing the SQL Server objects more than others did.
Around that time, I remember having some conversations with the people at Red Gate where I explained what I was doing with SQL Compare and asked if they had any alternative source control ideas. They pointed me to a couple of white pages but honestly, that wasn't a perfect solution either.
Today, there IS a Red Gate SQL Source Control solution. It may not be perfect, but it's darn close. I've been reviewing it for some months now and I'm ready to share my conclusions.
Red Gate SQL Source Control
First, I suggest that you check out the SQL Source Control page at the Red Gate website for all the technical details. It would be redundant for me to reproduce them here not to mention a possible copyright infringement and the fact that technical specifications bore me. There's also a not-so-boring video that demonstrates the product in action. All good reasons to check out their site.
As of the time of this writing, there is no posted price for the product. I've been involved with the "early access program" which, while free, has required a number of product updates. I'm currently running version 0.3.3.1 which will expire in 3 days.
The SQL Source Control product is effectively a snap-in for SQL Server Management Studio that integrates with Team Foundation Server or a Subversion source control system. Since I'm no longer on the team mentioned earlier and don't use Team Foundation Server, I opted to set up a Subverson server and have linked the Red Gate product to it. That was the most difficult part of the process.
Setting up Subversion
Subversion is an open source application for controlling file versions that has been around for about a decade. It was obscure to me but I spoke with someone at a Visual Studio Users Group meeting who has been using it for years to synchronize his desktop and laptop. He said he regularly recommends it to all his clients and made an excellent argument that convinced me to implement it in my office.
There's a lot of information out there about how to set up and use Subversion. To begin with, you might read the instructions and references at the Red Gate site.
However, I'm afraid I didn't find this explanation adequate to get up and running. Perhaps if I understood more about Subversion itself, this would have made sense. In the end, I found a blog site that walked me through the process. I was able to install the server and create a repository but couldn't seem to get it connected to the Red Gate product. I include those steps, and an alternate solution below.
Approximate Steps to Install and Set up
latest subversion files
2) Unpack files to local folder (suggestion is c:svn)
3) Create a repository by executing this line at command prompt:
svnamdin create "c:svnrepository"
4) Modify the newly created repository files
c:svnrepositoryconfsvnserve.conf and c:svnrepositoryconfpasswd
5) Install Subversion as a windows service with this command prompt call
sc create svnserver binpath= "c:svnbinsvnserve.exe --service -r c:svnrepository" displayname= "Subversion" depend= Tcpip start= auto
6) Start the service
net start svnserver
7) Try to connect. Debug the problem. Try again. Repeat.
Well, that was frustrating, time-consuming and ultimately unfulfilling. A little more web searching on the topic led me to a better solution called VisualSVN.
Alternate Steps to Create Subversion Server
1) Go to http://www.visualsvn.com/server/
2) Download and install Visual SVN Server
3) Create new repository
The good folks at VisualSVN also supply an add-in for Visual Studio so that Subversion repositories may be used with your application code as well. In the end, this was the solution I went with. Now that I have a Subversion server, I realize there's a lot I can do with it but that's beyond the context of this article. With a Subversion server in place, we're ready to install and use the Red Gate SQL Source Control product.
Setting Up Red Gate SQL Source Control
After installing the SQL Source Control, you open SQL Server Management Studio and navigate to a database in the object explorer. You'll see a new window similar to the one below that gives you the Source Control options. The first step is to link the database to a Subversion repository. The VisualSVN interface helped me quickly locate the URL, which is required for the Repository URL property.
SQL Source Control - Link a database to source control
Just click the Link button and after a little thrashing of the CPU, your database is now under source control. The interface is pretty simple. Just one tab for the setup, one for committing changes and one for getting the latest version. You'll also notice that the icons in the Object Explorer have changed to reflect that the database is under source control and to identify the source control status of individual objects.
In the screen shot below, we see that I've edited a view called v_Delete_Me_Later. It now includes T-SQL to return a sorted view with TOP 100 Percent and ORDER BY clauses. In the UI, we can see both the current database version of the view and the last saved source control version. To update the source control version, you simply check the object you want to commit and press the commit button. It's advisable to supply a comment but it's optional and may be omitted. The commit process, once initiated, is represented with a nice feedback screen that keeps you apprised of what's happening until the process completes.
SQL Source Control - v_Delete_Me_Later
Commit Changes to Source Control
The Get Latest option is meaningless if there's only one person using the tool, as it was for me in my production environment. I did, however, attach to the controlled database with my laptop to approximate a second user for testing. It's pretty slick and the interface is very intuitive. I was able to quickly and easily remove the TOP 100 PERCENT and ORDER BY from modified view from my laptop and the changes were immediately reflected in SQL Server Management Studio on my desktop. The program also supports a right-click menu for the Commit, Get Latest and Undo Changes commands.
I earlier described this as a "nearly perfect" source control product and here's why:
1) "Perfect" ...
because it delivers what it promises ... source control
2) "Perfect" ... because it doesn't depend on other developers using the snap-in
3) "Nearly" ... because there was one missing feature: Undo Changes
In retrospect, I was wrong about the missing feature. Either it was just recently delivered in one of the product updates or I simply missed it. Either way, it's there. The Undo function may not have its own tab but it does show up on the right-click menu as mentioned above. So, if I delete my view named v_Delete_Me_Later and want to get it back, I need only click the Undo Changes option to begin the process. Of course, changes may only be "undone" prior to having been committed.
What is missing, if it can be called missing, is the ability to navigate through previously saved versions of, let's say a view, and compare with other saved versions. While this can't be done from the SQL Server snap-in, it can be done from the TortiseSVN client tools. Once installed, they show up on the right-click menu in Windows Explorer and provided you have navigation rights to the repository, you can spelunk through the file history all you want. That's what Subversion gives you and as mentioned before, slightly beyond the scope of this article.
As for the point about other developers not using the snap in, this is the greatest feature. I tested the product on a new database and web application we're developing here at Pharmatech. My web developer is competent with SQL Server but generally leaves T-SQL changes to me. However, I came in one morning to find some uncommitted-to-source-control objects. He had changed a few things, outside of source control, but the Red Gate snap in picked up the changes and demonstrated the changes for me, as shown in the screen shot below.
SQL Source Control Picked up the Changes
This is exactly what I want and expect in a Source Control system. What we previously used with Team Foundation Server had a big hole right where this solution should be. People could circumvent the system, modify database objects and the only way to know was to run a SQL Compare of the current database against a previous snapshot. It was a clumsy and painful solution. Red Gate SQL Source Control is simple and elegant ... and it works!