MS SQL also Express on Azure VM – TempDB on ephemeral local disk

Fast SQL on Azure – nutshell:

  • TempDB on fast local disk
  • At least 3 disks for data and logs
  • Read-only caching for data disks

One of the most important things for SQL Server is to store TempDB on a fast ephemeral local – temporary disk. How to do it:

Install SQL and configure it to use D:\TempDB for TempDB.

Create two files on c: dirive:

StartSQL.bat:

PowerShell -Command “Set-ExecutionPolicy Unrestricted” >> “%TEMP%\StartupLog.txt” 2>&1

PowerShell C:\StartSQL.ps1 >> “%TEMP%\StartupLog.txt” 2>&1

StartSQL.ps1:

PowerShell -Command “Set-ExecutionPolicy Unrestricted” >> “%TEMP%\StartupLog.txt” 2>&1

PowerShell C:\StartSQL.ps1 >> “%TEMP%\StartupLog.txt” 2>&1

StartSQL.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 #remove # for non Express version

 

Set Startup Type – Manual for the following Services:

  • SQL Server (MSSQLSERVER)
  • SQL Server Agent (MSSQLSERVER)

Configure Task Scheduler (Run as SYSTEM with highest privileges):

Additional tip is to use at least 3 disks for Data and Logs. Just create 3 data disk and run this PowerShell that configure it for you:

$disks = Get-PhysicalDisk -CanPool $true

New-StoragePool –FriendlyName StoragePool1 –StorageSubsystemFriendlyName “*” –PhysicalDisks (Get-PhysicalDisk –CanPool $True)

New-VirtualDisk -FriendlyName “sql-stripe” -StoragePoolFriendlyName “StoragePool1” -Interleave 65536 -AutoNumberOfColumns -ProvisioningType Fixed -ResiliencySettingName “Simple” -UseMaximumSize

Get-VirtualDisk –FriendlyName sql-stripe | Get-Disk | Initialize-Disk –Passthru | New-Partition -DriveLetter F –UseMaximumSize | Format-Volume

 

Enable read-only caching on the disk(s) hosting the data files.

 

More information: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices