Change TEMPDB to Temporary Drive on Azure SQL IaaS

NOTE: This article has been retired. For the most-up-to-date information for SQL Server in Windows Azure VMs, see the documentation on MSDN. For the latest recommendations related to tempdb and performance of SQL Server in Windows Azure VMs, see white paper: Performance Guidance for SQL Server in Windows Azure Virtual Machines.

On Azure SQL IaaS, there is a recommendation that we have to move TEMPDB on Temporary Drive (D:) to improve SQL Server performance.

You could use the following steps to make this change:

1) Open SSMS and connect to our instance. Then execute the following script to change location of tempdb files. You have to restart services to apply, but we are going to restart at the end of this procedure.

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

2)      Set SQL Services to Manual
3)      From console, execute powershell and set execution-policy to remotesigned

Set-ExecutionPolicy RemoteSigned

4)      Create the following script in Powershell

$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

5)      Create a schedule task at system startup to call the script
6)      To test the script, restart the server now.

It's also worth saying that since drive D is temporary and could be recycled by Windows Azure in some situations, the PowerShell script (steps 2 to 6) is necessary if you want to place TEMPDB inside a folder: since that folder will disappear on VM recycle, you need to create it again before SQL Server starts, otherwise will fail. If you want to keep it simple, a possible alternative is to put TEMPDB files directly on the root of the disk and also add the service account as a member of the local administrator group.