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.