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:

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:

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

First we need to specify a connection to the database. For this, click ‘select connection’ and then specify 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:

Modify Project Properties

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

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:

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:

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:

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:

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):

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:

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:

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

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

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

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

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

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

Create a release definition and enable continuous deployment

For this, we’ll go to Build and Release tab -> Releases -> New 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:

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

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.