Tracking changes for projects is very important and allows us to revert back to previous versions. There is out-of-the box software to handle source control, but not all environments are equipped or disciplined enough to handle full version control–especially SQL objects, which can be changed with a simple F5 while applications are running. In the past, I’ve only used TFS with a SQL Server Database Project, which works great, but it is disconnected and doesn’t stop another developer or DBA from updating an object directly. There are others like SQL Source Control from Redgate, which seem to be integrated with SQL Management tools, but is not free.
Recently, I’ve worked on a project where the client was looking for a simple way to automatically track changes for certain SQL objects across their 3 different environments (dev/qa/prod). The solution we used was a DDL Trigger to record certain events and store all revisions in a separate configuration DB that resided in each environment. Let’s review how this works:
First, we need to create a table to store all revisions:
The table is created, so now we need to take current snapshots of the objects we’re going to be monitoring.
Now we need to create a trigger to capture these events when they happen. Using the code below, create a DDL trigger on each DB you wish to monitor to record changes to procedures, functions, and views.
You are not bound to monitoring just procedures, functions and views. Click here for other DDL events.
Some other things to consider with this solution:
- Adjusting the table and trigger to handle the rename event. This event is not available in SQL Server 2005 or below.
- Adding validation to ignore changes that have no alterations, like when you hit F5 more than once, forgetting you’ve already executed your changes. I’ve done this myself many times.
- Cleaning old versions after so many revisions if you only need to track the last # of changes and not the full history.
So how do you get the T-SQL out in a readable way? In SQL Server 2012 and above, carriage returns are preserved when copying data. If you are using an older SQL Server version, we can use the print function, but that has limitations. It truncates after 8000 bytes, which is 8000 chars or 4000 nchars. Below, is a stored procedure to print very large text.
This solution is not a prefect replacement for a full source control implementation. Due to its limitations and lack of functionality of other software, this solution might be better suited for small environments or as a temporary measure. But it is a simple approach to get you started.