SQL Source Control – no more database development without it

John Rummell had a problem that is shared by most database developers. How can one use SQL Server Management Studio to put source control files into a source control system such as SubVersion? The processes he tried were all error-prone and awkward to use. And then he found a solution.

Like many developers, I have been waiting a long time for a good source control solution for SQL. I wanted something that would easily track changes to table definitions, views, and stored procedures. In the past I’ve used SQL Server Management Studio to generate a script that would create all of the objects in my schema. Inevitably, I would forget to change the options to script table indexes or to include if not exists or some other setting that wasn’t the default. At this point I would regenerate the script again with the correct settings and then save it to a file and add it to my project which was in source control.

Now the script itself is quite a mess. Due to the nature of object dependencies, the users go first, then tables, then stored procedures and finally the foreign key constraints. Here’s an example from Facebook Steam Achievements, an open source Facebook application for sharing Steam Achievements (PC gaming achievements on the Steam network) with your Facebook friends.

Wouldn’t it be nice if this was organized into separate scripts for each object? It would certainly make it easier to read. Another method I’ve used is SQL Server Management Studio projects. This involves creating a script file for each object. In order to keep the database and scripts in sync, I would only make changes to the database by changing the scripts and executing them (instead of using the Design, Modify, or Script as ALTER options in Object Explorer). If you want to use Subversion to put this project in source control, you won’t have the convenience of add-ins like VisualSVN or AnkhSVN for Visual Studio. You could use External Tools in place of an add in, but it leaves much to be desired.

Red Gate has recently released a product called SQL Source Control that solves all of the issues I’ve mentioned. SQL Source Control allows you to edit your database as you normally would, but without worrying about updating or generating scripts. You don’t have to remember to script your database after you add a new stored procedure or add a column to a table. If you’ve made a change, it will display with a blue dot in the Object Explorer. You can think of this as the red modified overlay you’ve seen with TortoiseSVN.

1104-SSC1.jpg

To commit your changes, you can simply right click on the object and select Commit changes to source control... At this point you’ll see your changed objects and a diff view of the currently selected object. Simply type in a comment click Commit.

1104-SSC2.jpg

To get the latest version, simply right click on the database and select Get latest changes from source control…. Once you review the changes and click Get Latest, SQL Source Control will actually update your database with the latest changes. This way, you don’t have to worry about getting the latest scripts and then making the necessary changes in order to run it on your current version.

1104-SSC3.jpg

SQL Source Control also keeps your scripts organized. You probably won’t need to view the scripts, but if you are curious, you can view them in your working copy. In my Steam Achievements project I linked the database to https://facebooksteamachievements.googlecode.com/svn/trunk/Database. So now when I browse the Database folder in my working copy, I’ll see the scripts created by SQL Source Control.

1104-SSC4.jpg

As you can see, the scripts are organized by object type. If you browse the Tables folder you’ll see all of the table scripts.

1104-SSC5.jpg

There are a few things that I think would make SQL Source Control even better. Merge support would be very helpful as well as integration with the Visual Studio database tools. Merge would allow you to branch your database development, and would be a requirement for teams that use the branch-per-feature strategy. Visual Studio tooling support would allow developers to stay within one IDE for all of their development needs.

In summary, SQL Source Control is an SSMS add-in that:

  • Tracks changes to your database as you make them,
  • Doesn’t require you to generate scripts,
  • Updates your database when you get the latest version,
  • Keeps your scripts organized,
  • I don’t want to do database development without.

SQL Source Control can be downloaded from the Red Gate website.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , , , , , , ,

  • 10437 views

  • Rate
    [Total: 12    Average: 4.2/5]