Automating SQL Server version control with Visual SourceSafe

As a database administrator, I needed a way to automatically track changes to the database over time. My developers are constantly working on the databases, making changes and improvements, and they don’t always have the time to tell me what got changed. I’m not immune either – sometimes I make a quick fix, and I don’t do a good enough job of documenting what I did, and why.

Hilary Cotter wrote a slick SQL change management utility that makes change management a lot easier. It connects to a SQL Server and scripts every object in every database – tables, stored procedures, views, and indexes. It creates a separate text file for each object, and then here’s the nifty part – it connects to your Visual SourceSafe repository, checks to see if the object has changed, and if so, checks in the newer version.

It creates a detailed log of all activity, and a difference log of the changed objects. Hilary set his own version up to email the difference log to him when the script completes, and I made a few tweaks to mine as well, such as writing separate log files for each server.

The script even works with Visual SourceSafe 2005.

I have a scheduled task running it every 4 hours on our production boxes so that I’m notified faster. That way, if somebody makes a change that could undermine performance, I’ll know fast enough to fix it before things get out of hand.

There aren’t many drawbacks, but there’s a few. It doesn’t fully work with SQL Server 2005, because some object extended attributes (including the ones in the AdventureWorks sample database) make the script choke. It scripts all user databases in a given server – it’s all or nothing. It only uses Windows authentication, not SQL logins. All of these could probably be changed easily by somebody with VBscript experience and time, and I don’t have quite enough of either, so it’s good enough for me out of the box.

It also thinks an object has changed after a DBCC REINDEX command has been issued. I have a few dimension tables in my data warehouse that I reindex regularly for speed purposes, and this script always checks in the dimension tables after the reindex.

Another problem is that the Index Tuning Wizard (SQL 2000) and the Database Engine Tuning Advisor (2005) make temporary physical changes to objects. The script sees those objects as changed, even if the wizard/advisor undoes its changes, and checks in the new version to VSS. Granted, users should be running these index tuning tools on a development or QA server instead of production anyway, but since I monitor my development environment, I get a lot of false alarms here.

The utility doesn’t alert the database administrator when an object is dropped altogether. If a table is completely dropped, nothing will happen – it’s not taken out of VSS. Thankfully, if an index on a table is dropped, then that’ll trigger an alert on the table’s script.

Hilary’s version stores all of a databases’s objects (tables, views, procs, functions) in the same VSS project with that database’s name. Since I work with some pretty large databases, I modified it to store objects in the same folder hierarchy as SQL Management Studio uses – \Tables, \Views, \Programmability\Stored Procedures and \Programmability\Functions. That way, it’s easier to find the needles in the haystack.

Lastly, it has a lot of cool logic built in to detect whether or not an object has been added to the SourceSafe repository, but if the local file directories and the VSS repository get out of sync, the script explodes. During implementation testing, I repeatedly deleted the entire VSS repository without remembering to also delete the script’s working copy of the repository. The script got confused, and only worked successfully when I deleted the local working copy. If you use the script and run into errors, just delete all of the server-named subfolders in your script’s working directory, and it’ll re-download the repository from VSS.

Now, for a plug: I bookmarked Hilary’s SQL-to-VSS script in my favorite browser, Flock, and that’s where things got cool. When I bookmark something in Flock, I can see who else has bookmarked it:

Notice that another guy, bconlon, has bookmarked this same page. Since he found the SQL-to-VisualSourceSafe script interesting, I bet he would have other bookmarks that I would also find interesting. So, I clicked on his name, and presto, I could see his bookmarks – and I could zoom in on the ones specific to SQLserver. Ta-dah! There goes the rest of my day – I’ll be poking through the things this guy has already discovered. Ah, the magic of social bookmarking. If you’re a knowledge worker, you owe it to yourself to set up Flock and Delicious.