Friday 3 November 2017

Turn on Instant File Initialization on computer without GUI

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!):

###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ž

Wednesday 25 October 2017

The best time to start is NOW

V zadnjem času sem srečal kar nekaj navdušencev nad tehnologijo, ki so z veseljem poslušali moja predavanje in predvsem postavljali veliko (zelo dobrih) vprašanj. Zadnje 3 predstavitve so tako bile na SloWUG srečanju 2x v Ljubljani in 1x v Novi Gorici. Na vseh srečanjih sem zelo užival in mislim, da je čas, da začnem tudi preko bloga širit svoja spoznanja in ugotovitve. Če pa boste iz tega našli kaj zanimivega in koristnega, pa še toliko bolje.
Image may contain: 1 person, indoor
S2D predavanje na SloWUGu v LJ (fotograf: Miha Pihler)
Predavanja na SloWUG so na žalost že minimo, lahko pa si pogledate prosojnice in nekaj koristnih PowerShell ukazov, ki jih najdete na http://skrci.me/SloWUG-MZ, če pa je več interesentov, pa jih z veseljem tudi ponovim.

Za tiste, ki me (še) ne poznate...

V zadnjih 20 letih se intenzivno ukvarjam z IT infrastrukturo in se ne zadovoljim z osnovnimi informacijami o posamezni tehnologiji, ampak me vedno zanima več. Zanima me, kako stvari delujejo pod pokrovom? Kaj je izza grafičnega vmesnika? Kako se paketki prenašajo po omrežju in kako se podatek zapiše na disk v fizični obliki?
To seveda pomeni, da znam zastaviti "težka" in nepričakovana vprašanja, vendar mi to pomaga, da tehnologijo spoznam v globino.

Nekaj o meni lahko najdete tudi na https://about.me/m12 

Zakaj...

Ker z veseljem prebiram izkušnje, znanja, ugotovitve, rešitve kompleksnih tehničnih težav, ki jih veliko IT navdušencev deli z nami in ker upam, da bo to kar bom pisal tukaj vzpodbudilo še koga iz kroga okoli društva Cancel, da deli svoje znanje in izkušnje ter da bo na koncu tukaj zapisano vsaj komu izmed vas pomagalo prihraniti kakšno minuto dela in kakšen siv las.

Kaj in kako bom pisal...

Pisal bom o tehničnih vsebinah, konferencah, koristnih povezavah in o vsem, kar menim, da bi bilo zanimivo za širšo publiko. Zelo verjetno bom pisal predvsem v angleščini, kakšno malenkost pa seveda tudi po slovensko.

Zaključna misel...

Sharing is carrying!
Keep coming back here, ask questions and be curious!

Turn on Instant File Initialization on computer without GUI

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 ...