SQL Server installation options to test Azure DevOps deployments locally

Reading Time: 9 minutes

In this post I want to cover SQL Server installation options to test Azure DevOps deployments locally. Because I have been asked about this.

By end of this post, you will know various options to install SQL Server locally. Which you can use to test deploying SQL Server updates to local databases using Azure DevOps.

To clarify, when I say test Azure DevOps deployments locally I mean test deployments that are done using the Azure Pipelines service within Azure DevOps. However, a lot of people refer to them as Azure DevOps deployments or deployment pipelines.

Now you can use this post to get ideas for SQL Server installations to test this by itself. However, if all this is new to you I recommend that you also read my previous post about how to set up your laptop as an Azure DevOps agent to test SQL Server deployments.

I have based this post on doing the setup using Windows 10 Professional. However, some of these concepts in this post can be done on other operating systems as well. For example, some of the SQL Server installation options can also be done on Linux.

Why test Azure DevOps deployments locally?

In reality, there are a few reasons to test database deployments from Azure DevOps on SQL Server instances locally.

For example, because you already know that you must configure self-hosted Azure DevOps Agent Pools in a certain environment to deploy updates to SQL Servers databases within a network.

In addition, testing on local SQL Server databases saves you having to use Azure credit by testing in databases hosted in Azure. Whether that’s in Azure SQL database or SQL Server instances installed on virtual machines.

Local Administrator rights

Now before I go any further, I do want to point out that you need to have local administrator rights on your laptop to do a lot of the below. With this in mind, let’s go through the guide.

Multiple SQL Server databases to test Azure DevOps deployments

In reality, when considering SQL Server installation options to test Azure DevOps deployments locally there is one very simple way to test deploying updates to multiple copies of the same database.

Which is to install one SQL Server instance on your laptop and deploying to multiple databases within that instance.

For example, the below represents separate databases you can deploy updates to in integration, staging and production stages. All within the same SQL Server instance.

However, there are a few issues with doing this. First of all, it does not really represent deploying updates to different instances.

Secondly, they’re not displayed in a logical order which can be annoying. Of course, you can easily workaround this locally by putting numbers at the start of the database name.

Install multiple SQL Server instances locally to test Azure DevOps deployments

One initial option you can use to make sure you deploy updates to multiple instances is to install multiple SQL Server instances locally on your laptop. However, as you can probably imagine there are some issues with this scenario.

For a start, all those SQL Server instances and their services will take up a lot of resources on your laptop. Even if you install only the essential services. So, unless you have a powerful laptop you can encounter performance issues.

Plus, if you have to install various SQL Server versions it’s going to become a headache very quickly. In reality, I do not recommend this option at all. Either do the previous option or one of the ones which follows next.

Test Azure DevOps deployments using SQL Server Virtual machines

One way you can work around the above scenario is to install multiple virtual machines to test Azure DevOps deployments locally. Now the first thing you might realize is that this can also take up a lot of compute and storage.

In reality, I have used this method myself in the past using Hyper-V. In addition, to reduce the amount of storage the virtual machines used in Hyper-V I used differencing disks.

Since the introduction of containers and Docker this has become a less popular option. However, you can still read an old post of mine that contains tips about running SQL Server within Hyper-V on your own laptop.

Multiple Docker containers with SQL Server installed

Installing Docker containers locally has increased in popularity a lot as of late. Because they can be easy to install and are a lot more lightweight than virtual machines. Plus, there are also very easy to deploy updates to using Azure DevOps.

With this in mind, I recommend this option if you wish to install multiple SQL Server instances on your laptop to test Azure DevOps deployments locally.

Docker Desktop can be used on your Windows computer as long as it has either Hyper-V or WSL 2 enabled. I recommend using WSL 2 instead for better performance as per below setting.

Recently Docker have changed their terms of use for Docker Desktop. You can read more about how the changes apply to Docker Desktop in section 4.2 of the Docker Subscription Service Agreement.

Tip if upgrading Docker Desktop

Just a quick tip for those of you who have already installed Docker Desktop and are about to upgrade.

If you upgrade Docker Desktop and you decide to move from Hyper-V to WSL 2 then I highly recommend uninstalling the Hyper-V service afterwards. I have done that myself and it’s fine. Just remember to either migrate over or recreate the containers that you had when Hyper-V was used.

Installing Docker locally to test Azure DevOps deployments

On the other hand, if you are new to Docker you can go to the Docker website to install Docker Desktop on Windows.

For now, you can leave it with the default settings. However, later on you might want to experiment with changing the resources for your containers. Especially if you intend to run demos from your laptop.

Another thing to keep in mind is that you can also change to use Windows containers. For now, I recommend leaving Docker to use Linux containers.

SQL Server containers

