0

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.

Mitz
  • 135
  • 8

1 Answers1

0

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • i actually did just that but with a twist -> adding a start-service command after 5 seconds. I found out during testing that Delayed Start was sometimes not enough (the disk was not finished with formatting until SQL Server attempted to start, so this little ugly twist is enough. – Mitz Apr 11 '23 at 07:08