Automate your Database Deployments for SQL Server using Azure DevOps Releases

In my previous blog post, Automate your Database Deployments for Azure SQL using Azure DevOps Releases, I described how to automate your database deployments for Azure SQL. Now, in this blog post, we are going to take a look at how we can automate deployments for SQL Server.

Since the setup is a bit different in comparison with Azure SQL, we will start with the prerequisites first.

Quick Overview

Prerequisite

Step-by-step Example

—Installing an Azure DevOps Agent

—Creating the Release Pipeline for SQL Server

Next Steps

Prerequisite

In this section, I will be repeating myself, sorry about that, as I mentioned in my previous blog post. Before you can move forward to the step-by-step example, you should already have done the following:

1. Setup your Database Project
2. Saved your Database Project in Azure DevOps
3. Automated the Build process

If you didn’t do this yet, you can use the following resources to get started:

To get started with Database Projects & Azure DevOps, you can find all related blog posts using the following link: Getting Started With Database Projects & Azure DevOps

If you just need to set up your automated Database Project Build process, you can use the following blog post that will guide you through the process: Automate your Database Builds – Using Azure DevOps Pipelines

Step-by-step Example

Now that you are completely up and running it is time to take a look at the requirements to create your Release Pipeline to deploy to SQL Server.

As a first step, we will need to set up an Azure DevOps Agent to be able to make a connection to our On-premise SQL Server. Even if you are running a SQL Server hosted on an Azure VM you will need to go through this process.

For this blog post, I will be using my installation of SQL Server 2019 on my local machine. I will be installing the Azure DevOps Agent on my local machine and guide you through the process.

Installing an Azure DevOps Agent

To get the Azure DevOps Agent installed, we first navigate to my Azure DevOps Project and go to Project Settings. To continue, we navigate to the Pipelines section and look for Agent pools.

As soon as we click the Agent pools option, we will be navigated to the Agent pools section, where we can create a new Agent Pool by choosing Add pool in the top right corner.

As a next step, we choose to create a new Pool to link to, and as Pool type, We select Self-hosted. Provide a name to the Agent pool, a description and then confirm by clicking the Create button.

Now we can see the On-Premise Agent Pool showing up in the Agent pools overview

To continue, we click the On-Premise Agent Pool and click on the New agent button.

By clicking this button, we will be redirected to the Get the agent walkthrough. First, we need to download the agent software, which is a .zip file. When we extract the .zip file, we can see that there are a couple of command line files included. Though, extracting the .zip file is not required, since the walkthrough provides very useful PowerShell scripts to do all the hard work for us.

Just follow the steps in the Walkthrough, I will be highlighting a couple of important things here.

As a first step, when you run the config.cmd script, you will need to provide a server URL. To get this URL, navigate to your Azure DevOps Organization in your browser and copy the URL. In my case, for this blog post, https://dev.azure.com/oliviervansteenlandt/

As a next step, we need to specify the authentication method, for now, I will be using PAT (=Personal Access Token) as the authentication type. If you don’t know how to create a PAT, you can find all the required steps in detail by using the following link: Use personal access tokens – Azure DevOps | Microsoft Learn

PAT is the default authentication type for the Azure DevOps Agent, so we can just hit ENTER. Now we need to enter the generated PAT.

As soon as we have provided the information, the agent is connecting to the Azure DevOps server.

As a next step, we need to specify the agent pool which we would like to register our agent into. In our case, this will be our On-Premise Agent Pool, after we hit ENTER, we need to specify the agent name, you can keep the default or you can specify the name. I will be naming my agent: OnPremAgent.

As soon as we confirm the agent name, the Azure DevOps Agent scans for capabilities and connects to the server. As you can see in the screenshot above, we successfully added the agent to the Agent Pool.

To continue, I just keep the default work folder and I choose to run the agent as a service. As a next step, I keep the default for the enable SERVICE_SID_TYPE_UNRESTRICTED for agent service.

Now, we need to specify the user account we want to use to run the Azure DevOps Agent. For this blog post, I will be using the default, which is the NT AUTHORITY\NETWORK SERVICE, but It probably is a better idea to create a specific service account to run this service.

Since I want the service to launch as soon as the configuration is finished I hit enter.

Now, we can go back to the On-Premise Agent Pool in Azure DevOps and check if our Agent is up and running. Please be aware that this can take a moment.

As soon as the agent is up and running we are good to go to the next step and create our Release Pipeline.

Creating the Release Pipeline for SQL Server

To build the Release Pipeline for SQL Server, we can follow nearly all the steps mentioned in my previous blog post: Automate your Database Deployments for Azure SQL using Azure DevOps Releases.

So what are the differences?

When creating your Agent job in your Release Pipeline, you will need to select the On-Premise Agent Pool instead of the Hosted Agent Pool.

By changing to the On-Premise Agent Pool, you will be able to deploy to your local instance of SQL Server.

Besides selecting the right Agent pool, you also need to use the SQL Server database deploy task instead of adding the Azure SQL Database deployment task.

Using this task, you can also use Windows Authentication to connect to your SQL Server.

If you are using Windows Authentication, you will need to give the account that is running the Agent on your machine the required permissions on your SQL Server instance.

If you want to find out which permissions you require: Deploy a Data-tier Application – SQL Server | Microsoft Learn

Next Steps

Now that we have successfully set up our deployment automation, we will be diving into approval flows later on. Before we get into approval flows, we will be transitioning our deployment pipelines to YAML pipelines to be able to treat our pipelines as code.