Almost all Azure virtual machine sizes come with a temporary disk. The temporary disk is a locally attached SSD drive that comes with a couple of desirable features if you’re installing a SQL Server on your VM:
- Because it is locally attached, it has lower latency than regular disks.
- IO and storage are not billed like regular storage.
As the name implies, the temporary disk is not persistent, meaning that it will be wiped if you shut down your VM or if the VM moves to another VM host (as part of maintenance or troubleshooting). For that reason, we never want to put anything on the temporary disk that we need to keep.
tempdb could be a good fit
Since tempdb is wiped and recreated every time we start a SQL Server instance, it could be a great candidate to have on the temporary drive, provided a few prerequisites are met:
- Make sure you have enough space on the temporary drive. Azure VM instances come with differently-sized temporary drives and you can’t scale them infinitely.
- Do you have other workloads on the same VM that use the temporary drive? By default, Windows likes to place its pagefile.sys in the root directory of the temporary drive. This could not only affect storage space on the drive, but could also compete with the IOPS on the drive.
- Is your monitoring solution going to go nuts if we change the way SQL Server starts up?
Step by step
We’re going to set up a scheduled task that runs on server startup. This task will create the directory on the temporary drive and then start the SQL Server instance.
Tip: Disable the page file
Move the operating system page file somewhere else, or disable it entirely. For my money’s worth, if your SQL Server needs a page file, you’re doing it wrong. Brent seems to agree.
Set the SQL Server instance to “manual” startup.
This allows us to create the proper directory before SQL Server tries to create the tempdb files.
Create a PowerShell script
We’ll schedule this script to run on startup, in order to first create the directory on the temporary drive, and then start the SQL Server instance. Put this script somewhere on your system drive.
# Configuration
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempdbFolder="D:\tempdb"
# Make tempdb directory on the SSD
if (!(Test-Path -Path $tempdbFolder)) {
New-Item -ItemType Directory -Path $tempdbFolder
}
# Start the services
Start-Service $SQLService
Start-Service $SQLAgentService
Create a job in the Windows scheduler
Run the task at startup, so it starts whenever you start or reboot the server.
Set the task to run as “SYSTEM”, so it does not depend on a user account
The “-file” parameter to powershell.exe points to the PowerShell script file we just created.
Configure SQL Server to use the new directory
We’ll use ALTER DATABASE to move tempdb’s files to the new directory. Note that these changes will take effect the next time SQL Server restarts.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='D:\tempdb\tempdb1.mdf', NEWNAME='tempdb1');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp2', FILENAME='D:\tempdb\tempdb2.mdf', NEWNAME='tempdb2');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp3', FILENAME='D:\tempdb\tempdb3.mdf', NEWNAME='tempdb3');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp4', FILENAME='D:\tempdb\tempdb4.mdf', NEWNAME='tempdb4');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp5', FILENAME='D:\tempdb\tempdb5.mdf', NEWNAME='tempdb5');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp6', FILENAME='D:\tempdb\tempdb6.mdf', NEWNAME='tempdb6');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp7', FILENAME='D:\tempdb\tempdb7.mdf', NEWNAME='tempdb7');
ALTER DATABASE tempdb MODIFY FILE (NAME='temp8', FILENAME='D:\tempdb\tempdb8.mdf', NEWNAME='tempdb8');
ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='D:\tempdb\templog.ldf');
Make sure you spell the directory correctly and that SQL Server has permissions to create files in that directory. Otherwise, the instance won’t start at all.
In my experience, SQL Server does not delete the old tempdb files, so make sure to do that, too.
Other solutions
Joey D’Antoni has published a PowerShell script to set up the scheduled task in an automated fashion.
sqlsunday.com 
I’m not a Azure user.. not sure that I ever will be… but it it ever comes to that, this is most definitely an article I’ll be coming back to.
Thanks for what you do, Daniel.
Thanks, Jeff! I appreciate your kind words. :)
Have been using a Joeys script for some time but intefesting point about the page file. Something to look into.
Pingback: Putting tempdb on an Azure VM Temp Disk – Curated SQL
Another option is to use the IaaS agent extension.
https://www.sqltact.com/2020/05/azure-vm-tempdb-on-d.html
Yup, that makes life much easier. Sounds like MS need to advertise the benefits of the extension a bit more!
I had created a script that does exactly this, plus makes sure the appropriate permissions are granted to the service account. It works great. And then we tried out the SQL IaaS extension which supposedly will do this same thing, and it works sometimes. But when it doesn’t work (the extension may have issues during machine startup), it results in SQL not starting because your TempDB folder isn’t present where it expects.
So although the extension sounds nice, I wouldn’t call it reliable enough for production use since we’ve seen it fail on several occasions. But if it did work correctly, that’d be great. I think it may have failed, in at least one instance, because the extension was auto updating at some point during a server reboot, or after we’d stopped the VM and started it back up after some maintenance. I guess that may have caused the extension to miss the window where it would create the TempDB folder and then SQL just failed to startup until I did it manually.
Here is a snippet of my script to setup the ephemeral drive for TempDB on AWS EC2 instances. The script is called from user data.
$NVMe = (Get-PhysicalDisk -CanPool $true)
if ($NVMe) {
New-StoragePool -FriendlyName NVMePool -StorageSubsystemFriendlyName “Windows Storage*” -PhysicalDisks $NVMe
}
$StoragePool = (Get-StoragePool -FriendlyName “NVMePool”)
$driveLetters= (Get-Volume).DriveLetter
if ($StoragePool -and !($driveLetters -contains “t” -and $driveLetters -contains “e”)) {
New-VirtualDisk -StoragePoolFriendlyName NVMePool -FriendlyName TempDBDisk -ResiliencySettingName simple -ProvisioningType Fixed -Size ($StoragePool.Size * .65)
Get-VirtualDisk -FriendlyName TempDBDisk | Get-Disk | Initialize-Disk -PartitionStyle GPT -Passthru | New-Partition -DriveLetter T -UseMaximumSize | Format-Volume -FileSystem ReFS -AllocationUnitSize 65536 -NewFileSystemLabel TempDBfiles -Confirm:$false
New-VirtualDisk -StoragePoolFriendlyName NVMePool -FriendlyName BpeDisk -ResiliencySettingName simple -ProvisioningType Fixed -Size ($StoragePool.Size * .32)
Get-VirtualDisk -FriendlyName BpeDisk | Get-Disk | Initialize-Disk -PartitionStyle GPT -Passthru | New-Partition -DriveLetter E -UseMaximumSize | Format-Volume -FileSystem ReFS -AllocationUnitSize 65536 -NewFileSystemLabel BPEfiles -Confirm:$false
$item = Get-Item -literalpath “T:\”
$acl = $item.GetAccessControl()
$permission = $config.saDomainAccount,”FullControl”,”Allow”
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule $permission
$acl.SetAccessRule($rule)
$item.SetAccessControl($acl)
$item = Get-Item -literalpath “E:\”
$acl = $item.GetAccessControl()
$permission = $config.saDomainAccount,”FullControl”,”Allow”
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule $permission
$acl.SetAccessRule($rule)
$item.SetAccessControl($acl)
New-Item -Path “T:\Data” -ItemType Directory
New-Item -Path “T:\Logs” -ItemType Directory
}
This will set the T drive to roughly 2/3 of the ephemeral disk space and the E drive to roughly the remaining 1/3.
Anything wrong with putting the Temp Files on the Root of the Temporary Drive (D:\)? that way no need to recreate the path on sql restart.
Are there events that cause the Temporary drive to wipe that wouldn’t necessitate a automatic SQL restart in some fashion?
Non-privileged accounts can’t write to the root of a drive by default, and you absolutely don’t want to give the SQL Server service account local admin permissions.