Can I use the local NVME drive on the Microsoft Azure Lsv3 machines for SQL TempDB?

Question

These storage optimised VMs have a 1.8 TB NVME drive attached. It is a ephemeral drive(eg. it is lost when the VM is stopped/deallocated).

I can't really understand the use of this drive at this moment. I could not find any concrete use for this temporary disk if I can't keep it's drive letter (and filesystem) between computer resets. Any use of this drive would mean manually recreating the storage after a physical host failure.

I want to use this fast drive for the TempDB files as I have 100+ databases all competing for TempDB space. When the VM starts, the drive is not initialised. Did anyone try to auto-mount / initialise, format and allocate a drive letter? Is there any other use case for these drives that I am not seing?

What I did was: At startup I initialised the drive, created a NTFS file system, and wrote a small file on it. I wrote the same file on the other temporary SSD disk. Then I stopped the VM and changed it's size to a equvalent Lasv3 (with a AMD processor) and restarted it. The ssd temporary drive is reset but usable, the NVME drive is not initialised again. I'm trying to find a method to do this before SqlServer.exe starts, so that i move the tempdb files there.

Answer 1

You could set a scheduled task that runs at startup, which runs Powershell.exe with the following script (set the drive letter and label as necessary)

Get-Disk |
Where PartitionStyle -eq 'raw' |
Initialize-Disk -PartitionStyle GPT -PassThru |
New-Partition -DriveLetter 'T' -UseMaximumSize |
Format-Volume -FileSystem NTFS -NewFileSystemLabel 'TempDb' -Confirm $false;

Start-Service 'MSSQLServer';

Then set SQL Server service to Delayed Start.