SqlConfiguration

SqlConfiguration

Parameters

| Parameter | Attribute | DataType | Description | Allowed Values | | | | --- | | InstanceName | Key | String | Name of the SQL Server instance to be configured. | | | OptionName | Key | String | The name of the SQL Server Database Engine instance configuration option. For all possible values reference the article Server Configuration Options (SQL Server) or run sp_configure. | | | OptionValue | Required | SInt32 | The desired value of the configuration option. | | | ServerName | Write | String | The hostname of the SQL Server to be configured. Default value is the current computer name. | | | RestartService | Write | Boolean | Determines whether the instance should be restarted after updating the configuration option. | | | RestartTimeout | Write | UInt32 | The length of time, in seconds, to wait for the service to restart. Default is 120 seconds. | |

Description

The SqlConfiguration DSC resource manages the SQL Server Configuration Options on a SQL Server instance.

To list the available configuration option names run:

$serverObject = Connect-SqlDscDatabaseEngine -InstanceName 'SQL2022'
$serverObject | Get-SqlDscConfigurationOption | ft

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example shows how to configure two SQL Server instances on the same server to have CLR enabled.

.NOTES To get all available options run sp_configure on the SQL Server instance, or refer to https://msdn.microsoft.com/en-us/library/ms189631.aspx

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        foreach ($sqlInstance in @('CONTENT', 'DIST'))
        {
            SqlConfiguration ('SQLConfigCLR_{0}' -f $sqlInstance)
            {
                ServerName   = $Node.NodeName
                InstanceName = $sqlInstance
                OptionName   = 'clr enabled'
                OptionValue  = 1
            }
        }
    }
}

Example 2

This example shows how to configure two SQL Server instances on the same server to have the setting 'priority boost' enabled.

.NOTES To get all available options run sp_configure on the SQL Server instance, or refer to https://msdn.microsoft.com/en-us/library/ms189631.aspx

Configuration Example
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlConfiguration 'SQLConfigPriorityBoost'
        {

            ServerName     = 'localhost'
            InstanceName   = 'MSSQLSERVER'
            OptionName     = 'priority boost'
            OptionValue    = 1
            RestartService = $false
        }
    }
}