Deploying A SQL Server Database Onto An On Prem Server Using Azure DevOps

In previous posts I showed how to build and deploy ASP.NET 4.7.2 applications to an on-prem server running Windows 2016. Now, the next step in the journey is to deploy a Microsoft SQL Database onto an on-prem server. In this post I will show you how to deploy a SQL Database Project into a Windows 2016 on-prem sever running an instance of SQL Server 2017 by leveraging Azure DevOps.

Requirements

The target server that I will be using has been pre-configured with all the necessary components to support the database deployment. I will not go over the how-to for the local configuration of each component, as this post is really just about deploying the database, but I will enumerate my configuration at a high level so you can install an configure your own server. You will need the following local installation and configuration in order to follow along with this blog post:

• Windows Server 2016 https://www.microsoft.com/en-us/evalcenter/evaluate-windows-server-2016
• SQL Server 2017 https://www.microsoft.com/en-us/sql-server/sql-server-downloads
• SQL Servert Data Tools SSDT https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017#ssdt-for-vs-2017-standalone-installer
• Latest version of AdventureWorks OLTP database: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017

For demo purposes, I will be using a pre-configured SQL Login to authenticate against my demo server database. Therefore, make sure that the account you use on your SQL server for releases has the proper permissions to conduct deployments. For more information about SQL Server Permissions see the official documentation.

The SQL Code

I decided to use the Adventure Works sample database from Microsoft because it is simple to use, as all of the creation scripts are available, and lastly because it was easy to create a sample SQL Project based on it and upload it to Git source control in Azure Repos. Note that the release configuration that I will create in this post can be applied to similar SQL Database Projects, so you can use this post as a starting point. Here is what my Adventure Works SQL Database Project looks like:

Also, I have already uploaded the source code to Azure Repos and have created a YAML based pipeline for it:

The YAML based build configuration for this project was actually quite simple. And although I am sure more can be added to it, this is all the code I used to create the Build Pipeline in YAML:

Configuring the YAML build to run, renders a pretty clean set of build artifacts. The one that I am interested in is the AdventureWorksDataModel.dacpac file:

There are a number of ways to deploy a SQL Project to a database, such as generating SQL scripts as a build artifact. However, I will be using a dacpac file based deployment to make things simpler for the purposes of this post.

Creating A Database Release Definition

I will use the same target server as in my previous post, as it already has the agent installed on the server. First, since you were using tags in our Deployment Groups before, if you are following along from a previous post, you should add another tag to the target server for later reference in the Release Pipeline creation:

After you have done that, navigate to the Pipelines/Releases and click on New Pipeline:

On the Select a template textbox search for 'sql' and then select the IIS website and SQL database deployment template and click Apply:

You will then see the first stage being created, you can just leave the default name and click on the X to exit out to the main stage configuration screen:

Configuring The Database Release

Now you will first configure the artifacts for the release. Click on the Add an artifact box and select the artifacts for the DatabaseDeployment build you created earlier:

Configure your settings as follows and then click Add:

You should now see that the Artifacts configuration box contains the _DatabaseDeployment build artifacts, and the Stages section contains Stage 1 with 2 jobs and 3 tasks in it, so go ahead and click on the jobs and tasks so you can configure them:

Since you will only be working with the SQL Deployment job in this release pipeline, you can delete the IIS Deployment job in Stage 1:

With the extra IIS Deployment job now removed, you first configure Stage 1 with the Database Name:

Then, you can configure the Deployment group and Required tags for the SQL Deployment job as follows:

Additionally on this job, the Artifact Download should already be set to use the latest version of the build and _DatabaseDeployment artifacts:

Next, configure the SQL DB Deploy task by first setting the Server Name and Database Name as follows:

Note that I used 'localhost' for the Server Name, which could have also been a specific server name or a server/SQL_Instance name, but I will keep it simple for now and just use the localhost name as default.

The next set of settings are the credentials to authenticate with SQL Server. You can choose between Windows Authentication and SQL Server Authentication, in my case I chose SQL Server Authentication and will use my pre-configured SQL Login credentials:

As a last step, update the name of the Release to Database Release Pipeline and Save your pipeline changes:

Creating The Database Release

Once the release definition is in place, you are now ready to create the actual release to the target server. From the same screen where you finished the Database Release Pipeline configuration, click on Release and then click on Create a Release:

Select Stage 1, an optional Release description and click Create:

The Release-* is queued up, therefore, click on that link to follow it to the Pipeline screen:

On the Pipeline screen, click on Stage 1 configuration box to bring up the Stage 1 side screen. From there click on Deploy cloud icon, and continue to the Stage 1 side screen where you can add an optional Comment and then click Deploy:

After that you will be directed to the Deployment Process screen where you can monitor the deployment:

Finally, the SQL Deployment succeeds:

And you can confirm that your database was successfully deployed by accessing it from your SQL Server and taking a look under the hood:

Summary

In this post I showed how you can deploy a SQL Database Project from Azure Repos, leveraging dacpac files, into a SQL Server 2017 on-prem database through Azure Build & Release Pipelines.

Until next time, live long and deploy often. :)