SqlSetup: Missing Configuration Feature/Options : Instant File Initialization

[mdaniou]

Details of the scenario you tried and the problem that is occurring

This issue is created to bring some improvements to the SqlSetup DSC Configuration. The idea is to report all the missing options in the resource to be allow the installation of all the features and to use all the options available in the SQL Server installer.

Option to add to the DSC Resource :

/SQLSVCINSTANTFILEINIT : Enables instant file initialization for SQL Server service account. For security and performance considerations, see Database Instant File Initialization.

The DSC configuration that is using the resource (as detailed as possible)

SqlSetup

Version of the operating system and PowerShell the target node is running

Windows Server 2012/2012R2/2016

SQL Server edition and version the target node is running

SQL Server 2016/2017

What SQL Server PowerShell modules, and which version, are present on the target node.

Most recent version

Version of the DSC module you're using, or write 'dev' if you're using current dev branch

Most recent version

[SQLHorizons]

@mdaniou Note: This configuration is dependent on the DB engine service account being given the Perform Volume Maintenance permissions. As this is listed as a possible security attack point in ciscat controls for windows server, you may find some organisations have implemented GPOs that prevent this configuration.

[johlju]

@SQLHorizons Thank you for that information! That should added as a requirement to the resource (if the parameter is used) in the README.md, and a link to the security consideration could be valid to.

[movergan]

I would like to have /SQLSVCINSTANTFILEINIT too.

[advanw]

We are also missing this option. It seems there is no other way to enable this option through DSC.

[johlju]

Happy to review a PR that adds this parameter to the SqlSetup resource.

[mjcarrabine]

@advanw, I was able to do this through DSC by:

1. Use the UserRightsAssignment resource in SecurityPolicyDSC

   UserRightsAssignment "CCE-36143-6: Ensure 'Perform volume maintenance tasks' is set to 'Administrators' - Exception for SQL Server"{
   Policy = 'Perform_volume_maintenance_tasks'
   Identity = @('Administrators', 'MSSQLSERVER'
       )
   Force = $true
   }

2. Then just restart the SQL Server service.

That being said, it would be great if this were part of the SqlSetup resource.

[johlju]

@mjcarrabine you mean that that configuration is setting the permission needed as per https://github.com/PowerShell/SqlServerDsc/issues/1157#issuecomment-423769065, and after that setup is ran using the parameter /SQLSVCINSTANTFILEINIT?

[mjcarrabine]

@johlju, if I am reading this (Database File Initialization) correctly, Instant File Initialization isn't really a feature that gets turned on in SQL Server.

Instead, on startup, it checks if the account has the Perform Volume Maintenance Tasks authorization, and if it does, it enables it.

The /SQLSVCINSTANTFILEINIT parameter just adds the permission during installation. Which would be nice to have for completeness, but it looks like the UserRightsAssignment DSC resource is a valid workaround for now.

[johlju]

Ah, I see, thank you for the clarification! Your configuration example will help a lot to understand how to get the current state of the property when it is returned from Get-TargetResource when we add the parameter to the SqlSetup resource.

[mjcarrabine]

@johlju, Yes, Get-TargetResource for UserRightsAssignment should be helpful to tell if the Perform Volume Maintenance Tasks authorization is set.

In addition, and I have no idea what the pros and cons of making SQL calls in a DSC resource are, but the sys.dm_server_services view will tell you if the service needs to be restarted or not.

Starting with SQL Server 2012 (11.x) SP4, and SQL Server 2016 (13.x) SP1 through SQL Server 2017, the column instant_file_initialization_enabled in the sys.dm_server_services DMV can be used to identify if instant file initialization is enabled.

[johlju]

I'm hoping the setup.exe automatically want to restart the node when that property is added. setup.exe hopefully returns error code 3010 which means a restart is needed, The the resource should automatically kick of a reboot (although currently it just writes out a warning, but there is a breaking change issue to resolve that).