In this post, you will learn how to automate installing SQL server service packs on a SQL cluster with the help of PowerShell.

Deploying a SQL service pack on a single Microsoft SQL Server isn't typically too bad. You've got one server to deal with and don't have to fool around with ensuring SQL continues providing service to end users. However, when it comes to installing a SQL service pack on a cluster, things change a bit. Now, you have to deal with using a Windows failover cluster and patching nodes in the correct order.

Automating the installation of a SQL service pack on a Microsoft SQL Server cluster follows four general steps:

  1. Find all nodes in the cluster by active/passive status.
  2. Ensure no nodes are pending a reboot.
  3. Run the installer silently on all passive nodes and reboot.
  4. Run the installer silently on the active node and reboot.

I assume here that PowerShell Remoting is available on all nodes and that you have a pre-downloaded service pack installer.

Finding active and passive nodes ^

We'll be automating this process with PowerShell. We'll be using the Windows failover cluster name of CLUS.domain.local as the cluster name and a file path of C:\[…].exe representing the path to the service pack you've already downloaded.

The first task is finding all nodes in the cluster. We can do this by using the Get-ClusterNode command. I'll be using PowerShell Remoting to run all cluster commands on the nodes locally.

Once we have all the cluster nodes, we then need to figure out which one is currently active and which are passive. We'll need to update the passive nodes first.

Ensuring no nodes are pending reboot ^

Next, we need to ensure no nodes are pending reboot. If they are, we must reboot them first in a separate process we won't be covering. I'll be using a custom function I've built called Test-PendingReboot for this. It returns True if a server is pending a reboot and False if it is not. It is available here.

Server pending a reboot

Server pending a reboot

Installing on passive nodes ^

If the above snippet works without throwing an error mentioning a reboot, the next step is installing the service pack on all passive nodes. To install the service pack remotely, we first need to copy the service pack to all nodes.

After the installer is on each server, we'll then extract the contents of the installer. To extract the installer, we can use the arguments /extract:"C:\Temp\SQLSP" /quiet. I use a custom function I've created called Invoke-Program that allows me to easily invoke processes on remote computers to perform the extraction and run the installer. In this example, I'm extracting the installer to the C: folder on all servers.

I now need to install it on all the passive nodes and then clean up the temporary installer and extracted folder.

If all goes well, this will install the service pack on all passive nodes. Next, I'll reboot all the passive nodes to finish the service pack installation and wait for them to come back up. They must be online before we can patch the active node.

Installing on active nodes ^

Once all the passive nodes come back up, we can finally install the service pack on the active node and reboot it. When this happens, the cluster will transfer resources to another passive node that, at this time, will have the service pack already installed.

As soon as the active node comes back up from the reboot, all nodes should be up to the expected service pack!

Conclusion ^

Note that, from my experience, this process can be tricky, and the code provided here may need some tweaking for your environment. Cases in which the SQL cluster has multiple instances installed at potentially different versions, firewall ports not allowing PowerShell Remoting, and other situations can crop up. However, this article should give you a good idea of the process and a jumpstart on the coding to create an automated Microsoft SQL Server cluster script in PowerShell!

Join the 4sysops PowerShell group!

Your question was not answered? Ask in the forum!


Leave a reply

Your email address will not be published. Required fields are marked *


© 4sysops 2006 - 2020


Please ask IT administration questions in the forums. Any other messages are welcome.


Log in with your credentials


Forgot your details?

Create Account