Using SQL source control to track database changes

The goal of this article is to explain how SQL database source control can help in auditing database changes. Tracking a change by itself is not the only task required for successful implementation of SQL source control, as we also want to know who made the change, when and why. After all of these inputs are known, any problems and be diagnosed and be fixed efficiently. We’ll see how having a SQL database under version control can help in keeping the auditing trail with a possibility to revert back to any of the previous version of an object.

Challenge

SQL database version control, as a solution, needs to ensure that

• All changes made against a database are properly tracked,
• along with the information about Who made changes,
• When, and What objects are changed
• and finally, to be able to revert changes back to a particular point

These requirements are important because, at some point, a change made against a database can cause the process of building a database to fail and these are all pre-requisites to not only discover the problem but recover from it.

Native solution

First, we’ll look at a case where a database is not under source control but is still modified on a regular basis. The team of developers had developed their own informal way to keep track of all the changes, for example informing each other what was changed, or keeping scripts that are executed in order to make changes somewhere in the filesystem.

Let’s assume, in this environment, that a DDL trigger exists in a database, to catch all DDL changes. This does require an implementation that will help the team to have an auditing trail of database changes and to cover all the changes the team may perform against a database. This means that when a DDL trigger detects a change it has to save that information somewhere, so it can be used later. This solves one part of the problem (having an auditing trail). However, it is important to have a solution for reverting to an older version of an object once it is confirmed a newer one caused an error.

For tracking the changes made against the database, they are also using the Schema changes history report from SSMS.

The challenges they are facing are:

1. Which objects were changed?

   The Schema changes history report creates a list of all changed objects in a database, but the list is sorted by the name of the database object, not the time the last change of any object took place. To find a specific database object on the list, even if the database is relatively small (Adventure Works has about 150 objects), can be time-consuming.

   

2. What were the changes?

   When you finally find the changed object, you cannot be sure what specific change was made against it, since those details about which type of alteration is done (adding/dropping column) aren’t available in this view.

   

3. Who made the changes?

   When working at the same time on the same database object, it is important to determine which change was applied first, or last? To figure this one out, developers must communicate, all the time, to preserve the integrity of the object. Until you find and figure out which object is changed and how it is changed, in the Schema changes history report, the other users can alter that object again. So, you never really can be sure is the change made by the user you see in the Schema change history report, the last and the final change made against that object and by that user.

4. When the changes were made?

   To determine which object was changed last, you can also use the schema history report. The list that the Schema changes history report provides is not sorted by the last change, but by the name of the object. If you want to find out when the specific object is changed, you must find it on the list, and then hope that no one is altering the object in the moment you are searching the last change made against it. So, unless you tell all other members of your team to stop working on that database until you find the last change, you can never be sure that the change you are looking is the final change against that object.

5. Being able to revert changes back to a particular point?

   What happens when the change is made, but it should not be?

Alternative

ApexSQL Source Control is a SQL Server Management Studio add-in that allows seamless source control integration with Team Foundation Server, Git, Subversion, Mercurial and Perforce. From the moment of linking a database to source control and the initial commit of all SQL database objects, it allows tracking all the changes made against a database that are committed to source control, pulling back any version of a SQL database object and applying it against a database.

Pre-requisites

To show how SQL database source control can help with auditing database changes it is assumed that the following steps are covered:

1. Using ApexSQL Source Control, a database is linked to a repository using the shared model and all objects are initially committed by a single developer

2. A number of developers linked the same database from the same SQL Server instance to the same repository using the shared model

After the linking is done, a proprietary DDL Trigger will be created on the SQL Server. From that moment, the trigger will track every change made against any database object, and store all that information in the appropriate table

We’ll make a couple of changes by each developer, against a database.

Let’s say we have only two developers, working at the same time on the same database, which is already linked to source control system.

The first developer will add a new column to the table dbo.Table_1, and the second developer will, at the same time, delete one of the existing columns from the same table.

1. Which objects were changed?

   When the first developer changes the table, by adding a new column, the table icon in the Object Explorer pane will change its color to green, which means that the change was made, but it was not committed to source control. At the same time, for the second developer, the icon for the same object will change the color to red, which means that other developer altered the object and the change is not committed yet to source control.

   This way the add-in track changes in the real time, so developers know which database object was changed.

2. What were the changes?

   To see changes made against a database objects, right click on the linked database in the Object Explorer pane, and from the context menu choose the Action center command. In the Action center tab will be listed all changed database object since the last commit to the repository. Selecting any object on the list will show you in the Differences preview window its script, from the database and from the repository. All differences will be highlighted, in our case, the one deleted ([02] column) and the one added column (NewColumn06).

   

3. Who made the changes?

   This type of information will be present in the Action center tab as well. By the “Edited by” information of the changed object will be the name of the developer who made the last change against it, in the Edited by column.

   

   The one-piece information that the Action center tab does not contain, is the list of all developers who made changes against the object since the last commit to the repository. ApexSQL Source Control has solution for that as well.

   In the Options dialog, under the Administration tab, check the Log changes option, and the add-in will collect this type of information. To review who made the change against the object, right click the object in the Object Explorer pane, and from the More source control options menu, choose the Object change log command. In the Object change log window will be listed all developers who changed the object and the time of the change. Selecting the row, you can see which the type of change was it.

   

   

   

   This option is available for the whole database as well, where all changed objects will be listed with the developers who made that changes.

   

   

4. When the changes were made?

   This type of information will be present in the Database/Object Change log window, in the column Edited:

   

5. Being able to revert changes back to a particular point

   Besides tracking the database changes, ApexSQL Source Control track changes made against the repository, as well. The add-in saves the history of every commit to the repository, since the initial commit. So, if you ever made a change against the database that you do not want to, you can always view the History project window or the Object history window, choose which version of the object you want in your database, and apply it to the database to revert the unwanted changes.

   This way you can always back to the version of an object that ‘’worked’’.

   

   

Conclusion

Using ApexSQL Source Control for tracking changes made against your database, can be an efficient tool to save yourself from making unwanted mistakes and preserving the integrity of your database. Changes are communicated visually and in real time to other developers on the team.

And even if you make a mistake, ApexSQL Source Control have the mechanism to revert that change and restore the right version of an object in a few clicks.