january 30, 2024
Code versioning and releases for MSSQL
Git version control systems are essential for large-scale development projects — they provide a toolkit for tracking and managing changes to program code created by multiple developers in parallel.
One such tool is Bitbucket, a code versioning tool from Atlassian.
To organize SQL code versioning, Microsoft has developed the Visual Studio «Database Project» project standard, which we will tell you how to use in this article.
Required tools, installation and setup
- Visual Studio 2019
a. Plugin Visual Studio Bitbucket Extension
b. Plugin Microsoft Analysis Services Projects
c. SQL Server Data Tools
d. SQL Server ODBC Driver
List of Visual Studio extensions to install:
Figure 1: List of VS extensions
To operate Bitbucket through the plugin, you will need to create a personal account — in it you need to generate a password for the application.
Bitbucket settings:
Figure 2: BitBucket personal account
Bitbucket application password:
Figure 3: Creating a BitBucket application password
To connect to the account from the app in the future, you must use the generated password.
2. Git — for operating with remote repositories via the OS command line.
After installing and connecting the account you need to grant the appropriate access rights to the target repository — add the user to the appropriate group in the «Repository permissions» section of the repository settings, then add the user in the «Branch restrictions» section to the required main branches — master, test and dev.
Bitbucket repository settings:
Figure 4: Access rights to BitBucket branches
Next, you need to open the repository locally via Visual Studio — to do this, you need to clone the repository with the git clone command in the command line — the necessary request is generated by clicking the «Clone» button on the repository’s main page:
Figure 5. Cloning a BitBucket repository
In a user-friendly local directory, in a sloped repository, open a file with the type «sln» Below is a list of branches:
Figure 6. List of BitBucket local repository branches
In the project, connect via Bitbucket account, open «Manage Branches» in the «Git» section — the list of remote and local branches of the repository will open.
Working in a repository
Rules for creating branches
The master branch corresponds to the state of the code on prod-instances.
The test branch corresponds to the state of the code on the test environment.
The dev branch — corresponds to the state of the code on the dev environment.
In our project, the release schedule is defined by the project manager for the current year. For each release, a corresponding branch is created in the repository from the dev branch (e.g. release/1.0.31):
Figure 7. Creating a child branch from the dev branch
Further, all local branches inherit from the corresponding release branch — patch bugs including the following rule:
• feature/#NNNNN – implementation of new functionality,
• bugfix/#NNNNN – bug fix,
• hotfix/#NNNNN – hotfix.
где “#NNNNN” task number.
Merging branches and resolving conflicts
Pull-requests from all child tasks are merged («merge») into a release branch. Release branches are merged into a dev branch. When release testing starts, all changes from the dev branch are merged into the test branch and into the active release branches — to keep the code in them up to date. After testing is complete and release to release, changes from the test branch are merged into master.
Figure 8. Implementation of branch merging in BitBucket
Merge conflicts occur if the same lines in the same file were edited in different tasks. In this case the conflict must be resolved manually.
To merge a branch is necessary:
1. Copy a branch from a remote repository to your local machine — just switch to it in the «Remotes» section.
2. Switch to the branch to be merged into.
3. In the «Git»->»Merge Branches» tab, find the branch from step 1, click on PCM and select «Merge ‘branch X’ into ‘branch Y'»
Branch merge:
Figure 9.Implementation of branch merging in VS
Pause at branch merge:
Figure 10. Branch merge conflict
In case of a merge conflict, it must be resolved manually — to do this, resolve all conflicts in all files in the «Unmerged Changes» section. The figure below shows an example of merging a dev branch into a test branch. On the left side of the screen, the changes from the branch that is being merged are noted. The right side of the screen shows the changes in the current branch. At the bottom of the screen is the resulting text — this can be edited manually. Conflict Resolution:
Figure 11. Resolving a branch merge conflict
After all conflicts are resolved, a commit comment is written and the merge commit is fluffed to the remote repository.
Build the patch and install it on the instance
To build a patch correctly, current structures and procedure scripts must be deployed on one of the dev environments. If different releases are built on different databases at the same time, it makes sense to update one of the dev databases with scripts from the current dev branch.
To build a patch from Database Project, select «New Schema Comparison» in the «Tools» tab of Visual Studio in the «SQL Server» section.
Figure 12. New Schema Comparison tool
In the open comparison interface, you will need to specify the source and purpose of the comparison:
Figure 13. Selecting target databases in the «New Schema Comparison» tool
After clicking «Compare» in the interface header, you will need to manually select the changes that will be included in the build release.
Figure 14. Result of database schema comparison
After selecting all the desired changes, clicking «Generate Script» will generate the deployment script.
Figure 15. Deployment script generation
To manually run the script in the base, you must remove the lines at the beginning that are intended to be run from the command line and third-party applications:
Figure 16. Adjustments to the generated deployment script
After receiving the script text, it is necessary to swap the source and target in the comparison and generate a rollback script in case of emergency.
The received script is run on the database for the test environment in accordance with the release schedule (which is defined by the manager).
The script needs to be updated in case of corrections of patch errors detected during testing.
After the patch testing is completed, the actual script is run on the instances database according to the release schedule and in close cooperation with the web-development teamleader.
It is recommended to review the logs received when installing the script for warnings and errors — in some situations (for example, when a procedure does not compile on an instance with an outdated version of SQL Server) it is necessary to urgently manually fix the installed procedure. Some errors are not critical and can be resolved later — it is highly recommended to save logs from the installation on each instance locally. This will not only help to track down possible errors, but also to understand when and which instance was actually updated, and what changes were applied on it.
Installation log:
Figure 17. Installation log
