The Need for Version Tracking

Oftentimes life in the “real world” is a little different than that in the textbook. Take developing and maintaining SQL Server database structures and stored procedure code, for example. In the mythical land of best practices, development is always done in a dev environment, all scripts and code are carefully checked into a version control system, QA is done by a separate group on a separate staging server, and then approved changes are pushed to the production environment…that no one ever directly modifies or updates.

Uh huh. I’ve lived a lot of years in the “real world” now, and I know that this strict process is never the way reality is. At a minimum, DBAs need to make changes to indexes and perform optimizations to stored procedures on the production SQL Server database.  Very often other kinds of changes need to be made to the production database in real-time, too. And even in a shop with lots of discipline, it is challenging to track every actual change made to the production SQL Server. In a less disciplined environment, tracking such changes is all but impossible.

The problem is that source code management or version control systems aren’t well-suited for lots of production SQL Server work.  How exactly would a DBA go about making an index change while keeping the source repository up-to-date?  How exactly would he or she determine what had changed since Monday when things were running fine, and today when things are running slow?

SQL Server Management Studio (SSMS) is a fine tool (a few quirks and frustrations not withstanding like useful tab names, anyone?). Most of us that work with SQL Server use SSMS daily. Yes, SSMS supports source control clients that work with systems like Team Foundation Server. But honestly, how often is this really used when working with production databases?  (If some of you are working this way, great!  And I’d love to hear about it.  But I have never seen a SQL Server shop that works this way.)

Regardless, tracking changes on production SQL Servers is critically important. Making a small change like adding or dropping an index can have a huge impact. Accidentally altering a stored procedure can be very painful once you close the tab in SSMS. And figuring out what someone else did…is practically impossible.

This is the real world in which I have been living anyway.

What is SQLVer?

Fortunately, some years ago I created a passive version tracking solution in SQL and have used it successfully for a long time. Wherever I go now, my SQLVer is one of the first things I set up when I begin working in a new environment. I have recently posted SQLVer as an open-source project on SourceForge, and thought I’d write about it here.  I suspect many people working with SQL databases can benefit from it. You can download SQLVer from: https://sourceforge.net/projects/sqlver

SQLVer is really just a couple of tables, a handful of stored procedures and a DDL database trigger. But the results are pretty useful. SQLVer is entirely T-SQL based, and makes good use of a DDL database trigger. Many of you have probably built your own solutions for tracking changes in a similar way, so I am not suggesting that SQLVer is in any way revolutionary. But it sure does make working with production systems safer and easier, and helps me do a better job.

There are a lot of capabilities built into SQLVer, including:  automatic tracking of all DDL changes, ability to roll back to an older version of an object, ability to search for a string in T-SQL source code anywhere in the database, a run-time logging system, a way to identify slow stored procedures, a way to identify SQL connections that are hogging resources and blocking object access, and more.

For today, I just want to get you started. I can cover additional features in a future article.

Architecture

SQLVer creates a SQL schema, named sqlver, in the current database (in which you run the SQLVer script). SQLVer uses a single DDL Database Trigger for logging. Other than that, no other changes are made outside of the sqlver schema.  SQLVer can be uninstalled with a single command:

EXEC sqlver.spUninstall

All of that is to say, it is perfectly safe to install SQLVer in any database that you want—in a Dev environment or a Production environment.  But, as any prudent DBA should do, don’t take my word for it:  feel free to look at the source code.

At its heart, SQLVer uses two tables:  sqlver.tblSchemaManifest and sqlver.tblSchemaLog. tblSchemaManifest is populated automatically at installation with every SQL object in your database. A row gets inserted into tblSchemaLog any time you make a DDL change.

SQLVer does create a third table, sqlver.tblSysRTLog, but this is not used by version tracking. Instead, it supports run-time logging from T-SQL (think debugging or performance optimization logging). I’ll talk more about logging in the future. Today we are focusing on version tracking.

Passive Tracking

Since SQLVer makes use of a DDL trigger, you don’t need to do anything in order to benefit from SQLVer:  you can simply install it, and all future DDL changes including CREATE TABLE, ALTER TABLE, CREATE INDEX, CREATE PROCEDURE, etc. will automatically be logged for you.

Installing

To install, simply download the SQLVer SQL file and execute it in SSMS.  You can download the file attached to this story, or you can download the current version from https://sourceforge.net/projects/sqlver

After installing, tracking will automatically be enabled.  Look at the output in the Messages tab in SSMS for additional tips and examples.

Reviewing Version Information

To try this out, you could do something like:

CREATE TABLE tblTest (Id int);
ALTER TABLE tblTest ADD Name varchar(50);

To inspect the version information that has been logged, you can simply run:

EXEC sqlver.ver

This will return a list of all objects in the database, listed in descending order of the date last modified.  Note that the user who last modified the object is listed, as well as comments and some other information.

If you are interested in the history a particular object, you can run:

EXEC sqlver.ver @ObjectName = 'tblTest', @MaxVersions=5

Note that this also returns a column for SchemaLogId.  This value is useful if you want to retrieve a particular version.  To do so, simply run:

EXEC sqlver.ver @SchemaLogID = 2 --or whatever SchemaLogID matches the row that you are interested in

Besides returning the single row in the resultset pertaining to that version, if you click on the Messages tab in SSMS, you will see that it also prints out the source that made up the SQL command for that version.

One note on the source output:  Due to limitations in the SQL PRINT function, PRINT needs to output the source in chunks.  At the end of each chunk, you will see a ~ character followed by a new line.  Search through the source for ~, and delete that character and the following new line, and you will have exactly the source that was executed.  You can copy this source to a new query window, and execute it to alter a stored procedure or function back to that particular version.

You are always free to directly query the underlying tables as well:

SELECT *
 FROM sqlver.tblSchemaLog

Note that the real stored procedure that retrieves version information is sqlver.spVersion  SQLVer makes use of synonyms to create “shortcuts” to frequently used procedures.  Thus, when we EXEC sqlver.ver this is really a synonym that will execute the stored procedure sqlver.spVersion

As I mentioned before, there is a lot more that SQLVer does.  I will cover these in a future article, or you can look for more information at https://sourceforge.net/projects/sqlver

I am also eager to hear your comments and suggestions, and even to have you contribute code to the project  if you are interested.

Enjoy.  And rest easy, knowing that changes you and others make to the database are safely being captured and logged.