Tracking changes made to the SQL Server Database objects (Version Controlling)

The purpose of this app is to provide source control management (using Github), for changes made to any SQL Server database object. When using this app, it is no longer necessary to manually save and upload files to a repository, which can solve the following problems:

• ⮚      The developer forgetting to save the file
• ⮚      The developer saving the file with the wrong name
• ⮚      The developer manually pushing files to Github

When someone changes a stored procedure and compiles it, (which overwrites the existing stored procedure) there is no way to find out what was there earlier other than by retrieving previous backups. This app automatically saves and uploads this data to Github to increase efficiency and general productivity.

There are few drawbacks in this approach.

●     If the changes are made manually through the SQL Server Management Studio, the process may not work as expected. For example, if a table is altered, the changes are not recorded as “Alter Table” but shows the entire execution process.

●     If the change made is to be documented by the writer, then they can use a tag called ”<ChangeSummary>” to indicate which parts f the comments you wish to be used as a description of the change

The process has 3 main components

1.       Database Components:

a.       A server trigger

b.      A table to record the changes and the Github repository data.

c.       A Github repository

2.       An App which is scheduled to run once a day or week to:

a.       Get data (specially the SQL scripts) from table, which are newly added

b.      Save data in a file in the local repository

c.       Commit to Github with appropriate comments

d.      Publish the folder (with all changes)to Github

e.      Retrieve  the Github repository URL

f.        Update the above table-record with the URL and all other data.

Details

1.a Server Trigger

This is a DDL trigger, which runs when any DDL change is made to a database object. New changes can be added later by altering the Trigger Script.

1. b Table to store changes

The table is kept in its own database, which has fields to capture the data related to the change made and the Github repository links. The table is filled when any change is made to one of these 4 objects: tables, stored procedures, functions and views. This can be extended to other objects as triggers, synonyms and so on. The trigger needs to be modified in order to capture the changes of those objects if needed.

The table is filled by the Inserts in the trigger and the table is updated when the app is run. It will then commit and publish the files to the Github repository.

Other than the version control related information, the information on the user executing the query will also be recorded for organization purposes. This table / DB should be accessible only to authorized personnel.

Table Structure

Data in some of the columns

2 App

The App is a .Net (C#) Console App. The app has to be installed in a windows server and the windows / task scheduler has to be attached so that the app can run periodically. This would be beneficial for teams of all sizes; small teams can have the app run weekly and much larger teams can benefit from running the app daily to properly document any changes made. The frequency of running doesn’t have any adverse effects, except that, it is better to schedule to run it off work, to avoid table lock or any other conflicting errors.

2.a App - Get Data

In the App, an entity framework is used to make data interactions with the database where the changes are stored. It has a class which contains methods to get ‘newly added’ data. ‘Newly added’ means the files which have not already been added to the Github repository. Those files are found in conjunction with the table which has null values for the batchID (i.e the records added after the last Github update through running this app.) There are other methods to get each folder name, record details of each data object, etc.  

2.b App – Save Changes to the file.

Once the appropriate data is selected above, the app creates a folder (if it doesn’t exist) and gets data through the process described in section 2.a, and saves the data into a file with an appropriate name.

2.c App – Github commit with comments.

Once all the files of the folder are created, the whole folder is committed to the local repository with a description indicating the ID of the record. The ID can be used to trace the specific changes made in the table containing all changes.

2.d App – Publish the folder (with all changes) in to GIT

All the commits done in above 2.c are published (synchronised or pushed) to Github Cloud Repository. All Comments and other details will be published to the cloud as well.

I used my private repository, but the repository has to be dedicated to the company which uses it.

2.e App – Get back the GIT URL

When published, each change will have its own unique id (a Hexadecimal number) in the Github repository. This id will be created at the time of commit and that’s why for each changed file, there is an ID. That id is combined with a fixed URL to access the change in the repository. The first 7 characters of that id are shown on the page beside the committed files.
ex: 

2.f App – Get back the GIT URL

After getting the Hex ID committed folders, they are concatenated to the fixed portion of URL to create the relevant URL.

ex.
Fixed:    https://github.com/psrinika/SQLDBObjs/commit/
Hex ID: ac0d910e601f482e7ac41a915845f66b64e0a3ec
URL:       https://github.com/psrinika/SQLDBObjs/commit/ac0d910e601f482e7ac41a915845f66b64e0a3ec

2.g App – Update the above table-record with the URL and all other data.


 The source code of the app is on Github, and a video is on the way!