Technical

SQL Server Installation with PowerShell

Streamline your SQL Server installations with PowerShell. Learn how to automate the tedious setup process using Desired State Configuration (DSC). This step-by-step guide covers everything from extracting installation media to confirming successful deployment.

Published on December 26, 2017•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.

Metadata:

Reading Time: 3 minutes

Word Count: 568 words

Author: Joey Davis

Description:

@article{joeydavisme2017sql-server-installation-with-powershell,
  author = {Joey Davis},
  title = {SQL Server Installation with PowerShell},
  year = {2017},
  publisher = {joeydavis.me},
  url = {https://joeydavis.me/sql-server-installation-with-powershell},
}