SQL Server - SQL Objects Versioning and Analysis

This document explains how to set up source code versioning and analysis for SQL Server database assets using Azure Data Studio, GitHub, and SonarQube. The goal is to track changes in the database and push them to a GitHub repository for continuous code versioning. You can then use GitHub actions to perform code analysis on the database updates to ensure code quality and compliance with best practices. By following this procedure, you will be able to effectively manage and maintain your SQL Server database assets.

Setup

Download and install Azure Data Studio

Azure Data Studio is an open-source, cross-platform database tool (Windows, macOS, or Linux) for data professionals for on-premises and cloud data platforms management.

Azure Data Studio offers a user-friendly interface for querying, editing, and visualizing data. It also includes built-in features for managing and optimizing database performance, such as IntelliSense, code snippets, and code navigation. Additionally, Azure Data Studio is extensible, allowing users to install and use third-party extensions to add functionality to the tool .

You can find more details here.

Platform Installer
Windows User installer (recommended)
System installer
.zip file
macOS universal
x64
arm64
Linux .deb file
.rpm file
.tar.gz file

Install the SQL Database Projects and the SQL Server Schema Compare extensions

In Azure Data Studio, open the Extensions menu, then look up and install the following extensions:

  • SQL Database Projects
  • SQL Server Schema Compare

Check your GitHub account

Make sure you have access to the FintechOS GitHub application.

Set up your database project

Create a GitHub repository for the database project

Use the new GitHub repository link to create a GitHub repository based on the [fintechos-com/sql-server-development-template] template, as shown below:

Clone the GitHub repository locally

Open a console on your local environment and clone the GitHub repository you created earlier. For example:

Copy 
git clone https://github.com/fintechos-com/sql-server-dev-test.git

Open the local repository in Azure Data Studio

  1. In Azure Data Studio, go to File > Open and select the local GitHub repository folder.

  2. If asked, confirm that you trust the authors of the files in your folder.

Create a new project for your SQL database

  1. In Azure Data Studio, open the Database Projects menu and click Create new.
  2. Select your database and provide a name for the project.

Update the project from the database

  1. Right-click the newly created project and select Update Project from Database.
  2. Click Database connection details and provide your database connection details.
  3. Fill in the connection details and click Connect.
  4. Check the View all changes in Schema Compare option and click Update.
  5. Once the comparison is displayed, click Apply to update the local database project with all your SQL objects.
  6. After the database project is updated, you can review the local repository changes in the Source Control menu.
  7. You can toggle the changes view from the top menu button.
  8. Select the changes you wish to commit, provide a description, and click the Commit button.
  9. Click Sync Changes to push the commit to the GitHub repository.
  10. Confirm the push action.

Once completed, browse the GitHub repository to make sure that the new database project is under source control.

Continuous Code Versioning

Once you have set up source control, you can synchronize your Azure Data Studio database project with updates on the SQL Server, then push those updates to GitHub as a new version, following the GitHub code review pattern (pull request - code review - code merge).

  1. For instance, a new table called [dbo].[TestAzureDataStudio] is created on the database.
  2. In Azure Data Studio, right click the project and select Update Project From Database.
  3. Select View Changes in Schema Compare and click Update.
  4. Select the new table and click Apply.
  5. Once the project is up-to-date, the table file appears in the source control changes.
  6. Right-click the file and select Stage Changes.
  7. Click Commit to commit the table file to the local repository.
  8. Click Sync Changes to push the new table file to the remote repository.

Code Analysis - Build Database Project

The GitHub repository includes an automation called Build Database Project which triggers a code build integrity analysis on every push action.

This analysis is similar to Strict Mode in JavaScript. The action consist in running a .NET build command which checks all the SQL objects.

For instance a stored procedure create statement could run on SQL Server Management Studio (SSMS) but could fail on a .NET build due to ambiguous references which could generate later issues.

Copy 
/home/runner/work/sql-server-dev-test/sql-server-dev-test/database-lighthouse-dev/
EbsLocalization/Views/vwLocalizableAttributes.sql(32,29,32,29): 
Build error SQL71501: SqlView: [EbsLocalization].[vwLocalizableAttributes] 
contains an unresolved reference to an object. 
Either the object does not exist or the reference is ambiguous because 
it could refer to any of the following objects: 
[dbo].[syscolumns].[C]::[xtype], [dbo].[syscolumns].[xtype], 
[dbo].[systypes].[C]::[xtype], 
[EbsLocalization].[syscolumns].[C]::[xtype], 
[EbsLocalization].[syscolumns].[xtype] 
or [EbsLocalization].[systypes].[C]::[xtype]. 
[/home/runner/work/sql-server-dev-test/sql-server-dev-test/
database-lighthouse-dev/database-lighthouse-dev.sqlproj]

Code Analysis - SonarQube Scan SQL

The GitHub repository also includes an automation called SonarQube Scan SQL which allows you to perform code analysis of the SQL code (objects) on-demand.

To trigger a scan:

  1. Open the Actions tab in the GitHub repository.
  2. From the left side menu, select the SonarQube Scan SQL action.
  3. Click Run workflow.

Once the SonarQube scan finishes, you will receive an email with either the attached results or a link to the SonarQube project.