Continuous Integration and Deployment for SQL Database using SSDT and VSTS

The combination of technical and cultural processes behind databases makes automation difficult. Databases has a state associated with them, so you cannot blow them away like application code and create again from scratch without losing the data. Managing change in a way that doesn’t impact the data is very problematic. Combine that with the cultural issues, the silos, it creates a really difficult problem. There are some general best practices that you can apply to tackle a lot of this complexity, but any time you try to design the solution and get into the technicalities, a lot of time you end up implementing something very specific to a particular type of database. In this blog post, we’ll learn how to use SSDT to implement continuous integration and deployment for SQL database Schema to take some of these worries away.

Create a Database Project in Visual Studio

We’ll start with creating a database project in Visual Studio. For this, open Visual Studio -> File -> New Project -> Select Sql Server Database Project:

Create new sql server project inside visual studio
Create new sql server project inside visual studio

Provide a name for database project and directory to store project data. We’ll also select option for ‘Create a new Git repository’ so that we can treat database as source code as well. Thereafter we can use Git to do version control on database files.

Import Existing Database Schema

This step is optional and is not required if you want to start with a blank database. However, most of the time, there is already a database implemented and you would want to import its schema and build further on it. I am going to use an Azure SQL database for this purpose.

To import database schema, right click on project, select import and then select database:

Import existing database into sql project
Import existing database into sql project

This will open a pop-up wizard asking how would you like to import:

Import database wizard
Import database wizard

First we need to specify a connection to the database. For this, click ‘select connection’ and then specify connection details:

Specify sql database connection details
Specify sql database connection details

Once specified, click connect on above wizard. We’ll leave other settings as such and then click import. This will close existing wizard and start importing schema. It will also launch another wizard to show you progress. Click finish once its complete:

Import database schema completion
Import database schema completion

Modify Project Properties

Right click on project in Visual Studio, select properties and open Project Settings. Set target SQL server version here:

Set target sql server version
Set target sql server version

In our case, it will be set to Azure SQL Database v12.

Understand Sql Schema Imported

Since, we have specified to import sql schema in the format schema\object, we would see a hierarchy like below:

Imported sql schema files
Imported sql schema files

This tells us that we have only one schema i.e. the default one dbo. Then we have few stored procedures and tables inside that. Note that each sql schema object is created as separate file. Since this is a dummy database for demo, there is not much in it. If we click a file t.sql, it will open the table syntax and design view in the central pane:

Sql table design view and syntax
Sql table design view and syntax

We can now go ahead and start editing the schema files here.

Make changes to SQL schema and commit changes

For demo purposes, we’ll alter table t as below:

Alter existing table t
Alter existing table t

As you can see, we have modified code to update name of column b to bnew and added a new column c of datatype varchar(50).

We’ll also a new table t3 with below schema:

Add a new table t3
Add a new table t3

Once done, we’ll commit our changes(Since this is our first commit, you are seeing all sql files as they have not been added to project yet. You can safely neglect this):

Commit sql changes
Commit sql changes

Push schema changes to Central repo

We’ll now push our schema changes to a centralized repo in Source control management system of our choice. I’m doing it here with VSTS, but you can use any that is already implemented in your environment.

For our demo purposes, we have created a empty repo named as DatabaseProjectDemo and going to use the same for publishing this project:

Publish sql schema repository to empty git repo
Publish sql schema repository to empty git repo

Once its published successfully, you should be able to see your files in the VSTS server.

Create a build definition and enable Continuous Integration

For this, we’ll go to Build and Release tab -> Builds -> New definition:

Create a new build definition inside VSTS
Create a new build definition inside VSTS

We’ll start with an empty process. Set few initial things as below (Set these as per your environment and requirements):

Build Name: DatabaseProjectDemo-CI
Agent Queue: Hosted VS2017
Repository: DatabaseProjectDemo
Branch: Master
Enable checkbox for continuous integration
Build Number: $(Date:yyyyMMdd)$(Rev:.r)

Now, add a msbuild task and modify it as below:

Project: DatabaseProjectDemo.sln
MSBuild Arguments: /t:build /p:CmdLineInMemoryStorage=True

Add MSBuild task
Add MSBuild task

Now, add a copy file task and modify it as below:

Contents: ***.dacpac
Target Folder: $(build.artifactstagingdirectory)

Add file copy task
Add file copy task

Now, add a publish task and modify it as below:

Path to Publish: $(build.artifactstagingdirectory)
Artifact Name: drop

Add publish task
Add publish task

Let’s save and run our build definition. If everything goes smooothly, you should be able to see a successful run:

Successful run of build definition
Successful run of build definition

Create a release definition and enable continuous deployment

For this, we’ll go to Build and Release tab -> Releases -> New definition:

Create a new release definition
Create a new release definition

We’ll start with an empty process. Set few initial things as below (Set these as per your environment and requirements):

Environment Name: Dev
Environment Owner: {Defaults to your id}
Release Name: DatabaseProjectDemo-CD
Add Build artifacts from above build
Enable continuous deployment trigger

Go to Dev environment, add ‘Azure SQL Database Deployment’ task and modify it as below:

Azure Connection Type: Azure Resource Manager
Azure Subscription: {Specify endpoint for azure connection}
Azure SQL Server Name: {Specify Azure SQL Server Name}
Database Name: {Specify Azure SQL database name}
Server Admin Login: {Database username}
Password: {Database Password}
DACPAC file: $(System.DefaultWorkingDirectory)/DatabaseProjectDemo-CI/drop/DatabaseProjectDemo/bin/Debug/DatabaseProjectDemo.dacpac {Will change as per your environment}
Specify Firewall Rules Using: AutoDetect

Let’s save and run our release definition. If you have configured it successfully, we can see that release is successful:

Release status successful
Release status successful

When checking logs, we can find details of what was deployed:

Release logs generated
Checking release logs generated

In this post, we learned how to use SQL server project to enable CI/CD process for deploying SQL schema. We can now treat database schema as regular source code and implement CI/CD on it. Again, a slight different version of this can be used to deploy sql schema for on-prem sql servers as well.

4 thoughts on “Continuous Integration and Deployment for SQL Database using SSDT and VSTS

  1. hi Mohit..Thanks for this blog..it helps in doing CI/CD for Azure SQL DB. But i wanted to know whether we can handle any of scripts using this above steps. I read alter scripts cannot be handled by this way. If yes can you guide how to deploy the scripts using Power Shell and also what other scripts cannot be handled by above steps.

    Like

    1. Hi…Thanks for stopping by. It can handle alter schema statements fine. Only issue is with the rename of column. Writing a blog post on how to do it using PowerShell scripts is on my list but not able to find some time for it. It might take some time considering my current state of affairs. Thanks for understanding.

      Like

  2. Hello Mohit

    I have one question , How we can do the automation of the execution of scripts on the designated server for existing projects ( App and DB) .

    Thanks in Advance!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s