Tempdb files on D drive you say?
Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.
TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.
Just look at these results! Even for someone like me who sometimes looks at charts and often can’t see the obvious, I don’t think there is any denying when the change took place here.
Overview of how to configure SQL Server to use D drive for tempdb on Azure VM
- Run the ALTER DATABASE command for all of the files you need to change e.g.
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'D:\TempDB\tempdev.mdf')
- Create Folder D:\TempDB or something similar
- Restart SQL Server and make sure your files are created in D: as expected
- Manually remove any old tempdb files still lying around
Now to make sure tempdb is created when the VM restarts
- Create a Powershell script which will create the D:\TempDB folder on startup and start the SQL services
- You could use this script at https://community.idera.com/database-tools/blog/b/community_blog/posts/configuring-tempdb-on-azure-iaas-for-sql-server
- Schedule this script with a Task Scheduler startup job
- Change your execution policy to remote signed if it isn’t that.
Set-ExecutionPolicy RemoteSigned
- Change the startup type of your SQL Services to Automatic (Delayed Start)
- Test this thoroughly in dev before you rely on it in production. If it doesn’t work, you’ll need to remember how you configured tempdb and manually create the folders, then start SQL Server. Not a fun task under pressure.
More tempdb goodness
Configuring TempDB for SQL Server 2016 instances
D drive is not good for tempdb either. We had incident where complete tempdb folder got dropped after VM rebooting.
Even MSFT employee does not recommend
https://learn.microsoft.com/en-us/answers/questions/505774/my-d-drive-has-gone-missing
You will always lose tempdb when your vm reboots, it doesn’t matter where you put it. As long as you recreate the folder on startup which is mentioned in the blog post, it will regenerate.