In the past year I’ve been working more with SQL Server on Azure virtual machines. I was initially excited about Marketplace images for SQL Server virtual machines, but having deployed them as a test, I was ultimately disappointed with their underwhelming configuration. Because my current work environment uses Pulumi and Ansible for managing infrastructure as code (IaC), I taught myself how to build a virtual Windows Server and install SQL Server with a better configuration than what was provided by the images.

The production SQL Servers I inherited mostly have tempdb files located on the same drive as the user database data files, a configuration that is not consistent with my preferred best practices. Some instances have tempdb files in the root of the drive, also not recommended because of the elevated permissions required (the SQL Server service account has to be a local Administrator). However, because the ephemeral drive can be cleared during a reboot (less likely) or server deallocation (more likely), use of the drive in our environment has mostly been limited to use as a temporary staging area for copying files, this despite the fact that local SSD is the ideal place for tempdb. The challenge has been that SQL Server will not start if the configured path doesn’t exist. 

One nice feature of the Marketplace images was the pre-configuration of the SqlIaaSExtension, specifically how tempdb files were managed on the ephemeral SSD local disk. I did some research and testing of the automated configuration, and I am documenting here what I incorporated into my Ansible code to duplicate that behaviour.

There are two Windows components to make this work. The pre-requisite is configuring the SqlIaaSExtension, either through Azure manually, IaC JSON specifications, or other preferred method.

Registry

This snippet can be used to create a Registry Entry file for import. These string values define the desired paths for the files.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SqlIaaSExtension\CurrentVersion]
"TempDbData"="D:\\tempDb\\Data"
"TempDbLog"="D:\\tempDb\\Log"

Scheduled Task

The following XML can be imported to create a job that runs on system startup. SqlServerStartup.exe references the registry keys to create folders on the drive of your choice (mine being the ephemeral D drive) prior to the start of SQL Server.

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2022-03-13T08:09:20</Date>
    <Author>DOMAIN\yourlogin</Author>
    <URI>\SqlStartUp</URI>
  </RegistrationInfo>
  <Triggers>
    <BootTrigger>
      <StartBoundary>2021-01-01T00:00:00</StartBoundary>
      <Enabled>true</Enabled>
    </BootTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>S-1-5-18</UserId>
      <RunLevel>HighestAvailable</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <Duration>PT10M</Duration>
      <WaitTimeout>PT1H</WaitTimeout>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>PT72H</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>"C:\Program Files\Microsoft SQL Server IaaS Agent\Bin\SqlIaaSExtension.SqlServerStarter.exe"</Command>
    </Exec>
  </Actions>
</Task>

After completing both, reconfigure SQL Server to use the new location and reboot the server.

ALTER DATABASE [tempdb] MODIFY FILE (name='tempdev', filename='D:\tempDb\Data\tempdb.mdf'
ALTER DATABASE [tempdb] MODIFY FILE (name='templog', filename='D:\tempDb\Log\templog.ldf'

This configuration is still being tested in my environment, and has not yet been approved for production use. So be sure to thoroughly test these changes yourself before risking your data.