• Articles

So, suddenly all your sites go down and the slow trickle of Applications Insights alerts come in. You login to the Microsoft Azure portal (your hosting partner) and check your servers - everything looks okay and they are all up. A quick VPN and RDP later and you are into the web server and database server and nothing seem amiss, except for the Why did your server shutdown unexpectedly dialog on the database server. Event log time. Nothing major before the reboot event but SQL server is not coming up properly, although it does seem to be recovering all the databases (for reference held directly in Azure Storage, not on virtual disks). Back to the Azure portal and look at the server history - CPU activity drops out briefly so the machine seems to have gone down for some reason - Resource health shows that the machine is Available, but there is a Recently resolved issue: "A problem with your Virtual machine has been resolved."

Hmm - viewing the detailed history shows the following 3 entries:

• We're sorry, your virtual machine isn't available and it is being redeployed due to an unexpected failure on the host server
• Your virtual machine is unavailable
  and then
• There aren't any known Azure platform problems affecting this virtual machine

So the machine has been redeployed onto new hardware. Oh! We will have 'lost' the temporary (SSD) disk - guessing SQL server is struggling because we used the instructions on the setting up SQL server on Azure to move the TempDB to the temp SDD. We had followed the recommendations in Performance best practices for SQL Server in Azure Virtual Machines while setting up the machine and had moved the TempDB. Now it didn't spin up as the directory was missing and the SQL server didn't / couldn't recreate it automatically.

In the comments (raised on the 13th July 2017 - so after we had built the server) was a comment on exactly this problem:

tempdb requires a subfolder on the D, if the vm is restarted, the subfolder disappears. While the obvious solution to me is to write a powershell script to recreate the subfolder on D so sql server can start and create the tempdb files, I wonder if there is a more straightforward approach?

And apparently a blog post had already address this back in 2014! But it wasn't referenced in the official documentation!

Have a look at the following article on the SQL Server blog: Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions"

David Gutzmann

So a few quick steps later and the SQL server was back up and running, and will now come back up if the size is changed for performance reasons, or there is a failover on the hosting platform.

Many thanks to David Gutzmann for his response that got me to the solution more quickly than my having to write it myself.