SQL Server Installation with PowerShell
Published on •By Joey Davis
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:
_10 $ImagePath = 'C:\en_sql_server_2017_developer_x64_dvd_11296168.iso'_10 _10 New-Item -Path C:\SQLServer -ItemType Directory_10 Copy-Item -Path (Join-Path -Path (Get-PSDrive -Name ((Mount-DiskImage -ImagePath $ImagePath -PassThru) | Get-Volume).DriveLetter).Root -ChildPath '*') -Destination C:\SQLServer\ -Recurse_10 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
_10 Get-PackageProvider -Name NuGet -ForceBootstrap
Output:
_10 Name Version DynamicOptions_10 ---- ------- --------------_10 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:
_10 Install-Module -Name SqlServerDsc -Force
Create Configuration
Create a new PowerShell script. I have named mine SQLServerConfiguration.ps1.
_22 Configuration SQLServerConfiguration_22 {_22 Import-DscResource -ModuleName PSDesiredStateConfiguration_22 Import-DscResource -ModuleName SqlServerDsc_22 node localhost_22 {_22 WindowsFeature 'NetFramework45' {_22 Name = 'NET-Framework-45-Core'_22 Ensure = 'Present'_22 }_22 _22 SqlSetup 'InstallDefaultInstance'_22 {_22 InstanceName = 'MSSQLSERVER'_22 Features = 'SQLENGINE'_22 SourcePath = 'C:\SQLServer'_22 SQLSysAdminAccounts = @('Administrators')_22 DependsOn = '[WindowsFeature]NetFramework45'_22 }_22 }_22 }_22 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:
_10 Start-DscConfiguration -Path C:\SQLServerConfiguration -Wait -Force -Verbose
Output:
_10 ..._10 VERBOSE: [SQL01]: [[SqlSetup]InstallDefaultInstance] Features found: SQLENGINE_10 VERBOSE: [SQL01]: LCM: [ End Set ] [[SqlSetup]InstallDefaultInstance] in 297.3390 seconds._10 VERBOSE: [SQL01]: LCM: [ End Resource ] [[SqlSetup]InstallDefaultInstance]_10 VERBOSE: [SQL01]: LCM: [ End Set ]_10 VERBOSE: [SQL01]: LCM: [ End Set ] in 306.2460 seconds._10 VERBOSE: Operation 'Invoke CimMethod' complete._10 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:
_10 Get-Service -Name *SQL*
Source Code
I have posted the source code of an example for automating these steps to a repository on GitHub.