SqlSetup - johlju/SqlServerDsc GitHub Wiki
SqlSetup
Parameters
| Parameter | Attribute | DataType | Description | Allowed Values |
|---|---|---|---|---|
| Action | Write | String | The action to be performed. Default value is 'Install'. | Install, Upgrade, InstallFailoverCluster, AddNode, PrepareFailoverCluster, CompleteFailoverCluster |
| SourcePath | Write | 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 | Write | PSCredential | Credentials used to access the path set in the parameter 'SourcePath'. | |
| SuppressReboot | Write | Boolean | Suppresses reboot. | |
| ForceReboot | Write | Boolean | Forces reboot. | |
| Features | Write | String | SQL features to be installed. | |
| InstanceName | Key | String | Name of the SQL instance to be installed. | |
| InstanceID | Write | String | SQL instance ID, if different from InstanceName. | |
| ProductKey | Write | String | Product key for licensed installations. | |
| UpdateEnabled | Write | String | Enabled updates during installation. | |
| UpdateSource | Write | String | Path to the source of updates to be applied during installation. | |
| SQMReporting | Write | String | Enable customer experience reporting. | |
| ErrorReporting | Write | String | Enable error reporting. | |
| InstallSharedDir | Write | String | Installation path for shared SQL files. | |
| InstallSharedWOWDir | Write | String | Installation path for x86 shared SQL files. | |
| InstanceDir | Write | String | Installation path for SQL instance files. | |
| SQLSvcAccount | Write | PSCredential | Service account for the SQL service. | |
| SQLSvcAccountUsername | Read | String | Output username for the SQL service. | |
| AgtSvcAccount | Write | PSCredential | Service account for the SQL Agent service. | |
| AgtSvcAccountUsername | Read | String | Output username for the SQL Agent service. | |
| SQLCollation | Write | String | Collation for SQL. | |
| SQLSysAdminAccounts | Write | StringArray[] | Array of accounts to be made SQL administrators. | |
| SecurityMode | Write | String | Security mode to apply to the SQL Server instance. 'SQL' indicates mixed-mode authentication while 'Windows' indicates Windows authentication. Default is Windows. | SQL, Windows |
| SAPwd | Write | PSCredential | SA password, if SecurityMode is set to 'SQL'. | |
| InstallSQLDataDir | Write | String | Root path for SQL database files. | |
| SQLUserDBDir | Write | String | Path for SQL database files. | |
| SQLUserDBLogDir | Write | String | Path for SQL log files. | |
| SQLTempDBDir | Write | String | Path for SQL TempDB files. | |
| SQLTempDBLogDir | Write | String | Path for SQL TempDB log files. | |
| SQLBackupDir | Write | String | Path for SQL backup files. | |
| FTSvcAccount | Write | PSCredential | Service account for the Full Text service. | |
| FTSvcAccountUsername | Read | String | Output username for the Full Text service. | |
| RSSvcAccount | Write | PSCredential | Service account for Reporting Services service. | |
| RSSvcAccountUsername | Read | String | Output username for the Reporting Services service. | |
| RSInstallMode | Write | String | Specifies the install mode for SQL Server Report service. | SharePointFilesOnlyMode, DefaultNativeMode, FilesOnlyMode |
| ASSvcAccount | Write | PSCredential | Service account for Analysis Services service. | |
| ASSvcAccountUsername | Read | String | Output username for the Analysis Services service. | |
| ASCollation | Write | String | Collation for Analysis Services. | |
| ASSysAdminAccounts | Write | StringArray[] | Array of accounts to be made Analysis Services admins. | |
| ASDataDir | Write | String | Path for Analysis Services data files. | |
| ASLogDir | Write | String | Path for Analysis Services log files. | |
| ASBackupDir | Write | String | Path for Analysis Services backup files. | |
| ASTempDir | Write | String | Path for Analysis Services temp files. | |
| ASConfigDir | Write | String | Path for Analysis Services config. | |
| ASServerMode | Write | String | The server mode for SQL Server Analysis Services instance. The default is to install in Multidimensional mode. Valid values in a cluster scenario are MULTIDIMENSIONAL or TABULAR. Parameter ASServerMode is case-sensitive. All values must be expressed in upper case. | MULTIDIMENSIONAL, TABULAR, POWERPIVOT |
| ISSvcAccount | Write | PSCredential | Service account for Integration Services service. | |
| ISSvcAccountUsername | Read | String | Output username for the Integration Services service. | |
| SqlSvcStartupType | Write | String | Specifies the startup mode for SQL Server Sql Engine service. | Automatic, Disabled, Manual |
| AgtSvcStartupType | Write | String | Specifies the startup mode for SQL Server Sql Agent service. | Automatic, Disabled, Manual |
| IsSvcStartupType | Write | String | Specifies the startup mode for SQL Server Integration service. | Automatic, Disabled, Manual |
| AsSvcStartupType | Write | String | Specifies the startup mode for SQL Server Analysis service. | Automatic, Disabled, Manual |
| RSSVCStartupType | Write | String | Specifies the startup mode for SQL Server Report service. | Automatic, Disabled, Manual |
| BrowserSvcStartupType | Write | String | Specifies the startup mode for SQL Server Browser service. | Automatic, Disabled, Manual |
| FailoverClusterGroupName | Write | String | The name of the resource group to create for the clustered SQL Server instance. Default is 'SQL Server (InstanceName)'. | |
| FailoverClusterIPAddress | Write | StringArray[] | Array of IP Addresses to be assigned to the clustered SQL Server instance. | |
| FailoverClusterNetworkName | Write | String | Host name to be assigned to the clustered SQL Server instance. | |
| SqlTempdbFileCount | Write | UInt32 | Specifies the number of tempdb data files to be added by setup. | |
| SqlTempdbFileSize | Write | UInt32 | Specifies the initial size of each tempdb data file in MB. | |
| SqlTempdbFileGrowth | Write | UInt32 | Specifies the file growth increment of each tempdb data file in MB. | |
| SqlTempdbLogFileSize | Write | UInt32 | Specifies the initial size of each tempdb log file in MB. | |
| SqlTempdbLogFileGrowth | Write | UInt32 | Specifies the file growth increment of each tempdb data file in MB. | |
| NpEnabled | Write | 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 | Write | 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 | Write | 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, and error will be thrown. | |
| FeatureFlag | Write | StringArray[] | Feature flags are used to toggle functionality on or off. See the documentation for what additional functionality exist through a feature flag. | |
| UseEnglish | Write | 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 | Write | StringArray[] | Specifies optional skip rules during setup. | |
| IsClustered | Read | Boolean | Returns a boolean value of $true if the instance is clustered, otherwise it returns $false. |
Description
The SqlSetup DSC resource installs SQL Server on the target node.
Requirements
- Target machine must be running Windows Server 2012 or later.
- For configurations that utilize the 'InstallFailoverCluster' action, the following
parameters are required (beyond those required for the standalone installation).
See the article Install SQL Server from the Command Prompt
under the section Failover Cluster Parameters
for more information.
- InstanceName (can be MSSQLSERVER if you want to install a default clustered instance).
- FailoverClusterNetworkName
- FailoverClusterIPAddress
- Additional parameters need when installing Database Engine.
- InstallSQLDataDir
- AgtSvcAccount
- SQLSvcAccount
- SQLSysAdminAccounts
- Additional parameters need when installing Analysis Services.
- ASSysAdminAccounts
- AsSvcAccount
- The parameters below can only be used when installing SQL Server 2016 or
later:
- SqlTempdbFileCount
- SqlTempdbFileSize
- SqlTempdbFileGrowth
- SqlTempdbLogFileSize
- SqlTempdbLogFileGrowth
Note: It is not possible to add or remove features to a SQL Server failover cluster. This is a limitation of SQL Server. See article You cannot add or remove features to a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 failover cluster.
Feature flags
Feature flags are used to toggle functionality on or off. One or more
feature flags can be added to the parameter FeatureFlag, i.e.
FeatureFlag = @('DetectionSharedFeatures').
NOTE: The functionality, exposed with a feature flag, can be changed from one release to another, including having breaking changes.
| Flag | Description |
|---|---|
| - | - |
Skip rules
The parameter SkipRule accept one or more skip rules with will be passed
to setup.exe. Using the parameter SkipRule is not recommended in a
production environment unless there is a valid reason for it.
For more information about skip rules see the article SQL 2012 Setup Rules – The 'Missing Reference'.
Credentials for running the resource
PsDscRunAsCredential
If PsDscRunAsCredential is set, the installation will be performed with those credentials, and the user name will be used as the first system administrator.
SYSTEM
If PsDscRunAsCredential is not assigned credentials then installation will be performed by the SYSTEM account. When installing as the SYSTEM account, then parameter SQLSysAdminAccounts and ASSysAdminAccounts must be specified when installing feature Database Engine and Analysis Services respectively.
Credentials for service accounts
Service Accounts
Service account username containing dollar sign ('$') is allowed, but if the dollar sign is at the end of the username it will be considered a Managed Service Account.
Managed Service Accounts
If a service account username has a dollar sign at the end of the name it will be considered a Managed Service Account. Any password passed in the credential object will be ignored, meaning the account is not expected to need a '*SVCPASSWORD' argument in the setup arguments.
Note about 'tempdb' properties
The properties SqlTempdbFileSize and SqlTempdbFileGrowth that are
returned from Get-TargetResource will return the sum of the average size
and growth. If tempdb has data files with both percentage and megabytes the
value returned is a sum of the average megabytes and the average percentage.
For example is there is one data file using growth 100MB and another file
having growth set to 10% then the returned value would be 110.
This will be notable if there are multiple files in the filegroup PRIMARY
with different sizes and growths.
Known issues
All issues are not listed here, see here for all open issues.
Examples
Example 1
This example shows how to install a default instance of SQL Server, and Analysis Services in Tabular mode, on a single server.
.NOTES SQL Server setup is run using the SYSTEM account. Even if SetupCredential is provided it is not used to install SQL Server at this time (see issue #139).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server
SqlSetup 'InstallDefaultInstance'
{
InstanceName = 'MSSQLSERVER'
Features = 'SQLENGINE,AS'
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup'
ASServerMode = 'TABULAR'
ASConfigDir = 'C:\MSOLAP\Config'
ASDataDir = 'C:\MSOLAP\Data'
ASLogDir = 'C:\MSOLAP\Log'
ASBackupDir = 'C:\MSOLAP\Backup'
ASTempDir = 'C:\MSOLAP\Temp'
SourcePath = 'C:\InstallMedia\SQL2016RTM'
NpEnabled = $true
TcpEnabled = $true
UpdateEnabled = 'False'
UseEnglish = $true
ForceReboot = $false
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#endregion Install SQL Server
}
}
Example 2
This example shows how to install a named instance of SQL Server on a single server.
.NOTES SQL Server setup is run using the SYSTEM account. Even if SetupCredential is provided it is not used to install SQL Server at this time (see issue #139).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server
SqlSetup 'InstallNamedInstance-INST2016'
{
InstanceName = 'INST2016'
Features = 'SQLENGINE,AS'
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Backup'
ASConfigDir = 'C:\MSOLAP13.INST2016\Config'
ASDataDir = 'C:\MSOLAP13.INST2016\Data'
ASLogDir = 'C:\MSOLAP13.INST2016\Log'
ASBackupDir = 'C:\MSOLAP13.INST2016\Backup'
ASTempDir = 'C:\MSOLAP13.INST2016\Temp'
SourcePath = 'C:\InstallMedia\SQL2016RTM'
UpdateEnabled = 'False'
ForceReboot = $false
BrowserSvcStartupType = 'Automatic'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#endregion Install SQL Server
}
}
Example 3
This example shows how to install a named instance of SQL Server on a single server, from an UNC path.
.NOTES Assumes the credentials assigned to SourceCredential have read permission on the share and on the UNC path. The media will be copied locally, using impersonation with the credentials provided in SourceCredential, so that the SYSTEM account can access the media locally.
SQL Server setup is run using the SYSTEM account. Even if SetupCredential is provided it is not used to install SQL Server at this time (see issue #139).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server
SqlSetup 'InstallNamedInstance-INST2016'
{
InstanceName = 'INST2016'
Features = 'SQLENGINE,AS'
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Backup'
ASConfigDir = 'C:\MSOLAP13.INST2016\Config'
ASDataDir = 'C:\MSOLAP13.INST2016\Data'
ASLogDir = 'C:\MSOLAP13.INST2016\Log'
ASBackupDir = 'C:\MSOLAP13.INST2016\Backup'
ASTempDir = 'C:\MSOLAP13.INST2016\Temp'
SourcePath = '\\fileserver.company.local\images$\SQL2016RTM'
SourceCredential = $SqlInstallCredential
UpdateEnabled = 'False'
ForceReboot = $false
BrowserSvcStartupType = 'Automatic'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#endregion Install SQL Server
}
}
Example 4
This example shows how to install the first node in a SQL Server failover cluster.
.NOTES This example assumes that a Failover Cluster is already present with a Cluster Name Object (CNO), IP-address. This example also assumes that that all necessary shared disks is present, and formatted with the correct drive letter, to accommodate the paths used during SQL Server setup. Minimum is one shared disk. This example also assumes that the Cluster Name Object (CNO) has the permission to manage Computer Objects in the Organizational Unit (OU) where the CNO Computer Object resides in Active Directory. This is necessary so that SQL Server setup can create a Virtual Computer Object (VCO) for the cluster group (Windows Server 2012 R2 and earlier) or cluster role (Windows Server 2016 and later). Also so that the Virtual Computer Object (VCO) can be removed when the Failover CLuster instance is uninstalled.
See the DSC resources xFailoverCluster, StorageDsc and iSCSIDsc for information how to setup a failover cluster with DSC.
The resource is run using the SYSTEM account, but the setup is run using impersonation, with the credentials in SetupCredential, when Action is 'InstallFailoverCluster'.
Assumes the credentials assigned to SourceCredential have read permission on the share and on the UNC path. The media will be copied locally, using impersonation with the credentials provided in SourceCredential, so that the impersonated credentials in SetupCredential can access the media locally.
Setup cannot be run using PsDscRunAsCredential at this time (see issue #405 and issue #444). That also means that at this time PsDscRunAsCredential can not be used to access media on the UNC share.
There is currently a bug that prevents the resource to logon to the instance if the current node is not the active node. This is because the resource tries to logon using the SYSTEM account instead of the credentials in SetupCredential, and the resource does not currently support the built-in PsDscRunAsCredential either (see issue #444).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server Failover Cluster
SqlSetup 'InstallNamedInstanceNode1-INST2016'
{
Action = 'InstallFailoverCluster'
ForceReboot = $false
UpdateEnabled = 'False'
SourcePath = '\\fileserver.company.local\images$\SQL2016RTM'
SourceCredential = $SqlInstallCredential
InstanceName = 'INST2016'
Features = 'SQLENGINE,AS'
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
SQLCollation = 'Finnish_Swedish_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSvcAccount = $SqlServiceCredential
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
# Drive D: must be a shared disk.
InstallSQLDataDir = 'D:\MSSQL\Data'
SQLUserDBDir = 'D:\MSSQL\Data'
SQLUserDBLogDir = 'D:\MSSQL\Log'
SQLTempDBDir = 'D:\MSSQL\Temp'
SQLTempDBLogDir = 'D:\MSSQL\Temp'
SQLBackupDir = 'D:\MSSQL\Backup'
ASConfigDir = 'D:\AS\Config'
ASDataDir = 'D:\AS\Data'
ASLogDir = 'D:\AS\Log'
ASBackupDir = 'D:\AS\Backup'
ASTempDir = 'D:\AS\Temp'
FailoverClusterNetworkName = 'TESTCLU01A'
FailoverClusterIPAddress = '192.168.0.46'
FailoverClusterGroupName = 'TESTCLU01A'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#region Install SQL Server Failover Cluster
}
}
Example 5
This example shows how to add a node to an existing SQL Server failover cluster.
.NOTES This example assumes that a Failover Cluster is already present with the first SQL Server Failover Cluster node already installed. This example also assumes that that the same shared disks on the first node is also present on this second node.
See the example 4-InstallNamedInstanceInFailoverClusterFirstNode.ps1 for information how to setup the first SQL Server Failover Cluster node.
The resource is run using the SYSTEM account, but the setup is run using impersonation, with the credentials in SetupCredential, when Action is 'Addnode'.
Assumes the credentials assigned to SourceCredential have read permission on the share and on the UNC path. The media will be copied locally, using impersonation with the credentials provided in SourceCredential, so that the impersonated credentials in SetupCredential can access the media locally.
Setup cannot be run using PsDscRunAsCredential at this time (see issue #405 and issue #444). That also means that at this time PsDscRunAsCredential can not be used to access media on the UNC share.
There is currently a bug that prevents the resource to logon to the instance if the current node is not the active node. This is because the resource tries to logon using the SYSTEM account instead of the credentials in SetupCredential, and the resource does not currently support the built-in PsDscRunAsCredential either (see issue #444).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server Failover Cluster
SqlSetup 'InstallNamedInstanceNode2-INST2016'
{
Action = 'AddNode'
ForceReboot = $false
UpdateEnabled = 'False'
SourcePath = '\\fileserver.company.local\images$\SQL2016RTM'
SourceCredential = $SqlInstallCredential
InstanceName = 'INST2016'
Features = 'SQLENGINE,AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
FailoverClusterNetworkName = 'TESTCLU01A'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#region Install SQL Server Failover Cluster
}
}
Example 6
This example shows how to install a named instance of SQL Server on a single server.
.NOTES SQL Server setup is run using the SYSTEM account. Even if SetupCredential is provided it is not used to install SQL Server at this time (see issue #139).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server
SqlSetup 'InstallNamedInstance-INST2016'
{
InstanceName = 'INST2016'
Features = 'SQLENGINE,AS'
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Data'
SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.INST2016\MSSQL\Backup'
ASConfigDir = 'C:\MSOLAP13.INST2016\Config'
ASDataDir = 'C:\MSOLAP13.INST2016\Data'
ASLogDir = 'C:\MSOLAP13.INST2016\Log'
ASBackupDir = 'C:\MSOLAP13.INST2016\Backup'
ASTempDir = 'C:\MSOLAP13.INST2016\Temp'
SourcePath = 'C:\InstallMedia\SQL2016RTM'
UpdateEnabled = 'False'
ForceReboot = $false
SqlSvcStartupType = 'Automatic'
AgtSvcStartupType = 'Disabled'
AsSvcStartupType = 'Automatic'
BrowserSvcStartupType = 'Automatic'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#endregion Install SQL Server
}
}
Example 7
This example shows how to install a default instance of SQL Server, and Analysis Services in Tabular mode, on a single server. It contains configurations that apply to Sql Server 2016 or later only.
.NOTES SQL Server setup is run using the SYSTEM account. Even if SetupCredential is provided it is not used to install SQL Server at this time (see issue #139).
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server
SqlSetup 'InstallDefaultInstance'
{
InstanceName = 'MSSQLSERVER'
Features = 'SQLENGINE,AS'
SQLCollation = 'SQL_Latin1_General_CP1_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
ASSvcAccount = $SqlServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
InstallSQLDataDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup'
ASServerMode = 'TABULAR'
ASConfigDir = 'C:\MSOLAP\Config'
ASDataDir = 'C:\MSOLAP\Data'
ASLogDir = 'C:\MSOLAP\Log'
ASBackupDir = 'C:\MSOLAP\Backup'
ASTempDir = 'C:\MSOLAP\Temp'
SourcePath = 'C:\InstallMedia\SQL2016RTM'
UpdateEnabled = 'False'
ForceReboot = $false
SqlTempdbFileCount = 4
SqlTempdbFileSize = 1024
SqlTempdbFileGrowth = 512
SqlTempdbLogFileSize = 128
SqlTempdbLogFileGrowth = 64
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#endregion Install SQL Server
}
}
Example 8
This example shows how to ad skip rules to setup.exe.
.NOTES Using skip rules is not recommended in a production environment.
Configuration Example
{
[CmdletBinding()]
param
(
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlInstallCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential = $SqlInstallCredential,
[Parameter(Mandatory = $true)]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlServiceCredential,
[Parameter()]
[ValidateNotNullOrEmpty()]
[System.Management.Automation.PSCredential]
$SqlAgentServiceCredential = $SqlServiceCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
#region Install prerequisites for SQL Server
WindowsFeature 'NetFramework35'
{
Name = 'NET-Framework-Core'
Source = '\\fileserver.company.local\images$\Win2k12R2\Sources\Sxs' # Assumes built-in Everyone has read permission to the share and path.
Ensure = 'Present'
}
WindowsFeature 'NetFramework45'
{
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
#endregion Install prerequisites for SQL Server
#region Install SQL Server Failover Cluster
SqlSetup 'InstallNamedInstanceNode1-INST2016'
{
Action = 'InstallFailoverCluster'
ForceReboot = $false
UpdateEnabled = 'False'
SourcePath = '\\fileserver.company.local\images$\SQL2016RTM'
SourceCredential = $SqlInstallCredential
InstanceName = 'INST2016'
Features = 'SQLENGINE'
InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
InstanceDir = 'C:\Program Files\Microsoft SQL Server'
SQLCollation = 'Finnish_Swedish_CI_AS'
SQLSvcAccount = $SqlServiceCredential
AgtSvcAccount = $SqlAgentServiceCredential
SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName
# Drive D: must be a shared disk.
InstallSQLDataDir = 'D:\MSSQL\Data'
SQLUserDBDir = 'D:\MSSQL\Data'
SQLUserDBLogDir = 'D:\MSSQL\Log'
SQLTempDBDir = 'D:\MSSQL\Temp'
SQLTempDBLogDir = 'D:\MSSQL\Temp'
SQLBackupDir = 'D:\MSSQL\Backup'
FailoverClusterNetworkName = 'TESTCLU01A'
FailoverClusterIPAddress = '192.168.0.46'
FailoverClusterGroupName = 'TESTCLU01A'
# Not recommended to use in production.
SkipRule = 'Cluster_VerifyForErrors'
PsDscRunAsCredential = $SqlInstallCredential
DependsOn = '[WindowsFeature]NetFramework35', '[WindowsFeature]NetFramework45'
}
#region Install SQL Server Failover Cluster
}
}