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:
D:\SQL_Data_Root\
if it does not already existThis 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:
C:\sysadmin\sql-startup.ps1
Set-ExecutionPolicy RemoteSigned
powershell
with the arguments -file 'C:\sysadmin\sql-startup.ps1'
, triggered on system startupHere'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?
User contributions licensed under CC BY-SA 3.0