Notes on Instant File Initialization and Running Scripts on Multiple Servers

Instant File Initialization (IFI)

IFI can be a useful performance booster if you’re in an environment where your database files grow regularly, although there’s an argument that if that happens regularly, you should grow them massively up front in a controlled out-of-hours way, rather than doing it piecemeal, but that’s a discussion for another time.

What normally happens when a file grows is that space is allocated to the file and then filled with zeroes. With IFI, however, that last step is omitted, allowing for files to be created and resized significantly more quickly. This applies to SQL Server Database Data files, not transaction log files.

Configuring IFI Manually

To configure IFI manually, you heed to look in the “Local Security Policy” tool – Start->Run “secpol.msc”. In the treeview on the left, go to Security Settings -> Local Policies -> User Rights Assignment.

In the listview on the right, go to “Perform Volume Maintenance Tasks”, double click, and add the user that runs the SQL Server service.

Easy enough, for one server. What if you’ve got many?

Multiple servers

What if you’ve got many servers to deal with? Well, you could do them one at a time, going through the whole log in, fire up secpol, rummage around, find out the user ID for SQL Service, and give it the right rights rigmarole, but that would be a real pain in the backside.

What you should do is have this as part of your build script (what do you mean, you’re not doing scripted deployments?), and Mike Fal demonstrates this in this blog post on Powershell and Automating SQL Server Builds.

What if you don’t use the same service account for each server? Or what if you don’t trust the guy who set the servers up to have used the same user ID? Then you’ll need to detect the service account being used by the SQL Server service, and for that, I’m indebted to the partial script in The Scripting Guy’s post on finding service accounts with Powershell.

As for the whole “looping round all the servers” bit, well, I’ll leave that to you. In my case, I have an Azure environment wherein all the SQL Servers (and only the SQL Servers) have a name that matches the pattern “*SQLSRV*”, so I can use the Get-AzureVM method, and a bit of a lashed-together script to loop through and create remote sessions on each server.

The final script

$SQLServers = get-azureVM | where-object {$_.name -like '*SQL*'}
 
$UserName = '<<admin user name>>'
$UserDomain = '<<domain name>>'
$UserPassword = '<<admin user password>>'
$UserFullName = $UserDomain + '\' + $UserName
 
$UserCred = New-Object -TypeName System.Management.Automation.PSCredential `
                         -ArgumentList $UserFullName, `
                                       (ConvertTo-SecureString -String $UserPassword -AsPlainText -Force)
 
$so = New-PSSessionOption -SkipCACheck -SkipCNCheck
 
 
$SQLServers | ForEach-Object -Process {
    $SQLServer = $_
    $s = New-PSSession -ComputerName $SQLServer.Name -UseSSL -SessionOption $so -Cred $UserCred
 
    Invoke-Command -Session $s -scriptblock {
        #Configure Instant File Initialization – from MikeFal
        $svcaccount = (get-wmiobject win32_service | where-object {$_.Name -ilike 'MSSQLSERVER'} ).startname  #This bit from Scripting Guy
        secedit /export /areas USER_RIGHTS /cfg C:\Source\templocalsec.cfg
        $privline = Get-Content C:\Source\templocalsec.cfg | Select-String 'SeManageVolumePrivilege'
        (Get-Content C:\Source\templocalsec.cfg).Replace($privline,"$privline,$svcaccount") | Out-File C:\Source\newlocalsec.cfg
        secedit /configure /db secedit.sdb /cfg C:\Source\newlocalsec.cfg
    }
}

Yes, I know, there are some weaknesses here – no error handling, should get User Credentials in a more secure manner, but this is a quick one-off hack. Make sure you know what you’re doing before you run it. Back your environment up, check your will is up to date, and don’t drink and drive.