Configuring tempdb on Ephemeral Disks for SQL Server on Azure VMs

Introduction

When deploying SQL Server on Azure VMs, you can boost tempdb performance by storing it on ephemeral (temporary) storage. Microsoft recommends using the local SSD (often D: drive) for tempdb. However, if your SQL Server runs under a Managed Service Account (MSA), it may not have permission to access this folder after a reboot.

This post walks you through automatically assigning folder permissions to your SQL Server MSA on startup, ensuring tempdb always initializes successfully.

The Problem

When using ephemeral storage for tempdb, folder and its permissions must be recreated and reset after every VM reboot since the D: drive is reprovisioned on every boot.

As a result, SQL Server fails to start.

The Solution

Automate the creation and permission assignment of the tempdb folder during VM startup using a PowerShell script executed by Task Scheduler.

Step-by-Step Guide

1. Create the PowerShell Script

Save this script as C:\Scripts\SQLTempDB.ps1:

$FolderPath = “D:\SQLTEMP”
$Account = “DOMAIN\USER”
$SQLService=”SQL Server (MSSQLSERVER)”
$SQLAgentService=”SQL Server Agent (MSSQLSERVER)”

# Recreate folder if it doesn’t exist
if (!(Test-Path $FolderPath)) {
New-Item -Path $FolderPath -ItemType Directory | Out-Null
}

# Assign full control to SQL Server service account
$acl = Get-Acl $FolderPath
$permission = “$Account”,”FullControl”,”ContainerInherit,ObjectInherit”,”None”,”Allow”
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule $permission
$acl.SetAccessRule($accessRule)
Set-Acl -Path $FolderPath -AclObject $acl

Start-Service $SQLService
Start-Service $SQLAgentService

2. Create a Scheduled Task to Run the Script on Startup Via Task Scheduler

Create Task → General tab:

• Name: Initialize SQL TempDB Folder with privileges
• Run as: SYSTEM
• Check “Run with highest privileges”

• Triggers → New:
  • Begin the task: At startup
• Actions → New:
  • Action: Start a program
  • Program/script: powershell.exe
  • Add arguments: -ExecutionPolicy Bypass -File "C:\Scripts\SQLTempDB.ps1"

3. Configure start mode for SQL Server

Since you want the script to create the folder to run before SQL Server starts, you need to set the SQL Server and SQL Agent services to start manually. To do this, follow these steps:

1. Open SQL Server Configuration Manager.
2. Select SQL Server Services in the left pane.
3. Right-click on the SQL Server service and select Properties to open the Properties window.
4. On the Properties window, select the Service tab.
5. On the Service tab, use the dropdown list to change the Start Mode to Manual:
6. Use Apply to save your changes and then OK to close the window.
7. Repeat these steps for the SQL Server Agent service.

4. Test

You can first test by stopping SQL Server instance and deleting TempDB folder from D:. Then execute the job manually.

Final test is a server reboot.