Hey everybody!
I am asked by clients/team members/friends a lot about how to setup TEMPDB to use the D:\ drive on an Azure VM for SQL Server. Following the steps listed below is how I configure it on my VM’s. This seems to be the most standard way folks are doing it. However, this is not always the best practice to do. The reason being is that the drive size varies depending on the VM size you choose.
Read the section “Temporary Disk” first, then decide on where to place your TempDB.
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 NOTstore 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
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
$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
Set-ExecutionPolicy RemoteSigned
Test your Configuration
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!