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.
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
- In a previous step install Powershell 7
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.
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.
The full code can be found in my git repo.
Logging in to the VM shows that they are indeed set to Delayed Start.
I have tested this multiple times and it seems reliable (but let me know if you have issues).
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.