Introduction

In the previous article we have seen how to install SQL Server Management Studio (SSMS) using PowerShell DSC. In this article, we will explore how to use PowerShell Desired State Configuration (DSC) to automate the patching process for SQL Server, streamlining the update process and ensuring a standardized configuration.

Environment

For the demo part, we use the server, CENTRALSERVER. The current SQL Server version installed in the server is 16.0.1000.6 and will get patched to 10.4105.2.

SQL Server DSC Module Installation

Let us prepare the environment for DSC, including installing PowerShell DSC and SQL Server prerequisites in CENTRALSERVER. Open a PowerShell command line or PowerShell ISE tool as Run as Administrator. Then run this code:

# Install PowerShell Desired State Configuration (DSC)
Install-Module -Name SqlServerDsc

You may get this pop-up while installing the SqlServerDsc module. Click Yes to continue.

A folder with the module, named SqlServerDsc, will be created in the location, C:\Program Files\WindowsPowerShell\Modules as shown below.

Download the SQL Server Media File

For the demo purpose, we downloaded the latest update SQLServer2022-KB5032679-x64 exe file from Microsoft site  to C:\Downloads.

We have created a shared location \\centralserver\2022\SQL_Patches to which we have copied the downloaded exe file as shown below.

Script

The script named 'Update SQL Server.ps1' below contains steps for creating a DSC configuration function, compiling the DSC configuration, and applying the DSC configuration.

# 1. Create a configuration function 
Configuration UpdateSQL
{    
Import-DscResource -ModuleName SqlServerDsc
Import-DscResource –ModuleName 'PSDesiredStateConfiguration'
   Node CENTRALSERVER 
    { 
        Package Update
        {
            Ensure='Present'
            Name = 'Cumulative Update Package 10 for SQL Server 2022 - KB5031778'
            Path = '\\Centralserver\sql2022\Patch_Update\SQLServer2022-KB5031778-x64.exe'
            Arguments = '/quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances'
            ProductId = ''
            #Credential = $Credential
        }
}
}
# 2. Compile the script to ceate the mof file
UpdateSQL -OutputPath "C:\DSC" 
# 3. Apply the mof file in the location
Start-DscConfiguration -Path "C:\DSC" -Wait -Verbose -Force -Debug -ErrorAction SilentlyContinue

Create a DSC Configuration function

Let us execute the first part of the 'Update SQL Server.ps1' script where you create a DSC configuration function that defines the desired state of the SQL Server patch installation. This function should include the installation options for SQL Server updates, such as the Name, Path, Arguments, and ProductID to be installed.  The Resource named Node in the script below should be provided with the server name where the SQL Server must be installed. In our case it is CENTRALSERVER.

Note that the ProductID field is mandatory to be mentioned in the script but can be an empty value as well. We can also supply the ProductID if we have one from the previous installation.

Configuration UpdateSQL
{    
Import-DscResource -ModuleName SqlServerDsc
Import-DscResource –ModuleName 'PSDesiredStateConfiguration'
   Node CENTRALSERVER
    {
   
        Package Update
        {
         
            Name = 'SQLServer2022-KB5032679-x64'
            Path = '\\centralserver\sql2022\SQL_Patches\SQLServer2022-KB5032679-x64.exe'
            Arguments = '/quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances'
            ProductId = ''
           
        }
}
}

The below image shows the output of the configuration function execution.

Compile the DSC Configuration function

After creating the DSC configuration function, you need to compile it into a MOF (Managed Object Format) file using the UpdateSQL configuration function created above. This file contains the configuration of the server to install SQL Server patches. Here is the PowerShell script to compile the DSC configuration function. Create a folder named C:\DSC for saving the MOF file. Then run the compilation in the PowerShell ISE (or your own Powershell prompt).

# 2. Compile the DSC configuration function
UpdateSQL -OutputPath "C:\DSC" 

This script creates the mof file in the C:\DSC folder named CENTRALSERVER.mof. Please note that any files with the same name in the location will be overwritten if they exist.

The output of the compilation step is shown below

Apply the DSC Configuration

Once you have the MOF file created, you can apply the DSC configuration to the server using the Start-DscConfiguration commandlet as shown in the below script. This will initiate the installation of the SQL Server patch, and DSC will ensure that the installation is completed according to the defined configuration. Here is the example PowerShell script to apply the DSC configuration:

# 3. Apply the mof file in the location
Start-DscConfiguration -Path "C:\DSC" -Wait -Verbose -Force -Debug -ErrorAction SilentlyContinue

The below image shows the status when the installation gets kicked off from the PowerShell ISE.

The below image shows when the installation is completed

Reboot the server.

Verify the SQL Server patch installation

Once the installation is completed, you should verify if the SQL Server has been patched to the desired version. In our case, the desired version should be 10.4105.2.

To verify that, connect to SSMS with the connection string CENTRALSERVER , and open a new query window, then execute the below query to check the currently installed version of SQL Server.

SELECT @@version

Output should be as shown below:

Summary

The article provides the process to install an SQL Server Cumulative update 10.4105.2 (KB5032679) using Powershell DSC script. This can be used to do patching of local as well as remote servers following the steps explained in the article.