What lead me to situation...
Already some time ago, I was setting up SQL server for our VMM server. I wanted to make installation of both servers as lite as possible, so I decided to use Windows server 2016 core edition for both. I had several reasons not to go with GUI version of Windows, but it is not time to talk about this now. Maybe in one of my later post 😉.Installation itself was not very challenging, but just few days after I was finished and had all things running, we got a visit from SQL PFE. Few minutes into his visit, we started to look for first candidate to test SQL health check script and SQL server with VMM database was the first on my mind. Test came our satisfactory, but with one small complaint... Instant File Initialization was not turned on.
What is Instant File Initialization (IFI) and why it is useful on servers hosting SQL...
Let me put it in one sentence only...
When you are creating new database or SQL server is extending existing ones, IFI helps extending database file, without writing ZEROs to newly occupied space on the storage. More about this, can be found here...
How to turn IFI on...
Turning IFI is done by adding SQL service account the rights to Perform volume maintenance tasks and can be seen bellow:
BUT... Since this is mmc.exe application (secpol.msc), it is NOT WORKING in Windows Server CORE edition. There are not even remote tools available to do it from another (e.g. administration) computer.
If there is a wish, there is a (command line) way....
Firstly we need to get SID of account under which SQL service is running (and this is by best practice not in local admin group!):
Then we need to get current Security Policy settings and export it to file:
Now we need to edit saved document. You can do this with Notepad. YES it works in WS2016 Core.
Into line SeManageVolumePrivilege you need to add SQL service SID that you got from AD. SID needs to have * in front and if there is already another SID specified, you need to separate them by comma (,). Here is example:
After file has been changed, we need to push changes to server:
For the changes to take affect, we need to restart SQL service and afterwards we can confirm, that IFI is turned on by searching SQL server 2016 logs for following record:
-----------------------------------------------------------------------------------
Stay curious and keep challenging your self and people around you!
Matjaž
###Get SQL service user SID Get-ADUser SQL_Service
Then we need to get current Security Policy settings and export it to file:
### Export policy on WS CORE secedit /import /areas USER_RIGHTS /cfg c:\Temp\SecPolicy.txt
Now we need to edit saved document. You can do this with Notepad. YES it works in WS2016 Core.
Into line SeManageVolumePrivilege you need to add SQL service SID that you got from AD. SID needs to have * in front and if there is already another SID specified, you need to separate them by comma (,). Here is example:
After file has been changed, we need to push changes to server:
### Import back changed config secedit /db secedit.sdb /configure /cfg c:\Temp\SecPolicy.txt /overwrite /areas USER_RIGHTS
For the changes to take affect, we need to restart SQL service and afterwards we can confirm, that IFI is turned on by searching SQL server 2016 logs for following record:
For the end...
Let me for the end sum up all commands with short comments, as I like to keep them for later use:###Get SQL service user SID Get-ADUser SQL_Service ### Export policy on WS CORE secedit /import /areas USER_RIGHTS /cfg c:\Temp\SecPolicy.txt ###################### ### Edit exported file in you default TXT Editor. ### Into line SeManageVolumePrivilege you need to add SQL service SID you got from AD ### SID needs to have * in front and if there is already another SID specified, you need to separate them by comma , ###################### ### Import back changed config secedit /db secedit.sdb /configure /cfg c:\Temp\SecPolicy.txt /overwrite /areas USER_RIGHTS ### Restart SQL service to take effect ### In SQL 2016 you can look into SQL log for "Database Instant File Initialization", which is created at SQL instance start
-----------------------------------------------------------------------------------
Stay curious and keep challenging your self and people around you!
Matjaž
Thank you for sharing this informative article on enabling instant file initialization on Microsoft SQL Server. It's good to know that it can significantly improve database performance by reducing the time required for file growth operations. This is just the kind of solution the folks at NewCasinoSI need, I'll pass it on to them!
ReplyDelete