After installing Docker, you are all set to use local containers. Now, there are multiple posts online on how to install and configure local Docker containers with SQL Server installed. For example, the guide from Andrew Pruski about running SQL Server 2019 CTP in a Docker container.

However, if you have Azure Data Studio installed you also have an alternative option. You can use the deployment wizard that comes with it. You can start this by clicking on the ‘Deploy a server’ option in the Welcome tab as shown below.

From there you can select to deploy a SQL Server container image.

After doing this you can enter your container name, password and port to get going. Make sure you rename your container to something more sensible. Especially if you intend to install multiple containers. Make sure you change the port number for each container as well.

Once you have entered these details you click ‘Open Notebook’ to have a prepared Notebook opened for you. However, be sure the password you use is complex before doing this. Otherwise, you will get the below error when you click ‘Open Notebook’.

I strongly advise against clicking the ‘Run all’ option at the top of this notebook. Because the last cell stops and removes the container.

Instead, I recommend running each cell by itself in the notebook. You can do this by clicking the icon that looks like a play button on the left-hand side of each cell.

Changing SQL Server Docker image

You might want to change the container image. For example, from the standard one based on the Ubuntu Linux distribution to one based on RHEL. To change the docker image to I recommend the following.

Open up one of my old posts which explains how to find the list of all the current docker images for SQL Server. Which you can read in detail here.

Once you have found the one you want you can change the default container image to the one you want in the ‘Pull the container image’ cell below.

Once you have done that click the down arrow in the ‘Start a new container’ cell to extend it. Find line that starts with the word ‘template’ and scroll to the end of it. So that you can change the image there as well.

From there you can run the cells required to deploy the alternative image.

Testing Azure DevOps locally

Once you have installed the containers you can easily deploy to them using Azure DevOps if your computer is configured as an agent. You can read a bit more about how to configure your computer as one in my post about how to set up your laptop as an Azure DevOps agent to test SQL Server deployments

You can look to deploy to your local SQL Servers within Azure DevOps either by going into an existing pipeline in Azure Pipelines or by creating a new one. If you are going to create a new one, I recommend that is YAML based.

For help with the YAML syntax you use in your pipelines you can use the Azure Pipelines YAML schema reference.

Anyway, inside the pipeline you can then add the ‘SQL Server database deploy’ task to your pipeline. When you are editing your pipeline in Azure DevOps you can search for it on the right-hand side. Once you have selected it you can enter the relevant details for it before adding it to your pipeline.

Two things to remember to do if you are looking to deploy this locally in Azure DevOps using this task. First of all, change the ‘pool’ option in the pipeline to whichever agent pool you configured your computer to be in.

Secondly, make sure you get the syntax right for the server name in the task when specifying the Docker container that you want to deploy to. Like the below example, which shows that you need to use the port number for the container.

ServerName: 'localhost,1443'

Once you have done that you are ready to deploy to multiple SQL Server instances into the different stages as below within your pipeline.

Stage syntax

In case you were wondering, you can specify the different stages in the YAML pipeline by using the stage syntax. For example, the below example for a staging environment.

- stage: Staging
  dependsOn: Integration
  displayName: 'Staging'

In the above example the staging stage depends on the integration stage. For those of you who have used ARM templates before the dependsOn syntax will look familiar. It basically adds a dependency on another stage.

In addition, you can give your stages a more user-friendly name using the displayName syntax as above. Just remember that if the name is long it might not be displayed properly. However, using the displayName syntax with your tasks can make reviewing your deployment logs a lot more user-friendly.

I have an example of how a YAML file looks for SQL Server deployments in one my forks of the sqwatch GitHub repository. Which includes the stage syntax as well. It is in a YAML file called azure-pipelines-dacpac.yml.

Recommended SQL Server installation options to test Azure DevOps deployments locally

To clarify, I recommend one of two options if looking at SQL Server installation options to test Azure DevOps deployments locally on your own machine.

First option is to just to deploy updates to databases running on one SQL Server instance.

Second option is to deploy to multiple docker containers that are running SQL Server. Personally, I prefer deploying to containers for various reasons.

Alternative SQL Server options to test Azure DevOps deployments

In reality, there are alternatives if you want to test SQL Server database updates using Azure DevOps.

For example, you can test SQL Server deployments using one or more development instances on your network or to virtual machines in the cloud.

In addition, if the updates are for Azure SQL Database you can deploy the updates to Azure SQL Databases that you have created using the Azure credit from your Visual Studio subscription instead.

Final words about SQL Server installation options

I hope this post about SQL Server installation options to test Azure DevOps deployments locally has given you some ideas. In addition, I also hope my tips have helped in other areas as well.

Of course, what you decide to use depends on various factors. In reality, you have a lot of options and these are just my personal recommendations. Currently, I use Docker containers to test SQL Server deployments locally.

As always, feel free to reach out to me if you have any questions or comments.