How to ensure a directory exists before starting SQL Server upon Windows startup?

1

How can I ensure that a directory exists upon Windows Server system startup, before SQL Server starts?

I'm running SQL Server 2017 on Windows Server 2019 on an Azure VM. I'd like to store my tempdb on the local SSD D:\ drive ("Temporary Storage") (as suggested here) under the directory D:\SQL_Data_Root. In order to do that in a way that can survive the VM being deallocated and reallocated on a different physical host, I need to have the system check to make sure that the directory D:\SQL_Data_Root exists, and create it if it does not, before starting SQL Server.

So I'm trying to create something that will, upon system startup:

  1. Create D:\SQL_Data_Root\ if it does not already exist
  2. Then start SQL Server

This Microsoft article recommends doing so by changing the SQL Server Windows Services to Manual startup, and instead starting it using a Powershell script that is run by Windows Task Scheduler upon system startup. I've done so, by:

  1. Taking the script they have there, fixing the curly quotes in the text on their web page, and putting it at C:\sysadmin\sql-startup.ps1
  2. Running Set-ExecutionPolicy RemoteSigned
  3. Creating a Windows Task Scheduler task that runs the program powershell with the arguments -file 'C:\sysadmin\sql-startup.ps1', triggered on system startup

Here's an exact copy of my script text:

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQL_Data_Root"
if (!(test-path -path $tempfolder)) {
    New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
# For some reason, the Agent won't start if you run it right away
Start-Sleep -Seconds 30
Start-Service $SQLAgentService

It's not working. When I reboot the machine at the guest OS level (with the D:\SQL_Data_Root directory already existing), the SQL Server service comes up, but the SQL Server Agent service does not. When I Stop and Start the VM at the Azure portal level, the D:\SQL_Data_Root directory does not get created, and neither the SQL Server nor SQL Server Agent services come up.

Looking at the History for the task in Task Scheduler, I can see that it's running, and it appears to execute my code, because it takes a bit over 30 seconds to run (so it seems like it's getting to and respecting that Start-Sleep). The "Last run result" is 0xFFFD0000, which I don't know how to interpret.

I've tried having the task run as both my user account (which has local Administrator rights) with "Run with highest privileges" enabled, and as the local SYSTEM account. Seems to have the same behavior under both.

If I manually run the task from Task Scheduler, it also does not seem to work. And in this case it doesn't even seem to do the sleep, because it completes in under a second.

I also tried putting another Start-Sleep -seconds 30 at the start of the script, in case it was failing due to other dependencies that were still coming up, but that seemed to have no effect.

I also tried doing this:

Start-Service $SQLService -PassThru | Format-List >> C:\sysadmin\sql-server-startup.log

and the log file did not appear. Which makes me think there's a permissions issue somewhere.

So I tried creating a new local user, adding it to Administrators, granting it Log On As A Service permissions, and having the task run as that user. Still didn't work: neither the dir nor log files get created.

If I just log in to the running system and open up a PowerShell prompt as Administrator and run powershell -file 'C:\sysadmin\sql-startup.ps1', it works: the dir is created if it does not exist, and both the SQL Server and SQL Server Agent services come up. The services also start fine if I just manually start them from within the Services control panel.

Anyone know what might be going wrong, and how to get this to work? "Create a directory on system startup" doesn't seem like that complicated a thing to do; surely this is feasible somehow?

windows
sql-server
asked on Server Fault Jul 26, 2020 by Andrew Janke • edited Jul 26, 2020 by Andrew Janke

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0