Mistake: treating SQL Server as a one-time installation. SqlServerDsc applies PowerShell Desired State Configuration (DSC) to make installation and configuration repeatable, consistent, and easier to manage for compliance and security. section 8.9
A practical SqlServerDsc workflow is:
- Install the module with Install-Module SqlServerDsc; add PSDscResources when operating-system configuration is also required. section 8.9
- Define a configuration such as InstallSql, importing SqlServerDsc and accepting instance-name and edition parameters. section 8.9
- Use resources to create the instance, disable remote access, and create a Windows-user login; dependencies ensure configuration follows installation. section 8.9
- Compile the configuration with InstallSql -Edition Developer, then apply the generated configuration using Start-DscConfiguration. section 8.9
Listing 8.9 Creating a DSC configuration
Configuration InstallSql {
param (
$SqlInstanceName = 'MSSQLSERVER',
$Edition
)
Import-DscResource -ModuleName SqlServerDsc
if ($Edition -eq 'Developer') {
$ProductKey = '22222-00000-00000-00000-00000'
} elseif ($edition -eq 'Standard') {
$ProductKey = '00000-00000-00000-00000-00000'
} elseif ($edition -eq 'Enterprise') {
$ProductKey = '00000-00000-00000-00000-00000'
}
node localhost {
SqlSetup 'InstallInstance' {
InstanceName = $SqlInstanceName
Features = 'SQLENGINE'
SourcePath = 'C:\SQL Media'
SQLSysAdminAccounts = @('Administrator')
ProductKey = $ProductKey
}
SqlConfiguration 'RemoteAccess' {
InstanceName = $SqlInstanceName
OptionName = 'remote access'
OptionValue = 1
DependsOn = '[SqlSetup]InstallInstance'
}
SqlLogin 'AddSqlAdmin' {
Ensure = 'Present'
Name = 'Pete'
InstanceName = $SqlInstanceName
LoginType = 'WindowsUser'
DependsOn = '[SqlSetup]InstallInstance'
}
}
}
Inspect a complete InstallSql configuration showing SqlSetup, SQL engine features, source media, administrator assignment, and configuration resources. section 8.9
