Set up your laptop as an Azure DevOps agent to test SQL Server deployments

Reading Time: 9 minutes

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.

In reality, a lot of this post applies if you are looking to setup your computer as an Azure DevOps agent to test other services as well.

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. Which will cover SQL Server installation options to test Azure DevOps deployments locally.

Why set up your laptop up as an Azure DevOps agent?

Just a quick recap. Azure DevOps agents (also known as Azure Pipeline agents) are used to do the processing of tasks specified in Azure Pipelines. For example, build and release tasks as that are part of a CI/CD deployment.

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 instead of Microsoft-hosted agents. So that you can deploy to SQL Server instances in your own network securely. Which are called self-hosted agents.

I will cover various options that you can choose from 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. For example, development and production environments.

Intended audience

Intended audience for this post is anybody looking to do the above.

In addition, this can help if you are looking to implement your first database deployment pipeline as a team. Because you can forward this to other team members as well. Giving them a head start on any new deployments you want to test.

Like I mentioned earlier, a lot of this post also applies if you are looking to test other services as well.

Own Azure DevOps organization

I highly recommend that you first set up your laptop as an Azure DevOps agent to test SQL Server deployments in an Azure DevOps organization you have created yourself for testing purposes.

You can find out how to do this in detail here.

Software on your laptop

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 locally 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 make a local copy(clone) of the repository used for the pipeline.

In fact, this highlights one of the reasons why you might want to keep an Azure DevOps agent up and running instead permanently instead of having them for one-time use, like the Microsoft-hosted agents.

Because if your repository is large or it has a lot of history it is quicker to synchronize the repository with an existing local copy (clone) of the repository.

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 do when 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 other 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 the Microsoft-hosted agents running Windows Server 2019, 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 provides further details in a GitHub repository called ‘GitHub Actions Virtual Environments‘. 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.

Agent pool

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 that 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 create your own agent pool 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.

Agent installation

You can follow the links here to find out more in the official documentation about Azure Pipelines agents and the installation guides for self-hosted agents. We will be focusing on the instructions for how to install the self-hosted Windows agents.

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.

Multiple agents on your laptop

Like I said, if you only ever intend to ever install one agent then the above instructions 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. If you install all these agents as services the view of your Windows services is going to get very cluttered.

With this in mind, if you are looking to install multiple agents on one computer I recommend not installing them as a service. Instead, organize them in a sensible folder structure and run each one manually.

One way you can do this is to first create a folder for all your agents. When you go to install an agent for a pool create a subfolders with the pool name under 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 pools sensible names. So that you know which subfolder 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 within Azure DevOps

After you have installed your agent and started it you can check the status of it within Azure DevOps. You can do this by 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 for use in Azure Pipelines. You can do this by using the ‘pool’ syntax in your yaml pipeline. 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 within one pipeline.

Like the below example. Which uses a Microsoft-hosted agent for Azure SQL Database 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.

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 both 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 to improve the CI/CD experience, the name was changed to ‘Pipelines’.

However, 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.

Alternative to setting up your laptop as an Azure DevOps agent

Of course, if you are testing deploying to Azure SQL Databases then using the Microsoft-hosted agents is a good option. Especially since Microsoft maintain their hosted servers themselves. Plus, it avoids you having to worry about installing and configuring software on your machine.

However, combining a local self-hosted agent with multiple SQL Server instances locally can be a really valuable way of testing. Especially if you want to save your time and 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 SQL Server installation options to test Azure DevOps deployments locally.

So that you can easily test deploying to multiple stages locally and efficiently. Like in the below example.

Final words about setting up your laptop as an Azure DevOps agent

I hope this post helps some of you who are looking to set up your laptop as an Azure DevOps agent so that you can 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.