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.
Now you can use this post to get ideas for SQL Server installations by itself. In addition, you can also use it alongside my previous post about setting up your laptop as an Azure DevOps agent. Which you can read about in detail here.
I have based this post on doing the setup using Windows 10 Professional. However, some of these concepts can be done on other operating systems.
In reality, there are a few reasons to test database deployments from Azure DevOps to SQL Server instances locally. For example, if you know you must configure self-hosted Azure DevOps Agent Pools in a certain environment to deliver to SQL Servers installed across a network.
In addition, using SQL Server locally saves you having to use Azure credit by testing in databases hosted in Azure. Whether that’s in Azure SQL database of SQL Server instances installed on virtual machines.
Now before I go any further I do want to point out that to do the below you should have local administrator rights on your laptop. With this in mind, let’s go through the guide.
Multiple SQL Server databases
In reality, there is one very simple way you can replicate deploying updates to multiple copies of the same database. You can do it by installing one SQL Server instance on your laptop and deploying to multiple databases on there.
For example, the below represents separate databases you can deploy updates to in integration, staging and production stages. All within the same instance.
However, there are a few issues doing it this way. 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.
Install multiple SQL Server instances
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. As you can probably imagine there are a fair few issues with this scenario.
For a start, all those SQL Server instances, and their services will take a lot of resources on your laptop. So, unless you have a powerful laptop you will 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.
SQL Server Virtual machines
One way you can work around the above scenario is to install multiple virtual machines. Now the first thing you might realize is that this will also take up a lot of compute and storage.
In reality, I use to use this method myself in the past using Hyper-V. To reduce the amount of storage the virtual machines used in Hyper-V I use to used parenting 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 with tips in here.
Multiple Docker containers
Installing Docker 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 from Azure DevOps.
With this in mind, I recommend this option if you wish to install multiple SQL Server instances on your laptop.
You can have Docker running using either Hyper-V or WSL 2 instead. I recommend using WSL 2 instead for better performance as per below setting.
Tip if upgrading Docker Desktop
A quick tip for those of you who have already installed Docker Desktop and are about to upgrade.
If you upgrade Docker Desktop and get the option to enable WSL 2 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 you had when Hyper-V was used.
After doing this you can install Docker on your machine. You can do this easily from downloading from the Docker website here.
For now, you can leave it with the defaults. 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.
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 here.
However, if you have Azure Data Studio installed you have an alternative option. You can use the deployment wizard instead. You can start this by clicking on the ‘Deploy a server’ option in the Welcome tab.
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. Of course, the same goes for port numbers as well.
Once you have entered these details you click ‘Open Notebook’ to have a prepared Notebook opened for you. However, be sure you have a secure password before doing this. Otherwise you will get the below error.
From there you can simply run each cell at the time. I strongly advise against clicking the ‘Run all’ option in this notebook. Because the last cell stops and removes the container.
Changing SQL Server Docker image
If you prefer to change the docker image to use another version of SQL Server instead 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.
After you have done that you can change the 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. Scroll to the end of the line that starts with the word ‘template’ and change the image there as well.
From there you can run the cells required to deploy the alternative image.
Three SQL Server containers
Once you have installed the containers you can easily deploy to them using Azure DevOps if your laptop is configured as an agent.
You can do this within Azure DevOps by using the ‘SQL Server database deploy’ task within 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, use the ‘pool’ option to make sure whichever agent pool the service on your computer is running in is used.
Secondly, make sure you get the syntax right when specifying the Docker container. Like the below example.
Once you have done that you are ready to deploy to multiple instances in different stages as below within your pipeline.
In case you were wondering, you specify different stages in the yaml by using the stage syntax as per the below example.
- stage: Staging dependsOn: Integration displayName: 'Staging'
For those of you who have used ARM templates before the dependsOn syntax will be familiar to you. It basically adds a dependency from another stage.
In addition, you can give your stages a more user-friendly name using the displayName syntax. Just remember that name might now show so well graphically if it is long. However, using the displayName syntax with your tasks as well can make reviewing your deployment logs a lot more user-friendly.
You can find out more about the YAML schema reference for Azure pipelines here.
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 is to just to deploy updates to databases running on one instance.
Second option is to deploy to multiple docker containers. Personally, I prefer deploying to containers for various reasons.
Of course, it goes without saying there are alternatives if you want to test deploying SQL Server database updates somewhere.
For example, to one or more development instances on your network or to instances on virtual machines Azure. In addition, if the updates are for Azure SQL Database you can deploy the updates to test databases using your Visual Studio subscription instead.
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.
Of course, what you decide to use depends on various factors. In reality, you have a lot of options and these are my recommendations. Personally, I use Docker containers to test SQL Server deployments locally.
As always, feel free to reach out to me if you have any questions.