redgate SQL Source Control Revision Numbers

Anyone that’s developed with me in the last 7 years knows that I’m a HUGE fan of redgate. Honestly, now that I use their tools, I don’t know how I would work on MS SQL databases without them.

Recently, I’ve been taking a deep dive into SQL Source Control and SQL Compare. So far, I love it.

• SQL Source Control: Is a Microsoft SQL Server Management Studio plug-in which allows me to commit my database schema, static data, and migration scripts into version control.
• SQL Compare: Is a stand alone tool that allows me to compare and deploy schema changes. From a Database, Backup, Snapshot, Script Folder, or directly from source control to any combination there of.

In my case, I am using SQL Compare to deploy schema changes directly from source control, to my database.

One notable feature, is SQL Compare’s use of Extended Properties to track the version control revision number, in the database.

Here is an example of the script they use:

   1: DECLARE @RG_SC_VERSION BIGINT

   2: SET @RG_SC_VERSION = 13670

   3: IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))

   4:   EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL

   5: EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

   6: GO

This means you can figure out the revision number of your database by querying the Extended Property.

   1: SELECT Name, Value FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL)

Why does this matter?

Well, when the time comes to update a client’s database to the latest version, it’s as easy as.

1. Figure out the version of their database (using the script above)
2. Using SQL Compare: Set your “Source” to the Head of your source control, set the “Target” to the version of the database the client is at
3. Using SQL Compare: Compare the two database schemas, and generate a deploy script

So awesome.

I'll be on the Source Control Panel on February 19, 2008 18:00

The Cincinnati User's Group (Cinnug) has asked me to be a part of the "Source Control Panel" they are having on Tuesday February 19, 2008 at 1800.

This meeting is different then those I've been in before. It's an open panel for group members to come and ask questions about VSS, TFS, Harvest, ClearCase, Vault and Subversion.

I'll be there primarily as a Subversion resource, but I'll try to fill in on VSS and TFS where I can.

Short notice I know, but if you're not doing anything Tuesday, come on down an heckle me. I think it's going to be a lot of fun.

My Second, "Getting the Most from Version Control" Presentation

Introduction This time I gave a presentation at The Dayton .Net Developers Group. For those that attended, my materials are now available for download. Compared to the first presentation, this one focused on Subversion less, and more on general practices. That was something I feel needed to be improved from the first meeting. For those that attended the first presentation you might want to look at my new materials, I completely overhauled my slide deck. My demos were much less this time, and my last one bombed. But I promised to list the corrected steps here, so stay tuned. What did I learn? A big difference between this demo and last is I didn't use svnserve. TortoiseSVN (TSVN) can use the "file:" protocol to connect to local or UNC repositories. (I found it in "4.1.3. Local Access to the Repository" of the TSVN documentation.) That's great news for those people out there what want to get started quickly, TSVN can do everything you need. There was a request for a demo that involved VS and .NET projects, which I'll be sure to add if I do the presentation again. Final Thought All in all, the feedback was very positive. Thanks to all of you for your kind words, and if you have any comments please list them here, or on the forum at the group's website.