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.
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.
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.
1) Download
latest subversion files
2) Unpack files to local
folder (suggestion is c:svn)
3) Create
a repository by executing this line at command prompt:
4) Modify
the newly created repository files
5)
Install Subversion as a
windows service with this command prompt call
6)
Start the service
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.
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.
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!
»
See All Articles by Columnist
Danny Lesandrini
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases–foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply