Azure @ Enterprise - Should I keep tempdb files in d: drive or cluster storage in FCI ?

There are many importance to the tempdb¹ database in SQL Server. It is a system database that plays crucial role to keep the SQL Server performant. The best practices when we setup tempdb in on-premise SQL Server is different than when we setup tempdb on Azure SQL VM.

Problem

Azure SQL VMs has special d:\ drive that is ephemeral². It is expected to be used for temporary purpose. There are reasons why people place the tempdb files in that drive. Reasons are mainly performance related. It is fine if the machine is not part of the FCI³ (Failover Cluster Instance) but what about if the machine is part of FCI?

Will the d: drive is monitored by the fail over mechanism and trigger failover in case something happens to that drive? Likelihood of the scenario is very less but there is a possibility.

Solution

The FCI is not monitoring the d:\ drive and it will not trigger a fail over in case something goes wrong there. SQL Server will simply go down. Leading to reduction of high availability.

The recommended solution is to keep the tempdb file in the cluster storage. Docs⁴ says the same.

All good. Only in case someone missed to read the docs, they get into trouble.

Why this post if the official docs says it right?

I started writing this post in 2021 after 6 months of struggle. At that time this guidance was not there.

This section was committed⁵ to docs only on 2022-01-13

Reference

• ¹ https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16
• ² https://docs.microsoft.com/en-us/azure/virtual-machines/windows/faq#can-i-use-the-temporary-disk--the-d--drive-by-default--to-store-data-
• v https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server?view=sql-server-ver16
• ⁴ https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql
• ⁵ https://github.com/MicrosoftDocs/sql-docs/commit/1e920fbd09b099a5548fa55e061b004e1c2270ac