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

1. 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')
2. Create Folder D:\TempDB or something similar
3. Restart SQL Server and make sure your files are created in D: as expected
4. Manually remove any old tempdb files still lying around

Now to make sure tempdb is created when the VM restarts

4. 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
5. Change the startup type of your SQL Services to Automatic (Delayed Start)
6. 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