In this post I want to cover how you can set up your laptop as an Azure DevOps agent to test SQL Server deployments. Because I have been asked about this this a lot.
By the end of this post you will know how to set your laptop up as an Azure Pipelines agent for use with Azure Pipelines. In addition, have some useful tips and know where to configure your pools in Azure DevOps.
It will also prepare you for a follow up post. So that you can test SQL Server deployments within Azure DevOps to local SQL Server databases as optimal as possible.
One of the main reasons you would want to configure your laptop as an Azure DevOps agent when working with SQL Server is so that you can to test deploying database updates locally. Usually you do this to save costs of creating test databases in Azure.
However, it can also be more practical for other reasons.
For example, because you are planning to create your own agents for use within your infrastructure. So that you can deploy to SQL Server instances in your own network securely.
I will cover various options you have to make the most of local SQL Server installations for these kind of tests in a later post. Because this can be really powerful way to test deploying to multiple databases using an Azure Pipeline with ease. Even if you are looking to test deploying to various environments.
Intended audience for this post is anybody looking into doing the above. If you are intending to encourage other team members to look into this like I discussed here you can forward it on to them as well. Because it will give them a head start on any new deployments you want to test.
Own Azure DevOps organization
I highly recommend you first do this in an Azure DevOps organization you have created for testing purposes. You can find out how to do this in detail here.
Before you look to configure your machine as a local Azure DevOps agent you must consider what software to install on there. In addition, Microsoft lists some prerequisites before installing the agent software here.
At a bare minimum you are going to want Git installed if your repository in Azure DevOps is Git based. Because when an Azure DevOps agent has to work with a pipeline it first has to synchronize with the repository stored within Azure DevOps.
In fact, this highlights one of the strengths of keeping an Azure DevOps agent up and running. Because if your repository is large or has a lot of history it is quicker to synchronize with a copy of the repository on an existing agent.
If you have a Visual Studio subscription then I do recommend installing Visual Studio 2019 on your laptop. Because then you have the same experience as you would have if you use a Microsoft hosted agent with Visual Studio 2019 installed.
Doing this makes it a lot easier for you to swap between your self-hosted agent and a Microsoft-hosted agent.
However, if you cannot do this for licensing reasons you do have a couple of options. For example, you can look to install the trial version of Visual Studio 2019 instead.
Another option is to simply install SQLPackage or MSBuild locally instead. If you are looking to use a third-party vendor tool as part of your pipeline check if you have to install anything else on your agent. For example, client software for SonarQube.
Mimic Microsoft-hosted agents
If you are looking to mimic what is installed by default on Microsoft-hosted agents you can view what is installed on them here.
However, be warned that this is a long list. If you want to mimic these agents exactly you are better off creating them as an image elsewhere.
Microsoft has a guide for how to do that here. However, don’t be confused by the name of the GitHub repository. It’s called ‘GitHub Actions Virtual Environments’ because you use the same build for both Azure DevOps agents and GitHub Actions runners.
Before you go to install the agent locally you must decide which Agent Pool you want to install it in first. You can view your existing Agent Pools by clicking on the Project Settings option within your project as below.
From there you can select Agent pools in the Pipelines section to manage your existing pools.
If you are installing it in an organization you have created using the Default pool is just fine. However, if you intend to do this in an enterprise environment it’s a good idea to test creating your own pool first. It’s also a good idea to do this if you are going to create agents on multiple machines.
Once you have decided which pool your agent will use you can look to install the agent locally.
You can read more about Azure Pipelines agents and find the links to install self-hosted agents on various operating systems here. We will be focusing on the instructions for how to install the self-hosted agent on Windows. Which you can read about in detail here.
If you only intend to have one agent running on your machine these instructions are fine. Below are the current instructions within Azure DevOps for installing the agent with the defaults in Windows. Followed by some tips from me about them.
Like I said, if you only ever intend to ever install one agent then the above are fine. However, you can end up having to install multiple agents on your computer.
For example, you could have an agent for your test organization. In addition, another agent for an organization somebody has decided to share with you.
With this in mind, I suggest creating a subfolder with the organization name under the agent folder after creating the agent folder. You can do this after the start of the ‘Create the agent’ part of the instructions. By using the same md syntax to create the new subfolder.
After you have created the subfolder, navigate to it using the cd command before running the extract command below.
From there you can easily install the agent for that organization to suit your needs. Whether you run your agents as a service or not is entirely up to you.
However, I must stress that if doing this for multiple organizations make sure you give your agents sensible names. So that you know which service is for which organization.
Doing the above for organizing your agents into sensible subfolders is perfectly fine. In fact, I do this myself for multiple organizations.
Checking your agent
You can check the status of your agent after you have installed and started it. You can do this by y going back into ‘Project Settings’ within your Azure DevOps project. From there select ‘Agent Pools’ again and click on ‘Agents’. If your agent is running it will show as online.
Once your agent is showing as online you have set up your laptop as an Azure DevOps agent to test SQL Server deployments.
From there you can click on the agent to see what jobs have run on there. In addition, you can view its capabilities. Which can be very useful if you encounter any issues and you want to check the relevant software is installed.
For example, you can view which version of PowerShell your agent is running. In addition, it can show you if your agent has docker or sqlpackage installed. Like the below example.
Specifying agent pool in yaml
Once your agent is up and running you can specify the pool easily in your yaml syntax. You can do this by using the ‘pool’ syntax. Like in the below example.
variables: configuration: release agentpool: 'kchant Test pool' pool: $(agentpool)
Specifying the pool as a variable makes it a lot easier if your pipeline is complicated. Especially if you want to swap between self-hosted and Microsoft-hosted agents for various jobs. Like the below pipeline example. Which uses a Microsoft-hosted agent for Azure deployments and a self-hosted agent for SQL Server 2019 deployments.
Specifying agent pool in GUI pipelines
You can easily change this if you are using the older ‘Classic Editor’ and ‘Release’ GUI pipelines within Azure DevOps as well.
However, if you are new to Azure DevOps I highly recommend sticking to using yaml pipelines for many reasons. Unless you have a very specific user case. Because you can do builds and releases in your yaml code within Azure Pipelines.
For those of you who are fairly new to Azure DevOps, ‘Pipelines’ was originally called ‘Build’ and was GUI based. After yaml pipelines were introduced the name was changed to ‘Pipelines’. You can still use the GUI functionality by selecting ‘Classic Editor’ when creating a pipeline. You can change your Agent pool in them as per the below example.
In addition, the below shows how to change your Agent pool in the traditional ‘Release’ pipeline.
Of course, if you are testing deploying to Azure SQL Databases using the Microsoft-hosted agents is a good option. Especially since they maintain their hosted servers themselves. Plus, it avoids you having to worry about installing and configuring software on your machine.
However, combining the above tips with multiple SQL Server instances locally can be a really valuable way of testing. Especially if you want to save your Azure credit. I will cover more about this in a later post.
For now, this is the groundwork to set up your laptop as an Azure DevOps agent to test SQL Server deployments. In my next post I will cover optimal SQL Server deployments options. So, you can easily test deploying to multiple stages locally. Like the below example.
I hope this post helps some of you set up your laptop as an Azure DevOps agent to test SQL Server deployments. Especially since I have been asked a few times about it now.
Of course, if you have any follow up questions feel free to reach out to me.
Keep an eye out for the next post as well if you want to make the most of testing Azure DevOps for SQL Server deployments locally.