Microsofts recommendation on TempDB on Azure VM’s is to place it on the D-drive that every Azure server is created with.  In most cases this is going to be the fastest storage on the virtual machine, and given that tempDB files do not need to be persisted this can give you access to a high performance TempDB configuration without the need to provision additional storage.

  The only issue is that the Ephemeral drive is temporary.  Everything is wiped from it whenever a machine is deallocated(Note – contents can survive a reboot, just not a shutdown-deallocation event).  Because I keep forgetting how to set it up I thought I might as well store it here so next time I forget I don’t have to log into the last VM I set up and create it.

Step 1.  Create a folder called scripts and create a file in it called SQL-Startup.ps1.  Edit that file and enter the following code.

Start-Transcript -Path "C:\Scripts\Transcript$(((get-date).ToUniversalTime()).ToString("yyyyMMdd_hhmmss")).txt" -Append
$SQLService="SQL Server (MSSQLSERVER)”
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMPDB"
if (!(test-path -path $tempfolder)) {
   New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

$runtime= Get-Date
echo “{$runtime}: I just ran” >> C:\scripts\outputfile.txt

Step 2.  Create a Windows Scheduler Task which will run that script each time the Virtual Machine Starts.

1. Click on Windows button and type ‘Scheduler’ – select and open task scheduler.
2. Click “Create Task”
   1. On General Tab:
      1. Set name to “SQL Startup”
      2. Change user to run as a service account with permissions to create a folder and Start SQL services.
      3. Select “Run whether the user is logged in or not.
   2. On Triggers Tab:
      1. Select “New”
      2. Select “At Startup” from the dropbox menu.
      3. Ensure “Enabled” is checked and click Okay.
   3. On the Actions Tab:
      1. Select “New”
      2. Select Action as “Start a program”
      3. For the program type(or browse to) powershell.exe
      4. For Add Arguments type Powershell.exe -ExecutionPolicy Unrestricted -File “C:\Scripts\SQL-Startup.ps1”
      5. Click Okay.
   4. On the Conditions Tab:
      1. No Changes
   5. On the Settings Tab:
      1. No Changes
3. Click Okay – You will be prompted for the credentials for the service account you selected to run the job under.

Step 3.  Set SQL to auto start(delayed)

1. Click Windows Start button and type “Services.msc”
2. Right click on the SQL Service and change Startup Type to “Automatic (Delayed Start)”
3. Click Okay.

Step 4.  Ensure Service Account has correct rights

1. Ensure your service account has sufficient permissions to start SQL and create a folder.
2. Ensure your service account has “Log on as a batch job” rights.

Now you can stop SQL, delete the tempdb folder and restart the virtual machine.  On bootup the script should recreate the folder and add a record of the restart to C:\scripts\outputfile.txt