SqlServer.adoc

DSC Resource 'SqlServer'

SqlServer is used for deployment and configuration of Microsoft SQL Server.

Source	https://github.com/dsccommunity/CommonTasks/tree/dev/CommonTasks/DscResources/SqlServer
DSC Resource	SqlServerDsc
Documentation	SqlServerDsc Wiki, SqlSetup,

Note	It is possible to use Group Managed Service Accounts and Built-In Accounts in the YAML configuration.

Table 1. Attributes of category 'SqlServer'

Parameter	Attribute	DataType	Description	Allowed Values
Setup		hashtable	The SqlSetup DSC resource installs SQL Server on the target node.

Table 2. Attributes of category 'SqlServer/Setup'

Parameter	Attribute	DataType	Description	Allowed Values
Action		String	The action to be performed.	Install (default) Upgrade InstallFailoverCluster AddNode (currently not functional) PrepareFailoverCluster CompleteFailoverCluster
SourcePath	Mandatory	String	The path to the root of the source files for installation. I.e and UNC path to a shared resource. Environment variables can be used in the path.
SourceCredential		PSCredential	Credentials used to access the path set in the parameter SourcePath. See Considerations for the parameter SourceCredential.
SuppressReboot		Boolean	Suppresses reboot.
ForceReboot		Boolean	Forces reboot.
Features		String	SQL Server features to be installed. All features might not be available on all versions of SQL Server.	SQLENGINE REPLICATION DQ DQC BOL CONN BC SDK MDS FULLTEXT RS AS IS SSMS ADV_SSMS
InstanceName		String	Specifies the name of the instance to be installed. Use MSSQLSERVER if you want to install a default clustered instance.
InstanceID		String	SQL Server instance ID (if different from parameter InstanceName).
ProductKey		String	Product key for licensed installations.
UpdateEnabled		String	Enabled updates during installation.
UpdateSource		String	Path to the source of updates to be applied during installation.
SQMReporting		String	Enable customer experience reporting.
ErrorReporting		String	Enable error reporting.
InstallSharedDir		String	Installation path for shared SQL Server files.
InstallSharedWOWDir		String	Installation path for x86 shared SQL Server files.
InstanceDir		String	Installation path for SQL Server instance files.
SQLSvcAccount		PSCredential	Service account for the SQL Server's Windows service.
AgtSvcAccount		PSCredential	Service account for the SQL Agent's Windows service.
SQLCollation		String	Collation for SQL Server Database Engine.
SQLSysAdminAccounts		String[]	An array of accounts to be made SQL Server administrators.
SecurityMode		String	Security mode to apply to the SQL Server instance. The value SQL indicates mixed-mode authentication while the value Windows indicates Windows Authentication.	SQL Windows (default)
SAPwd		PSCredential	Specifies the SA account’s password. Only applicable if parameter SecurityMode is set to SQL.
InstallSQLDataDir		String	Root path for SQL Server database files.
SQLUserDBDir		String	Path for SQL Server database files.
SQLUserDBLogDir		String	Path for SQL Server log files.
SQLTempDBDir		String	Path for SQL Server temporary database data files.
SQLTempDBLogDir		String	Path for SQL Server temporary database log files.
SQLBackupDir		String	Path for SQL Server backup files.
FTSvcAccount		PSCredential	Service account for the Full Text's Windows service.
RSSvcAccount		PSCredential	Service account for Reporting Services's Windows service.
RSInstallMode		String	Specifies the install mode for SQL Server Report Services service.	SharePointFilesOnlyMode DefaultNativeMode FilesOnlyMode
ASSvcAccount		PSCredential	Service account for Analysis Services's Windows service.
ASCollation		String	Collation for the SQL Server Analysis Services.
ASSysAdminAccounts		String[]	Array of accounts to be made Analysis Services admins.
ASDataDir		String	Path for Analysis Services's data files.
ASLogDir		String	Path for Analysis Services's log files.
ASBackupDir		String	Path for Analysis Services's backup files.
ASTempDir		String	Path for Analysis Services's temp files.
ASConfigDir		String	Path for Analysis Services's config files.
ASServerMode			The server mode for SQL Server Analysis Services instance. The default is to install in Multidimensional mode. + Important Parameter is case-sensitive. All values must be expressed in upper case.	MULTIDIMENSIONAL TABULAR POWERPIVOT
ISSvcAccount		PSCredential	Service account for Integration Services's Windows service.
SqlSvcStartupType		String	Specifies the startup mode for the SQL Server Database Engine's Windows service.	Automatic Disabled Manual
AgtSvcStartupType		String	Specifies the startup mode for the SQL Server Agent's Windows service.	Automatic Disabled Manual
IsSvcStartupType		String	Specifies the startup mode for the SQL Server Integration Services's Windows service.	Automatic Disabled Manual
AsSvcStartupType		String	Specifies the startup mode for the SQL Server Analysis Services's Windows service.	Automatic Disabled Manual
RSSVCStartupType		String	Specifies the startup mode for the SQL Server Reporting Services's Windows service.	Automatic Disabled Manual
BrowserSvcStartupType		String	Specifies the startup mode for SQL Server Browser's Windows service.	Automatic Disabled Manual
FailoverClusterGroupName		String	The name of the resource group to create for the clustered SQL Server instance.	Default: SQL Server (InstanceName)
FailoverClusterIPAddress		String[]	Specifies an array of IP addresses to be assigned to the clustered SQL Server instance. IP addresses must be in dotted-decimal notation, for example 10.0.0.100. If no IP address is specified, uses DEFAULT for this setup parameter.
FailoverClusterNetworkName		String	Host name to be assigned to the clustered SQL Server instance.
SqlTempdbFileCount		UInt32	Specifies the number of temporary database data files to be added by setup.
SqlTempdbFileSize		UInt32	Specifies the initial size of each temporary database data file in MB.
SqlTempdbFileGrowth		UInt32	Specifies the file growth increment of each temporary database data file in MB.
SqlTempdbLogFileSize		UInt32	Specifies the initial size of each temporary database log file in MB.
SqlTempdbLogFileGrowth		UInt32	Specifies the file growth increment of each temporary database data file in MB.
NpEnabled		Boolean	Specifies the state of the Named Pipes protocol for the SQL Server service. The value $true will enable the Named Pipes protocol and $false will disabled it.
TcpEnabled		Boolean	Specifies the state of the TCP protocol for the SQL Server service. The value $true will enable the TCP protocol and $false will disabled it.
SetupProcessTimeout		UInt32	The timeout, in seconds, to wait for the setup process to finish. Default value is 7200 seconds (2 hours). If the setup process does not finish before this time, an error will be thrown.	Default: 7200
FeatureFlag		String[]	Feature flags are used to toggle DSC resource functionality on or off. See the SqlServerDsc Wiki for what additional functionality exist through a feature flag.
UseEnglish		Boolean	Specifies to install the English version of SQL Server on a localized operating system when the installation media includes language packs for both English and the language corresponding to the operating system.
SkipRule		String[]	Specifies optional skip rules during setup.

