Deploying to multiple SQL Server database types using Azure DevOps faster

Reading Time: 4 minutes

I want to cover deploying to multiple SQL Server database types using Azure DevOps faster. because I have done this recently.

In fact, I done it for the pipeline I discussed in a previous post. Which was able to deploy updates to multiple database types after updating one single database project.

You can read that post in detail here. However, here’s a reminder of what the pipeline does.

• Three SQL Server 2019 instances in three Docker containers. Representing Integration, Staging and Production environments.
• At the same time the Git repository in Azure Repos would sync with a GitHub Repo. Which would then start a GitHub Action to update another database.
• An Azure SQL Database.
• Finally, an Azure Synapse Analytics Dedicated SQL Pool was also updated.

By the end of this post, you will see how I got a pipeline down from ten minutes and forty seconds to the below.

I’m very impressed I managed to get it down to this with a few simple steps. Just to show the timing is correct below is the full pipeline with the timings of each stage.

How

I was able to do this using the below steps.

Unit testing

First thing I did was move the unit testing to be done on my laptop.

I created a new container locally with SQL Server 2019 installed as you can see on the below dashboard. Afterwards I then changed the existing stage to run a unit test against a copy of the database on this instance instead.

Doing this made an immediate difference of an average of around two minutes thirty seconds faster. So, my builds were averaging around seven minutes thirty seconds.

Moved all pools to local

After doing this I changed all the builds that were Microsoft-hosted to be self-hosted. Basically, everything then ran off my computer.

Doing this also made an immediate impact. Because my pipeline then started completing at an average of five minutes.

Now, that is more than half the time it took for the live demo. In reality, I was happy and could have stopped there. However, I decided to see if I could take it further.

Second laptop

I decided to test running the pipeline with two agents. Because some of the stages could be run in parallel. For example, the stage to deploy to integration and the stage to Sync the repository with GitHub.

However, I decided to leave the deployments to the three SQL Server 2019 containers on the original laptop. To do this I first added a capability to my original laptop in my custom Agent Pool as below.

Afterwards, I changed the yaml I used in the three stages which deployed to the three containers to the below. So that only my laptop would be used.

      pool: 
        name: $(agentpool)
        demands:
          - Docker.Host

In reality, you should only do this if testing like I am. Just keep in mind that this stops it from being easily repeatable.

To get an average I ran the pipeline again a few times again. Now the pipeline was averaging at around four minutes.

I suspect if more of the deployments were parallel this would be faster.

Afterwards

Afterwards, I looked to see if I could make it a bit faster. I looked into various options. For example, I set priority of the agent executables higher on both of the laptops. Which only made the pipeline slightly faster.

I even ran the pipeline manually selecting the ‘Enable system diagnostics’ setting to get verbose logging.

Doing this led me to an interesting discovery. Because it appears that in the ‘Azure SQL Database deployment’ task there is a wait time of 10 seconds in place for after the pipeline settings are in place.

Of course, this is fine for a handful of deployments. However, this can be an issue if looking to use this for a large number of deployments.

With this in mind, I tested removing the firewall and additional argument additions from the ‘Azure SQL Database deployment’ task for the three types of Azure databases. Of course, I did check the firewall settings existed for the Azure SQL database and the Azure Synapse SQL Pool before running the pipeline again.

Doing this reduced the average time of my pipeline down to three minutes thirty seconds. With the fastest time being three minutes 22 seconds as you can see here.

In theory

In theory there’s other things I could do.

For example, I could hard code in sqlpackage instead of using the deployment task. I suspect this will save some further time. However, I do know doing this is frowned upon by some.

Other options include doing things like test using nested pipelines.

Final word

I hope me covering deploying to multiple SQL Server database types using Azure DevOps faster has been helpful. In addition, I hope it has given some of you ideas of your own.

Personally, I am happy to get it down from over ten minutes to three minutes twenty-two seconds. I promise that I will demo this the next time myself and Sander co-present ‘Azure DevOps Duet’ in 2021.

If you have your own ideas or views about this feel free to reach out to me.