Continuous Integration and Continuous Deployment (CI/CD) – SQL Server Database CI – Part 1

In this post, we are going to discuss how we can enable continuous integration and continuous deployment for a SQL Server Database project using Azure DevOps Server. For this demo, we will be using a SQL Server instance running on-prem along with a locally installed Azure DevOps Server. Continuous integration and continuous delivery (in short CI/CD) is very common in an application development process. We need to create an application using one of the IDE like Visual Studio or PyCharm and then we need to create a Git repository to host the source code. Finally, we configure a CICD pipeline which typically follows the below steps when a push event occurs in the repository:

1. Continuous Integration pipeline steps:
   1. Pull the latest code from Git or any Source Code repository
   2. Compile the source code
   3. Build the source code
   4. Perform Unit/Smoke/Integration test
   5. Publish the test report
   6. Publish the artifacts (DLLs/jars)
2. Continuous Delivery pipeline steps:
   1. Take the artifacts from the CI pipeline
   2. Deploy to Stage Environment and verify
   3. Deploy to Pre Prod/UAT Environment and verify
   4. Deploy to the Production Environment and verify

For an application development CICD process, it is straightforward. However, continuous integration and continuous deployment become a bit tedious when we think of a database CICD. Because a database stores data as well as schema which is equivalent to the classes in an application. For this reason, Database CI/CD process is a bit different than an application CI/CD process.

Azure DevOps and Jenkins both facilitates industry standard CI/CD pipelines which can be configured to implement a CI/CD pipeline for a SQL Server database. However, Jenkins user interface has a legacy look and feels whereas Azure DevOps has a modern user interface with an advanced look and feel. Azure DevOps also facilitates some other functionalities like Azure Repo, Azure Board, and etc other than the CICD build and release pipelines.

Azure DevOps has two offerings:

1. Azure DevOps Server – Formerly TFS – On-Prem Version
2. Azure DevOps Services – Formerly VSTS – Cloud Version

State-Based vs Migration-Based Approach in Database CICD

For this tutorial, we are going to use a State-based deployment approach rather than the migration first deployment approach. In a state-based deployment approach, the source code represents the current state of the database. The database project contains all the database objects like procedures, tables, triggers, views, and etc into separate .sql files. In this approach, we don’t write alter statements and simply defines the expected state for that object. During deployment, a tool (sqlpackage.exe) compares the DACPAC file (gets generated during the build process) with the current database state and generates the incremental script to be deployed on that instance. If we want to recreate the database in a new environment, we can simply deploy this database project from the repository to the new environment.

In a migration based approach, we import the entire database script in a single file. All the objects of the database like procedures, triggered, views, and etc will be created in a single .sql file. For each ongoing change, we need to create a new folder and files with incremental numbers. To recreate the database in a new environment, we need to deploy each script in the same order.

Continuous integration for SQL Server Database

We are assuming that you have an existing database for which we need to create a CI pipeline. If not, you can download and use the attached script to create a dummy database with 2 tables and 2 views. Click here to download the source code.

In order to implement continuous integration for state-based SQL Server database project using Azure DevOps Server or Azure DevOps Services, we can follow the below steps:

1. Download and install Azure DevOps Server
2. Setup the database project
3. Create a GitHub repository and push the source code to the GitHub
4. Create a continuous integration pipeline using Azure DevOps Server
5. Enable build trigger

Let’s discuss each step in more detail.

1. Download and install Azure DevOps Server

We will be using Azure DevOps Server which can be downloaded and installed on an on-prem machine using this link.

2. Setup the Database project for SQL Server CICD

• Open visual studio. We are using VS 2019 in this example.
• Choose “Create a new project” option.
• In the project type template choose, “SQL Server Database Project” and choose Next.
• Put a name for the project like “Demo_Database_CICD” and point it to the desired location on your machine.
• Click on the “Create” button.
• Right-click on the database project and choose Import -> Database.

• Next, click on “Select Connection” and go to the “Browse” tab. And click on the “Start” button to start the import process. Once it is imported, click Finish.

• The database project in the visual studio should look like this.

• Now, we need to build our project locally to verify that it is working fine. Go to Build -> Build Solution. It should be built successfully.

3. Create a GitHub repository and push the source code to the GitHub

We need to create a GitHub repository and then we need to push our source code into the repository.

• Create a GitHub repository by logging into the GitHub.
• Push the source code into GitHub.

4. Create a continuous integration pipeline using Azure DevOps Server

Now, let’s create the CI pipeline using Azure DevOps Server. To create a continuous integration pipeline, follow below steps:

• Go to Pipelines -> Builds -> New -> New build pipeline.

• First, we need to select a repository. Choose the External Git option and put a connection name and repository URL. if you are using the GitHub Enterprise, you can choose the GitHub Enterprise option and verify your credentials.

• Once you have a verified connection to the repository. Next, we need to select a template. Here, we are choosing an Empty Job.

• It will add a blank pipeline job. Put a name for the pipeline like CICDDemoPipeline.

• Now, click on Agent Job 1 and rename it to PipelineAgent. Do not touch other properties and leave these properties with their default values.
• Next, click on the plus sign of the Pipeline Agent and search for MSBuild. Click on the Add button to add it to the pipeline flow.

• Next, configure the build task. Give it a name and then select the solution/project file which represents the entire solution/project in the project field. Leave the MSBuild version as the latest and select the MSBuild architecture appropriately. Also, you can add additional arguments in MSBuild Arguments “/t:build /p:CmdLineInMemoryStorage=True“. It helps us to build the solution in memory. Finally, it looks like this.

• Next, go to the pipeline Agent and search for the “Copy Files” task and add it. Put “$(system.defaultworkingdirectory)” as Source folder and “$(build.artifactstagingdirectory)” as Target folder value. Also, put “**\bin\Debug\*.dacpac” as contents value. Here, we are copying the DACPAC file from the working directory to the staging directory. This task looks like this.

• Finally, search for a “Publish Build Artifact” task and add it to the pipeline. Put “$(build.artifactstagingdirectory)” as Path to Publish value and rename the artifact as Database. It looks like this.

• Now, we have created a continuous integration pipeline. Go to the top and click on the “Save & queue” button. It will save the pipeline and queue a manual build also.

• To view the latest build history visit the build history tab. We can also verify it by looking into the agent job folder locations.

5. Enable a build trigger

In order to enable a build trigger for the CI pipeline, we can go to the Triggers tab and click on “Enable continuous integration“. Now, whenever a push event will occur to the repository, it will start a new build process automatically.

As of now, we have a working CI demo for the SQL Server database. In the next part, we will discuss how we can create a Release or Continuous Deployment pipeline using Azure DevOps Server.

Thanks for the reading. Please share your inputs in the comment section.