SQL Server Installation with PowerShell
If you or your organization are still using the GUI installer to setup new SQL Server installations, then you know how tedious it can be. While using the GUI may be a good experience to have at least once, there is a simpler, automated method for installing SQL Server using PowerShell Desired State Configuration.
Note: All commands specified here should be run in an administrator instance of PowerShell as elevated privileges are required. At the time of writing, commands were run in PowerShell 5.0
Steps
Obtain SQL Server Installation Media
For demo purposes, I installed SQL Server 2017 Developer edition and moved the ISO to C:\en_sql_server_2017_developer_x64_dvd_11296168.iso
on a fresh installation of Windows Server 2016.
Extract Installation Media
For SQL Server Desired State Configuration, a source path must be provided for the installation media. This may be achieved by extracting a SQL Server image to a directory. The command below mounts the image and copies the contents to C:\SQLServer
.
Command:
$ImagePath = 'C:\en_sql_server_2017_developer_x64_dvd_11296168.iso'
New-Item -Path C:\SQLServer -ItemType Directory
Copy-Item -Path (Join-Path -Path (Get-PSDrive -Name ((Mount-DiskImage -ImagePath $ImagePath -PassThru) | Get-Volume).DriveLetter).Root -ChildPath '*') -Destination C:\SQLServer\ -Recurse
Dismount-DiskImage -ImagePath $ImagePath
Acquire SqlServerDsc
If the intent is to install SQL Server on a fresh installation of Windows Server, the NuGet package provider must be installed. By combining Get-PackageProvider
with its ForceBootstrap
parameter, the package provider will either be retrieved if it is already installed or force Package Management to automatically install it.
Command
Get-PackageProvider -Name NuGet -ForceBootstrap
Output:
Name Version DynamicOptions
---- ------- --------------
NuGet 2.8.5.208 Destination, ExcludeVersion, Scope, SkipDependencies, Headers, FilterOnTag...
With the NuGet package provider present, the SqlServerDsc
package may now be installed.
Command:
Install-Module -Name SqlServerDsc -Force
Create Configuration
Create a new PowerShell script. I have named mine SQLServerConfiguration.ps1
.
Configuration SQLServerConfiguration
{
Import-DscResource -ModuleName PSDesiredStateConfiguration
Import-DscResource -ModuleName SqlServerDsc
node localhost
{
WindowsFeature 'NetFramework45' {
Name = 'NET-Framework-45-Core'
Ensure = 'Present'
}
SqlSetup 'InstallDefaultInstance'
{
InstanceName = 'MSSQLSERVER'
Features = 'SQLENGINE'
SourcePath = 'C:\SQLServer'
SQLSysAdminAccounts = @('Administrators')
DependsOn = '[WindowsFeature]NetFramework45'
}
}
}
SQLServerConfiguration
Note: If you are not familiar with PowerShell scripts, the script above first defines the Configuration
, then executes the configuration immediately below its definition.
Documentation for SqlSetup
may be viewed in the project's README. Additional examples for SqlSetup
from the SqlServerDsc
module may be seen in its repository.
Running . .\SQLServerConfiguration.ps1
in PowerShell will generate a directory named SQLServerConfiguration
, the name of the configuration function. The directory contains a single localhost.mof
file , representing the node
defined in the configuration function.
To begin deployment:
Command:
Start-DscConfiguration -Path C:\SQLServerConfiguration -Wait -Force -Verbose
Output:
...
VERBOSE: [SQL01]: [[SqlSetup]InstallDefaultInstance] Features found: SQLENGINE
VERBOSE: [SQL01]: LCM: [ End Set ] [[SqlSetup]InstallDefaultInstance] in 297.3390 seconds.
VERBOSE: [SQL01]: LCM: [ End Resource ] [[SqlSetup]InstallDefaultInstance]
VERBOSE: [SQL01]: LCM: [ End Set ]
VERBOSE: [SQL01]: LCM: [ End Set ] in 306.2460 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 306.836 seconds
Confirming Installation
To check the result of the deployment, use Test-DscConfiguration
. If successful, true
will be returned.
To see if the SQL Server services were started properly at the end of installation, the following command will display them:
Command:
Get-Service -Name *SQL*
Source Code
I have posted the source code of an example for automating these steps to a repository on GitHub.