Install SQL Server service pack using PowerShell

• Remove From My Forums

• Question

• I would like to install SQL Service pack on 100+ servers. It would be time consuming if I need to install manually. I am planning to install by powershell. But I am novice in powershell so I am not sure how to do it. What I learnt I need to use following two commands:

  $servers = Get-Content 'servers.txt'

  ForEach-Object ($server in $servers)

  $cmd = "C:\install\SQLServer2005SP1-KB913090-x86-ENU.exe /allinstances / quiet";

  Invoke-Expression -command $cmd | out-null;

  But I am not sure how to embed these pieces of codes in one place. Any help would be highly appreciated.

  Thanks

Answers

• I would like to install SQL Service pack on 100+ servers. It would be time consuming if I need to install manually. I am planning to install by powershell. But I am novice in powershell so I am not sure how to do it. What I learnt I need to use following two commands:

  $servers = Get-Content 'servers.txt'

  ForEach-Object ($server in $servers)

  $cmd = "C:\install\SQLServer2005SP1-KB913090-x86-ENU.exe /allinstances / quiet";

  Invoke-Expression -command $cmd | out-null;

  But I am not sure how to embed these pieces of codes in one place. Any help would be highly appreciated.

  Thanks

  Hi XYZ001,

  As the problem happens on SQL Server and we mainly focus on the questions about Windows Server system, for quick and accurate response to the question, it is recommended you create a new thread in the SQL Server forum. The support professional there are more familiar with it and can help you in a more efficient way.

  http://social.technet.microsoft.com/Forums/en-US/category/sqlserver

  And the following thread may be helpful.

  Installing SQL Service pack by PowerShell

  http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/83190b71-a348-4a0c-85a0-a36ecf7db4a5

  Hope the issue will be resolved soon.

  Regards,

  Anders Wang

  ---

  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • Proposed as answer by Thursday, January 26, 2012 1:28 AM
  • Marked as answer by Anders_Wang Saturday, January 28, 2012 2:32 PM

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Install SQL Server with PowerShell Desired State Configuration

• Article
• 09/14/2022
• 5 minutes to read

In this article

Have you ever gone through the SQL Server installation interface just by selecting the same buttons, entering the same information, and not giving it a second thought? The installation finished, but you forgot to specify the DBA group in the sysadmin role. Then you had to do these things:

• Drop into single-user mode.
• Add the appropriate users or groups.
• Bring SQL Server back up in multiuser mode.
• Test.

What's worse is now the confidence of the entire installation is shaken. "What else did I forget?" you might ask yourself.

Read about PowerShell Desired State Configuration (DSC). By using DSC, you build one configuration template that you can reuse over hundreds and thousands of servers. Depending on the build, you might have to tweak a few of the setup parameters. But that's not a significant issue because you can keep all of the standard settings in place. It eliminates the possibility that you'll forget to enter an important parameter.

This article explores the initial setup of a standalone instance of SQL Server 2017 on Windows Server 2016 by using the SqlServerDsc DSC resource. Some prior knowledge of DSC is helpful as we won't explore how DSC works.

Note

This article refers to PowerShell 5.1, which is the default version of PowerShell installed with Windows Server 2016, 2019, and 2022.

The following items are required for this walkthrough:

• A machine that runs Windows Server 2016.
• SQL Server 2017 installation media.
• The SqlServerDsc DSC resource.

Prerequisites

In most cases, DSC is used to handle the prerequisite requirements. But for the purposes of this demo, we handle the prerequisites manually.

Install the SqlServerDsc DSC resource

Download the SqlServerDsc DSC resource from the PowerShell Gallery by using the Install-Module cmdlet.

Note

Make sure PowerShell is running As Administrator to install the module.

Install-Module -Name SqlServerDsc

Get the SQL Server 2017 installation media

Download the SQL Server 2017 installation media to the server. We downloaded SQL Server 2017 Enterprise from a Visual Studio subscription and copied the ISO to C:\en_sql_server_2017_enterprise_x64_dvd_11293666.iso.

Now the ISO must be extracted to a directory:

New-Item -Path C:\SQL2017 -ItemType Directory
$mountResult = Mount-DiskImage -ImagePath 'C:\en_sql_server_2017_enterprise_x64_dvd_11293666.iso' -PassThru
$volumeInfo = $mountResult | Get-Volume
$driveInfo = Get-PSDrive -Name $volumeInfo.DriveLetter
Copy-Item -Path ( Join-Path -Path $driveInfo.Root -ChildPath '*' ) -Destination C:\SQL2017\ -Recurse
Dismount-DiskImage -ImagePath 'C:\en_sql_server_2017_enterprise_x64_dvd_11293666.iso'

