VM IaaS do Azure - Use D:\ Drive para tempdb - Formatação NTFS 64k

Esta era uma pergunta com a qual eu estava lutando hoje cedo e, eventualmente, descobri uma resposta. Não se importaria com algo melhor, mas queria fornecer isso para quem também precisasse.

Primeiro, em uma VM do Azure, você obtém uma unidade D:\ gratuitamente, que é SSD. A ressalva é que ele geralmente é destruído quando uma VM é reiniciada. A prática recomendada do MS para alto volume de gravação é usar essa unidade para tempdb. O que eles não dizem é que não está formatado em 64kb. O pagefile.sys oculto reside aqui, então você precisa manter isso em conta se tentar reformatar (vai falhar).

A partir disso: https://cloudblogs.microsoft.com/sqlserver/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

Você desejará alterar o script do powershell de inicialização para algo mais parecido com abaixo, onde primeiro estou removendo o arquivo de paginação completamente, depois formatando para 64k, criando o arquivo como o script normalmente faria e, em seguida, colocando o arquivo de paginação.sys de volta antes de iniciar o serviços de inicialização.

$SQLService=”SQL Server (MSSQLSERVER)”
$SQLAgentService=”SQL Server Agent (MSSQLSERVER)”
$tempfolder=”D:\SQLTEMP”
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete() 
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = 'D:\pagefile.sys';} 
}
Start-Service $SQLService
Start-Service $SQLAgentService

Alguém tem algo melhor ou vê algum buraco nesse processo?

Para um pouco mais de automação/ajuda para outras pessoas, também faço o script do script acima, bem como os gatilhos para criá-lo e executá-lo 30s após a inicialização usando abaixo. Isso basicamente permite que alguém execute automaticamente as etapas no artigo de cloudblogs que eu faço referência.

#1 - Set Services to manual startup so that windows scheduler will start after tempdb adjusted
Set-Service -Name MSSQLSERVER -StartupType Manual
Set-Service -Name SQLSERVERAGENT -StartupType Manual
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Set-Service -Name MsDtsServer130 -StartupType Manual
}

<#2 - using below powershell script to create the startup script#>
$script=
'
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete() 
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = "D:\pagefile.sys";} 
}
Start-Service $SQLService
Start-Service $SQLAgentService
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Start-Service -Name MsDtsServer130
}
'

Out-File -FilePath C:\SQL-startup.ps1 -InputObject $script -NoClobber


<#3 - using below script to create a scheduled task to call a powershell script that creates a folder on the d drive.#>
$trigger = New-JobTrigger -AtStartup -RandomDelay 00:00:30
Register-ScheduledJob -Trigger $trigger -FilePath C:\SQL-startup.ps1 -Name CreateSqlFolderOnDSsd

** Editar, acima assume SQL 2016 e pelo menos powershell 5 (embora eu não tenha conhecimento de dependências específicas, deve funcionar pelo menos na v4).