When deploying SQL Server on Azure VMs<\/strong>, you can boost This post walks you through automatically assigning folder permissions to your SQL Server MSA<\/strong> on startup, ensuring When using ephemeral storage<\/strong> for As a result, SQL Server fails to start<\/strong>.<\/p>\n\n\n\n Automate the creation and permission assignment<\/strong> of the Save this script as $FolderPath = “D:\\SQLTEMP” Create Task<\/strong> \u2192 General tab:<\/p>\n\n\n\n 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:<\/p>\n\n\n\n You can first test by stopping SQL Server instance and deleting TempDB folder from D:. Then execute the job manually. <\/p>\n\n\n\n Final test is a server reboot.<\/p>\n","protected":false},"excerpt":{"rendered":" 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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45,2],"tags":[75],"class_list":["post-304","post","type-post","status-publish","format-standard","hentry","category-azure","category-mssql","tag-tempdb"],"blocksy_meta":[],"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/304","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=304"}],"version-history":[{"count":9,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/304\/revisions"}],"predecessor-version":[{"id":314,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/304\/revisions\/314"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=304"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}tempdb<\/code> performance by storing it on ephemeral (temporary) storage<\/strong>. Microsoft recommends using the local SSD (often D:<\/code> drive) for tempdb<\/code>. However, if your SQL Server runs under a Managed Service Account (MSA)<\/strong>, it may not have permission to access this folder after a reboot.<\/p>\n\n\n\ntempdb<\/code> always initializes successfully.<\/p>\n\n\n\nThe Problem<\/h2>\n\n\n\n
tempdb<\/code>, folder and its permissions must be recreated and reset after every VM reboot<\/strong> since the D:<\/code> drive is reprovisioned<\/strong> on every boot.<\/p>\n\n\n\nThe Solution<\/h2>\n\n\n\n
tempdb<\/code> folder during VM startup<\/strong> using a PowerShell script<\/strong> executed by Task Scheduler<\/strong>.<\/p>\n\n\n\nStep-by-Step Guide<\/h2>\n\n\n\n
1. Create the PowerShell Script<\/h3>\n\n\n\n
C:\\Scripts\\SQLTempDB.ps1<\/code>:<\/p>\n\n\n\n
$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<\/p>\n\n\n\n2. Create a Scheduled Task to Run the Script on Startup Via Task Scheduler<\/h3>\n\n\n\n
\n
Initialize SQL TempDB Folder with privileges<\/code><\/li>\n\n\n\nSYSTEM<\/code><\/li>\n\n\n\n\n
\n
At startup<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n\n
Start a program<\/code><\/li>\n\n\n\npowershell.exe<\/code><\/li>\n\n\n\n-ExecutionPolicy Bypass -File "C:\\Scripts\\SQLTempDB.ps1"<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n3. Configure start mode for SQL Server<\/h3>\n\n\n\n
\n
4. Test<\/h2>\n\n\n\n