Like any other piece of software, Microsoft SQL Server needs to be kept up to date. Microsoft routinely releases service packs for SQL Server that must be installed. To install a service pack, you can either connect to a remote console of the SQL Server, run the installer, and click through the wizard, or you can do it the easy way.
Every service pack installer for SQL Server supports silent installations. This installation type enables you to use the command line to kick off an installation. In addition it also enables you to automate the service pack installation process to one, tens, or hundreds of SQL servers at once.
Once you figure out how to install a service pack silently via the command line, you can then build an automation tool using PowerShell to quickly and efficiently deploy service packs.
Prerequisites
This walkthrough article covers how to deploy SQL Server service packs with PowerShell from a remote computer. To follow along, be sure you have the following:
A SQL Server to update (version doesn’t matter) User permissions to install a service pack on the SQL Server PowerShell Remoting available on the remote SQL Server A service pack installer downloaded (You can find all service packs here. ) An intermediate level knowledge of PowerShell scripting
If you have everything in order, let’s begin!
To install a service pack silently and remotely, it takes roughly five steps:
Ensure the server does not have a pending reboot. Copy the service pack installer to the remote SQL Server. Extract the compressed installer. Initiate the installer silently. Clean up the installer.
Testing for a Pending Reboot
Because a service pack cannot install unless the Windows Server is not pending a reboot, you should check for this situation upfront. A quick way to do so is to use PowerShell. I prefer to use a function called Test-PendingReboot. By pointing this function to a server, it returns a simple True/False, letting you know if the server is pending a reboot.
Copy the Service Pack Installer to the SQL Server
Next, copy the installer you’ve downloaded from Microsoft to the remote SQL Server. I’m assuming the computer you’re copying the installer from is in the same Active Directory domain as the SQL Server. If both computers are in the same Active Directory domain, you don’t need to worry about providing alternate credentials to your PowerShell commands.
Assuming the service pack is on your local hard drive is C:SQLServerServicePacksSQL2016SP2.exe and your remote SQL Server is called SQLSRV, open up a PowerShell console and run:
You should now have the service pack installer on the root of the C drive of your SQL Server.
Extract the Compressed Installer
Once the installer is on the server, you can extract the contents of the installer. To extract the installer, use the arguments /extract:“C:TempSQLSP” /quiet. Using the file name from the previous example, run the following code on the SQL Server:
Because you’re installing a service pack remotely though, you don’t need to open up an RDP console session on the server to do so. Instead, you can use a function called Invoke-Program, which is PowerShell function that enables you to execute remote processes.
You can run extract the contents of the service pack on the remote SQL Server, using the following code:
Execute the Service Pack Installer
At this point, you’re ready to begin the installation process. Using the Invoke-Program PowerShell function again, run the setup.exe installer that was extracted from the original file, and provide /q and /allinstances switches to it. It tells the service pack installer not to bring up an installation window and to patch all of the SQL instances on the server.
You can see an example of kicking off the installer here:
Clean up the Installer
If all goes well, you have an updated SQL Server once the installer finishes. You have one last task to perform, though, cleaning up.
Assuming you don’t want to leave the files you had previously transferred to the server, remove them using the Remove-Item PowerShell command. You must, however, provide the path to the folder that the original file extracted and the original installer. Here’s how to do it:
Summary
You’ve successfully remotely installed a SQL Server service pack using nothing but a file and a PowerShell script. Now that you have the skills to update a SQL Server for one server, you can easily extend this code to multiple servers.
To deploy a service pack to multiple servers, use PowerShell constructs like a foreach loop. Place the code you learned in this article inside of a foreach loop to quickly process one or a hundred SQL servers at once!
