Contents

Sql Virtual Machine Race Conclusion

A couple of months ago I wrote about a race condition in a SQL IaaS VM using the Azure sqlvm resource. I have been working with Microsoft and have come to a better resolution than placing tempdb on a separate remote drive as mentioned in that article.

Suggestion

Microsoft suggested to change the startup type of the SQL Server service to Automatic (Delayed Start). I have now put this into the bicep template for both SQL Server and SQL Server Agent service.

One problem I came up against was trying to use the PowerShell cmdlet Set-Service to set the SQL Server service to Delayed Start because this is only supported from PowerShell 6.0 onwards and Windows Server 2022 is shipped with Windows Powershell 5.1.

Two solutions to this

  1. In a previous step install Powershell 7
  2. Use sc.exe instead

For this article I opted for sc.exe instead, but recommend PowerShell 7 as it has many performance, feature and security improvements over Windows PowerShell 5.1. Setting just the SQL Server service to Delayed Start was intermittently successful, typical of a race condition.

https://d33wubrfki0l68.cloudfront.net/2fa76fc00746084c02ac8251f9f7143fa41ef8dd/cd085/blog/sqliaas-race-conclusion/2022-06-30_12-22-38.jpg
Deployment with SQL Server service set to Delayed Start (click to enlarge)

Further suggestion

Microsoft later suggested to set both the SQL Agent and SQL Server services to Delayed start. This seems to have fixed the issue and I have not had a Timeout since. This makes sense because if you start the SQL Server Agent service without starting the SQL Server service, SQL Server gets started anyway because SQL Server Agent depends on it.

To set both services to delayed start in my template I call the Microsoft.Compute/virtualMachines/runCommands resource and run this script as part of the deployment.

1
sc.exe config SQLSERVERAGENT start= delayed-auto; sc.exe config MSSQLSERVER start= delayed-auto;

The full code can be found in my git repo.

Logging in to the VM shows that they are indeed set to Delayed Start.

https://d33wubrfki0l68.cloudfront.net/2e2a831c24d2982ec88cc66f962fd31caab12c68/e00ab/blog/sqliaas-race-conclusion/2022-06-30_13-38-31.jpg
SQL Services set to Delayed Start (click to enlarge)

I have tested this multiple times and it seems reliable (but let me know if you have issues).

https://d33wubrfki0l68.cloudfront.net/68f80461e8213cc9cc8694b5dfaa8e955c25f681/6c0ed/blog/sqliaas-race-conclusion/2022-06-30_14-29-05.jpg
Reliable SQLVM deployments! (click to enlarge)

Problem solved

I can now have reliable SQL Virtual Machine deployments with tempdb on the local, fast ephemeral disk without having to place that on a provisioned disk.

Hope this helps!

UPDATE 20th July 2022

Microsoft got in touch with me and let me know that this race condition has now been fixed by the product team so the code to set the SQL Server services to Delayed Start will no longer be required. At the time of this update the fix is being rolled out to all Azure regions according to their safe deployment practises. I will provide another update when the fix has been rolled out to all regions, from that point the workaround code I provided in this article can be removed.

UPDATE 1st August 2022

This is now rolled out across all of Azure.