Configuring TempDB on Azure IaaS for SQL Server

I am asked by clients/team members/friends a lot about how to set up TEMPDB to use the D:\ drive on an Azure VM for SQL Server. Below are the steps I took to configure it on my VMs.

First, read the section “Temporary Disk,” then decide on where to place your TempDB.

Temporary disk – AKA D:\ on Azure VM’s

Remember, this disk is, as the title of this section says, TEMPORARY! Do not put anything on this drive you cannot afford to lose. Don’t say nobody warned you either, because the drive itself contains a nice little txt file warning you. Here is the exact text:

WARNING : THIS IS A TEMPORARY DISK.

Any data stored on this drive is SUBJECT TO LOSS and THERE IS NO WAY TO RECOVER IT.

Please do not use this disk for storing any personal or application data.

For additional details, please refer to the MSDN documentation at : http://msdn.microsoft.com/en-us/library/windowsazure/jj672979.aspx

The temporary storage drive, labeled as the D:\ drive, is not persisted to Azure blob storage. Do not store your user database files or user transaction log files on the D:\ drive.

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D:\ drive could result in higher TempDB throughput and lower TempDB latency.

For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports premium storage with read caching enabled. There is one exception to this recommendation: if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D:\ drive, which is also SSD-based on these machine sizes.

Configure SQL Server to Use Local SSD

  • Connect to your VM in Azure
  • Create a new directory in the root of the D:\ drive called SQLTEMP (You have to create a folder as you will not have permissions to write directly to the root of D:\)
  • Launch SQL Server Management Studio and connect to the SQL instance
  • Launch a new query window by clicking the New Query button
  • Configure the TempDB system database to store data and log files on the D:\ drive. Cut and paste the following code into the query window then click the execute button
    USE MASTER
    GO
    ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= ‘D:\SQLTEMP\tempdb.mdf’)
    GO
    ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = ‘D:\SQLTEMP\templog.ldf’)
    GO

6. In Management Studio, right click the server icon and choose Restart. Click the Yes button on each of the confirmation popups

7. In File Explorer, navigate to the D:\SQLTEMP directory you created earlier. You should now see two files for TempDB in the folder

Configure Windows Server Startup to Prepare the SSD

  1. In File Explorer, create a new folder here: C:\SQLServerFiles\TEMPDBSetup
  2. Copy and paste the following code into a new file and save the file to
    C:\SQLServerFiles\TEMPDBSetup\SQLTEMPDBInit.ps1

    $SQLService="SQL Server (MSSQLSERVER)"
    
     $SQLAgentService="SQL Server Agent (MSSQLSERVER)"
    
     $tempfolder="D:\SQLTEMP"
    
     if (!(test-path -path $tempfolder)) { `
    
     New-Item -ItemType directory -Path $tempfolder `
    
     }
    
     Start-Service $SQLService
    
     Start-Service $SQLAgentService
  3. Launch PowerShell as Administrator. Run the following code to change the execution policy to RemoteSigned and type Y to confirm
    Set-ExecutionPolicy RemoteSigned
  4. Launch the Windows Task Scheduler by navigating to the Start screen, click Administrative Tools, then double-click Task Scheduler
  5. In the Task Scheduler, click Create Basic Task… on the Actions pane
  6. In the Create Basic Task Wizard, type TempDBInit for the name and click Next
  7. On the Task Trigger screen, select the radio button next to When the computer starts then click Next
  8. On the Action screen, select the radio button next to Start a program then click Next
  9. On the Start a program screen, type powershell for the Program/script and -file “C:\SQLServerFiles\TEMPDBSetup\SQLTEMPDBInit.ps1″ for the Add arguments then click Next
  10. On the summary page, check the box next to Open the Properties dialog for this task when I click Finish and click Finish
  11. On the Properties screen, click the Change User or Group button and type SYSTEM for the name and click OK, change Configure for to Windows Server 2016. Click OK to save the changes
  12. In the Task Scheduler, click on Task Scheduler (Local), then scroll down to Active Tasks and verify that your task was created and activated. Then close the Task Manager
  13. Open the Services Snap-in by going to Start > and typing: Services.msc
  14. Right-Click on your SQL Server (Instance Name) service
  15. Go to properties and left click
  16. Change the startup type to: Automatic(Delayed Start)

Test your Configuration 

  1. From the SQL Server Configuration Manager, stop both the SQL Server and the SQL Server Agent
  2. Navigate to the root of the D:\ drive and delete the SQLTEMP folder
  3. Restart Windows by right-clicking the Windows Start button and clicking Restart
  4. Give the virtual machine about five minutes to restart then log back in.
  5. Navigate back to the D: drive and verify that your SQLTEMP directory was recreated.
  6. Open SQL Server Management Studio and execute the following query to verify that tempdb is running with files located on the D:\ drive
    
    SELECT dbid, name, filename FROM sys.sysaltfiles WHERE dbid = 2

    If SQL Server is configured correctly your output should look like this:

    Thanks for reading everybody!  Stay tuned for more posts about Azure soon.

Leave a Reply

Your email address will not be published. Required fields are marked *