Lessons learned when deploying multiple databases to Azure with VSTS

It's had been a while since I worked into Visual Studio Team Services (VSTS), and it was a real pleasure to get back in that area. For the solution I was working on, we need to keep the current database up and running while deploying a new version. For this purpose, we decided to append the release number to database name (ex: MyDatabase363). In our Build and Release processes, we needed to identify which databases are from the last release. In this post, I will show what I did using an inline PowerShell script to get that number and set it as an environment variable so it can be accessible by other tasks.

To get started let's add a Azure PowerShell task to our build definition. In this post, I use a build process but of course this is also valid for release process. To find the task quickly, use the search text box. I will add two of those, one to get the number, the second to validate that this value is now set as an environment variable and readable from other tasks.


Now it's time to set the first task. Fill-out all the properties and select Inline Script as the Script Type. It should look like this.



Let's examine the code.

Get last Release Number

$matchingResources = Find-AzureRmResource -ResourceNameContains "mydatabase" -ResourceType "Microsoft.Sql/servers/databases"

$lastRelease = 0

ForEach($resource in $matchingResources)
{
    if ($resource.ResourceName -match '(\d)+$') {
        if($lastRelease -lt $matches[0]){
            $lastRelease = $matches[0]
        }
    }
}
Write-Output "The last release number is:  $lastRelease"
Write-Output ("##vso[task.setvariable variable=lastReleaseNumber;]$lastRelease")

On the first line, I use the Azure PowerShell commandlet Find-AzureRmResource¹ to get an array of all the databases currently online in my resource group that contains a specific string. In this case, it's the name of the database without the release number. Then I will loop through all returned resources and using a very simple Regex to extract the release number and keep the biggest one (the last release).

To close that script we have two outputs. The first one is to give feedback in the logs, because it's always good to have some information there. The second one look more complicated, but if you split it, it's easier to see what's happening. In fact, we are producing a VSTS (previously called Visual Studio Online this is why it's VSO) command to initialize a variable ##vso[task.setvariable variable=lastReleaseNumber;] And of course, assign to it our last release number $lastRelease

To validate that we really successfully found our last release number and assigned it to a variable, let's try to read it back but from another step. That will be easily done this code in the other step created:

Validate the last Release Number

$number = $env:lastReleaseNumber

Write-Output "Confirmation, the last Release Number is:  $number "

The only thing missing before we can run our test is to create that environment variable. To to it simply go in the Variables tab and add it there.



It's all set, run the build and you should see something similar in your logs.

References

1. AzureRM PowerShell 4.3.1 documentation ↩