This article explains how SQL database source control can help in version control SQL scripts alongside SQL database objects. Developing a software sometimes requires versioning not only database objects, but custom SQL scripts for migration, configuration, automation or other purposes
Challenge
The challenge is to find a way to track changes of a custom SQL script like a database object script, so you can track any changes that might happen and to have a possibility to revert that changes back, with preserving the integrity of a database.
Problem
When working with a database which is under source control, SQL database object scripts are sent to a repository, where all changes are being tracked and database object scripts are updated accordingly. But when working with a database, there is often additional information about it, that is stored in the custom scripts, the text based scripts:
These scripts could be any of the following, and more
- A database creation script, that would be used to drop and create a new QA database each time a new build is created from source control
- A configuration file that could be run to update and existing QA database
- A script to create procedures for unit testing that would be run against a newly built database
- Scripts to run before or after a build to prep and/or clean up a newly created database
- Data creation scripts if static data wasn’t stored in the repository on a per table basis
- Scripts to automatically generate test data
One way to sort this problem, is to create a new project on the repository, for each type of custom script (updated on a daily basis and updated from time to time).
Pre-condition
We will assume that a database is already linked to source control system repository, using ApexSQL Source Control, a SQL Server Management Studio add-in that allows seamless source control integration with Team Foundation Server, Git, Subversion, Mercurial and Perforce. After the linking process is done, the initial commit can be performed, but it is not mandatory.
Solution
ApexSQL Source Control has solution for all above listed problems.
We linked the database DevDB, in dedicated development model, to an empty Git repository. Before the initial commit performed, this repository will be empty, without any folder structure. After the initial commit, on this repository will be created the folder structure for every type of the object present in the linked database.
From this moment, all changes made against the database tables will be tracked. All custom SQL scripts that our application is using, related to this database, will be stored in a local folder, the Test queries folder. We will put them under source control as well.
Go to the ApexSQL main menu in SQL Server Management Studio and from the ApexSQL Source Control menu choose the Options command:
Under the Custom script tab, select Server and the linked database, to which you want to add custom scripts:
Click the Browse for folder button, in order to assign the custom script folder which, you want to put under source control:
When everything is set, click the OK button to save the settings. Initiate the Action center tab, or refresh it if is already open. Now in the Action center tab, all custom scripts from the selected folder (the Test queries folder) will be listed:
From now on, all custom scripts will be treated as SQL database objects. This means that, after custom scripts are initially committed to a repository, all changes will be tracked and shown as any other SQL change made against objects from the linked database.
After committing all custom scripts, ApexSQL Source Control will create a new folder on a repository, named the Custom scripts folder, in which all custom scripts will be saved:
If the other team members want to see the changes made against the custom scripts, they must link to the same repository their copies of DevDB database. After the linking process is done, create the new, empty folder, add it in the same way shown above to the linked database, select all custom scripts listed in the Action center tab and click the Apply button. This action will store all custom scripts from the repository to local folder.
To add a new custom script to a repository, just add a custom script to the linked folder on a local machine, and initiate the Action center tab. The new custom script will be listed as one of the change, and checking it and clicking the Apply button will send it to a repository.
In the environment that a team working with multiple custom scripts, but every team member is working with the specific custom script or a group of custom scripts, ApexSQL Source Control can simplify the tracking. Each team member can choose which custom scripts will be tracked (shown) in the Action center tab, by filtering them on a local machine. This can be done through the Options dialog, under the Object filtering tab. After selecting the Server and the linked database, click the Show object button, and from the Repository only tab, check which custom scripts you want to track:
Now, when we initial the Action center tab, it will compare the last version of the custom script from the repository and our version of the same script. This way you will always know which is the last version of the script send it to the repository. Here will be all changes made against the custom script, and you have the option to send your version of the custom script to the repository or to get the version from the repository. If you choose to send your version to the repository, all other team members will see which changes were made, when they start the Action center tab.
Since the add-in tracks changes made it against the repository as well, if by any chance the wrong version of the custom script is sent to the repository, through the Project history form you can find the last working script version and restore it.
The whole solution we have been explaining was based on the presumption that the custom scripts folder is placed on the local machine, regardless of the development model. You can place the custom script folder on a network location, as well, so every member of the team can access it. This kind of solution will transform the custom script folder to a central repository for custom scripts, and all changes made by any member of the team will be visible for the rest of the team.
November 3, 2017