Example

SqlServer:
  Setup:
    Action: Install
    SourcePath: \\mediaserver\install\sqlserver
    SourceCredential: '[ENC=...=]'
    SuppressReboot: false
    ForceReboot: true
    Features: SQLENGINE, REPLICATION, CONN
    InstanceName: MSSQLSERVER
    InstanceID:
    ProductKey:
    UpdateEnabled: false
    UpdateSource: \\mediaserver\install\sqlserver\updates
    SQMReporting:
    ErrorReporting:
    InstallSharedDir: C:\Program File\Sql Server Shared
    InstallSharedWOWDir:
    InstanceDir:
    SQLSvcAccount: '[ENC=...=]'
    AgtSvcAccount: '[ENC=...=]'
    SQLCollation:
    SQLSysAdminAccounts:
    SecurityMode: SQL
    SAPwd: '[ENC=...=]'
    InstallSQLDataDir:
    SQLUserDBDir:
    SQLUserDBLogDir:
    SQLTempDBDir:
    SQLTempDBLogDir:
    SQLBackupDir:
    FTSvcAccount:
    RSSvcAccount:
    RSInstallMode: DefaultNativeMode
    ASSvcAccount: '[ENC=...=]'
    ASCollation:
    ASSysAdminAccounts:
    ASDataDir:
    ASLogDir:
    ASBackupDir:
    ASTempDir:
    ASConfigDir:
    ASServerMode: MULTIDIMENSIONAL
    ISSvcAccount: '[ENC=...=]'
    SqlSvcStartupType: Automatic
    AgtSvcStartupType: Automatic
    IsSvcStartupType: Disabled
    AsSvcStartupType: Disabled
    RSSVCStartupType: Manual
    BrowserSvcStartupType: Manual
    FailoverClusterGroupName:
    FailoverClusterIPAddress:
    FailoverClusterNetworkName:
    SqlTempdbFileCount: 4
    SqlTempdbFileSize: 100
    SqlTempdbFileGrowth: 100
    SqlTempdbLogFileSize: 20
    SqlTempdbLogFileGrowth: 10
    NpEnabled: true
    TcpEnabled: true
    SetupProcessTimeout: 3600
    FeatureFlag:
    UseEnglish: true
    SkipRule: