John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Put tempdb files on D drive in Azure IAAS

21st March 2019 By John McCormack 5 Comments

Data loss warning Azure VMTempdb 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.

tempdb latency azure IAAS
Blazing fast now

 

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

  1. 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
  2. Change the startup type of your SQL Services to Automatic (Delayed Start)
  3. 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

IT Certification Category (English)728x90

Share this:

  • Tweet
  • Email

Related

Filed Under: Azure, Azure VM, front-page Tagged With: azure, azure iaas, sql server on Azure, tempdb

About John McCormack

John McCormack is an experienced SQL DBA with extensive knowledge of the two largest public clouds: AWS and Azure.

Trackbacks

  1. Putting TempDB Files On Azure IaaS D Drive – Curated SQL says:
    26th March 2019 at 12:05 pm

    […] John McCormack tries out using the temporary drive on Azure VMs for tempdb: […]

    Reply
  2. Test read intent connections to an AG Listener - John McCormack DBA says:
    19th April 2019 at 1:39 pm

    […] https://johnmccormack.it/2019/03/put-tempdb-files-on-d-drive-in-azure-iaas/ […]

    Reply
  3. Change a user's resource class within Azure Synapse Analytics using Azure Automation runbooks - John McCormack DBA says:
    18th December 2019 at 2:07 pm

    […] https://johnmccormack.it/2019/03/put-tempdb-files-on-d-drive-in-azure-iaas/ […]

    Reply
  4. How DBATools can help with performance tuning - John McCormack DBA says:
    20th May 2020 at 2:58 pm

    […] A common bottleneck can be tempdb, it’s always good to know you have tempdb on high performing disks. (If using Azure VMs, you can utilise the D drive for tempdb) […]

    Reply
  5. A successful performance tuning project - John McCormack DBA says:
    5th June 2020 at 4:43 pm

    […] https://johnmccormack.it/2019/03/put-tempdb-files-on-d-drive-in-azure-iaas/ […]

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.