This is a post on how to use a GPO and Security Group in AD to set up Instant File Initialization on all your SQL Server instances. If you don’t know what IFI is, check out John’s post here: SQL server Instant File Initialization

This setup relies on 3 things.

1: You move all SQL Server Instances into a single OU in AD
2: You have all SQL Server Service accounts in a Group
3: You have access to AD and can Create Groups, OUs and Move Servers (Never do any of these steps in Production without talking to your Server Admin Team!!!)

Lets begin:

Create an OU in AD (I called mine SQL Server) and moved all my servers into it.

SQLServerOU

 

Now create a AD Group

SQLServerEngineServiceAccounts

and add any SQL Server Engine Service Accounts.

SQLServerEngineServiceAccountsMembers

Now the fundamentals are in place, the next step is to create the GPO, Open Group Policy Management. Navigate to your SQL Server OU, Right Click and “Create a GPO in this domain, and Link it here…”

NewGPO

Name the Policy (Ive named it SQL – Instant File Initialization)

GPOIFI

Right Click on the GPO and select “Edit…”

EditGPO

Open up the tree to:

Computer Configuration -> Polices -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignments

Scroll down and Select “Perform volume maintenance tasks”

PerformVolMaintTask

Double Click to open and Tick “Define these policy settings”

and Add User or Group

EnablePerformVolMaintTask

Hit the Browse button

browseForGroup

Add your AD Group

findGroup

Click ok and confirm you can see the group in

ChangesToGPO

Close the Window. You can again confirm that the settings are in place by going to the Settings and drilling down the HTML view

GPOHTMLView

Now for the GPO to take place you will need to push that GPO to the server.

To test that the GPO is in place you can do it a couple of ways

If you have Admin access to the server you can login and open the Local Security Policy from the Administrative Tools and navigate to Security Settings -> Local Polices -> User Rights Assignments and scroll down to “Perform volume maintenance Tasks”

checkpvmt

If you dont and need to check via SQL you can go a couple of ways, heres a few:

Tibor Karaszi – Check for Instant File Initialization

Paul Randall – How to tell if you have instant file initialization enabled with WhoAmI Priv

 

 

 

Advertisements