Azure VM locall SSD use for TempDB

David C 191 Reputation points
2023-02-07T21:04:28.3166667+00:00

[Edited] Kinda late to the game of SQL on Azure VM with Always On AG.

Per this article, it's recommended practice to put TempDB on the ephemeral temp drive D: which is low-latency SSD...

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql

I understand that the temp drive can be deallocated, and you loose the TempDB if you deallocate the VM, or during other Azure maintenance behind the scenes.

Also, I understand it takes some automation to locate TempDB on D: such as configuring a Windows scheduled task to run a PowerShell script which recreates a folder on temp drive, assigns permissions to it, and starts SQL.

My question is, can the temp drive be deallocated and TempDB lost during only when you deliberately deallocate the VM, or can the the drive also be deallocated and TempDB lost during some behind the scenes Azure maintenance even while SQL Server is up and running? If that scenario were possible, why would the referenced article suggest to locate TempDB there?

Lastly, in case of a Always On AG failover, what happens to TempDB if each of the cluster nodes has its TempDB on the local temp D: drive?

Thanks.

SQL Server on Azure Virtual Machines

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tamarick Hill 11 Reputation points Microsoft Employee
    2023-05-17T19:22:04.29+00:00

    Not sure I fully understand your question but I will attempt to help.

    Emphemeral disks are recommended for tempdb because they provide lower latency which is important for tempdb workloads. In addition, these dont incur any additional costs as them come with various VM Family tiers. Basically these disks are stateless, so in the event of a VM reboot, all data on this disk will be lost. This is not an issue for tempDB because SQL Server will always create a new tempdb file anytime that SQL server starts/restarts.

    I think the article you referenced is simply highlighting that you need to monitor this disk in the event there is a hardware issues impacting this disk only. Azure scheduled maintenance should not impact your workloads, but if there is an unexpected issue affecting this disk only, since it is not part of the cluster, a failover may not automatically be triggered, so you would need to have a monitoring solution to detect disk issues and failover.

    Hope this helps.

    0 comments