Create the configuration

Configuration

Create the configuration function that will be called to generate the Managed Object Format (MOF) documents:

Configuration SQLInstall
{...}

Modules

Import the modules into the current session. These modules tell the configuration document how to build the MOF documents. They also tell the DSC engine how to apply the MOF documents to the server:

Import-DscResource -ModuleName SqlServerDsc

Resources

.NET Framework

SQL Server relies on the .NET Framework. So we need to make sure it's installed before we install SQL Server. The WindowsFeature resource is used to install the Net-Framework-45-Core Windows feature:

WindowsFeature 'NetFramework45'
{
     Name = 'Net-Framework-45-Core'
     Ensure = 'Present'
}

SqlSetup

The SqlSetup resource is used to tell DSC how to install SQL Server. The parameters required for a basic installation are as follows:

• InstanceName. The name of the instance. Use MSSQLSERVER for a default instance.
• Features. The features to install. In this example, we install only the SQLEngine feature.
• SourcePath. The path to the SQL installation media. In this example, we stored the SQL installation media in C:\SQL2017. A network share can minimize the space used on the server.
• SQLSysAdminAccounts. The users or groups who are to be a member of the sysadmin role. In this example, we grant the local Administrators group sysadmin access.

Note

We don't recommend this configuration in a high-security environment.

A full list and description of the parameters available on SqlSetup are available on the SqlServerDsc GitHub repository.

The SqlSetup resource installs only SQL Server and doesn't maintain the settings that are applied. An example is if the SQLSysAdminAccounts are specified at installation time. An admin might add or remove sign-ins to or from the sysadmin role. But the SqlSetup resource won't be affected. If you want DSC to enforce the membership of the sysadmin role, use the SqlServerRole resource.

Finish configuration

Configuration SQLInstall
{
     Import-DscResource -ModuleName SqlServerDsc

     node localhost
     {
          WindowsFeature 'NetFramework45'
          {
               Name   = 'NET-Framework-45-Core'
               Ensure = 'Present'
          }

          SqlSetup 'InstallDefaultInstance'
          {
               InstanceName        = 'MSSQLSERVER'
               Features            = 'SQLENGINE'
               SourcePath          = 'C:\SQL2017'
               SQLSysAdminAccounts = @('Administrators')
               DependsOn           = '[WindowsFeature]NetFramework45'
          }
     }
}

Build and deploy

Compile the configuration

Dot source the configuration script:

. .\SQLInstallConfiguration.ps1

Run the configuration function:

SQLInstall

A directory called SQLInstall is created in the working directory. It contains a file called localhost.mof. Examine the contents of the MOF, which shows the compiled DSC configuration.

Deploy the configuration

To start the DSC deployment of SQL Server, call the Start-DscConfiguration cmdlet. The following parameters are provided to the cmdlet:

• Path. The path to the folder that contains the MOF documents to deploy. An example is C:\SQLInstall.
• Wait. Wait for the configuration job to finish.
• Force. Override any existing DSC configurations.
• Verbose. Show the verbose output. It's useful when you push a configuration for the first time to aid in troubleshooting.

Start-DscConfiguration -Path C:\SQLInstall -Wait -Force -Verbose

As the configuration applies, the verbose output shows you what's happening. As long as no errors (red text) are thrown, when Operation 'Invoke CimMethod' complete appears on the screen, SQL Server should be installed.

Validate installation

DSC

The Test-DscConfiguration cmdlets can determine if the current state of the server meets the desired state. In this case, it's the SQL Server installation. The result of Test-DscConfiguration should be True:

PS C:\> Test-DscConfiguration
True

Services

The services list now returns SQL Server services:

PS C:\> Get-Service -Name *SQL*
Status  Name           DisplayName
------  ----           -----------
Running MSSQLSERVER    SQL Server (MSSQLSERVER)
Stopped SQLBrowser     SQL Server Browser
Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Running SQLTELEMETRY   SQL Server CEIP service (MSSQLSERVER)
Running SQLWriter      SQL Server VSS Writer

SQL Server

PS C:\> & sqlcmd -S $env:COMPUTERNAME
1> SELECT @@SERVERNAME
2> GO
1> quit

See also

Windows PowerShell Desired State Configuration Overview

Install SQL Server from the command prompt

Install SQL Server by using a configuration file

How do I install SQL Server 2016 Service Pack?

How do I download a SQL Server module from PowerShell?

How do I install a SQL Server service?

How do I automate a SQL patch in PowerShell?