Skip to content

SqlServer.adoc

Latest commit

 

History

History
731 lines (612 loc) · 15.4 KB

SqlServer.adoc

File metadata and controls

731 lines (612 loc) · 15.4 KB

DSC Resource 'SqlServer'

SqlServer installs SQL Server instances and configures initial logins.

When to use 'SqlServer'

Use this resource when you need to perform a fresh installation of a SQL Server instance. It handles the SQL Server setup process and creates initial logins during installation. This is typically the first SQL resource applied, before SqlConfigurations, SqlDatabases, or Always On setup.

Source

DSC Resource

Documentation
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

DefaultInstanceName

String

Default SQL InstanceName

If the InstanceName of an option is not explicitly set, the DefaultInstanceName will be used.

Default: MSSQLSERVER

Setup

Hashtable

The SqlSetup DSC resource installs SQL Server on the target node.

SqlLogins

Hashtable[]

List of SQL Server logins for a SQL Server instance.
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.

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.

If not specified the value of DefaultInstanceName is used as default.

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.

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.

  • True

  • False

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.

  • True

  • False

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.

  • True

  • False

SkipRule

String[]

Specifies optional skip rules during setup.

FileStreamAccessLevel

Int16

FILESTREAM Access Level Value

  • 0 → Disables FILESTREAM support for this instance

  • 1 → Enables FILESTREAM for Transact-SQL access

  • 2 → Enables FILESTREAM for Transact-SQL and local file system access

  • 3 → Enables FILESTREAM for Transact-SQL, local and remote file system access

0 - 3
Table 3. Attributes of category 'SqlServer/SqlLogins'
Parameter Attribute DataType Description Allowed Values

Name

Key

String

The name of the login.

InstanceName

Key

String

Name of the SQL Server instance to be configured.

If not specified the value of DefaultInstanceName is used as default.

Ensure

String

The specified login should be Present or Absent.

  • Present (default)

  • Absent

LoginType

String

The type of login to be created.

If LoginType is WindowsUser or WindowsGroup then provide the name in the format DOMAIN\name.

The login types Certificate, AsymmetricKey, ExternalUser and ExternalGroup are not yet implemented and will currently throw an exception if used.

  • WindowsUser (default)

  • WindowsGroup

  • SqlLogin

  • Certificate

  • AsymmetricKey

  • ExternalUser

  • ExternalGroup

ServerName

String

The hostname of the SQL Server to be configured.

Default: current computer name

LoginCredential

PSCredential

Specifies the password.

Only applies to SQL Logins.

LoginMustChangePassword

Boolean

Specifies if the login is required to have its password change on the next login.

Only applies to SQL Logins. This cannot be updated on a pre-existing SQL Login and any attempt to do this will throw an exception.

  • True (default)

  • False

LoginPasswordExpirationEnabled

Boolean

Specifies if the login password is required to expire in accordance to the operating system security policy.

Only applies to SQL Logins.

  • True (default)

  • False

LoginPasswordPolicyEnforced

Boolean

Specifies if the login password is required to conform to the password policy specified in the system security policy.

Only applies to SQL Logins.

  • True (default)

  • False

Disabled

Boolean

Specifies if the login is disabled.

  • True

  • False (default)

DefaultDatabase

String

Specifies the default database name.
Example
SqlServer:
  DefaultInstanceName: MSSQLSERVER
  Setup:
    Action: Install
    SourcePath: \\mediaserver\install\sqlserver
    SourceCredential: '[ENC=...=]'
    SuppressReboot: false
    ForceReboot: true
    Features: SQLENGINE, REPLICATION, CONN
    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:
    FileStreamAccessLevel: 2

  SqlLogins:
    - Name: CONTOSO\User1
      LoginType: WindowsUser
      DefaultDatabase: AdventureWorks
    - Name: CONTOSO\Group1
      LoginType: WindowsGroup
      DefaultDatabase: master
    - Name: sqlUser1
      LoginType: SqlLogin
      DefaultDatabase: master
      LoginCredential: '[ENC=PE9ianM...=]'
      LoginMustChangePassword: false
      LoginPasswordExpirationEnabled: false
      LoginPasswordPolicyEnforced: false
      Disabled: true