Like any other software, Microsoft SQL Server must be kept up to date. Microsoft routinely releases service packs for SQL Server that need to be installed. To install a service pack, can connect to a remote SQL Server console, run the installer and click on the wizard, or you can do it the easy way.
Each SQL Server Service Pack Installer Supports Silent Installations. This type of installation enables you to use the command line to start an installation. At the same time, It also enables you to automate the service pack installation procedure in one, tens or hundreds of SQL servers at the same time.
Once you figure out how to install a service pack silently via command line, you can create an automation tool with PowerShell to implement service packs quickly and efficiently.
Prerequisites
This tutorial post covers how to implement SQL Server Service Packs with PowerShell from a remote computer. To move on, make sure you have the following:
- A SQL Server to upgrade (the version doesn't matter)
- User permissions to install a service pack on SQL Server
- PowerShell remoting available on remote SQL server
- A downloaded service pack installer (you can find all service packages here.)
- An intermediate level knowledge of PowerShell scripting
If you have everything in order, Let's start!
To install a service pack silently and remotely, it takes about five steps:
- Make sure the server does not have a pending reboot.
- Copy the service pack installer to the remote SQL server.
- Extract the compressed installer.
- Launch the installer silently.
- Clean the installer.
Reboot test pending
Because a service pack cannot be installed unless Windows Server is not waiting to be restarted, you should check this situation in advance. A quick way to do it is to use PowerShell. I prefer to use a function called Test pending restart. By pointing this function to a server, returns a simple True / Fake, which allows you to know if the server is waiting to be restarted.
Copy the Service Pack Installer to SQL Server
Next, copy the installer you downloaded from Microsoft to the remote SQL server. I assume that the computer from which you are copying the installer is in the same active directory domain as 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.
Taking charge that the service pack is on your local hard drive is C: SQLServerServicePacks SQL2016SP2.exe and your remote SQL server is named SQLSRV, open a powershell console and run:
Copy-Item -Path C:SQLServerServicePacksSQL2016SP2.exe -DestinationPath SQLSRVc$
you should now have the service pack installer in the root of drive c on 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. With the file name of the previous example, run the following code in sql server:
C:SQLServerServicePacksSQL2016SP2.exe /extract:"C:TempSQLSP" /quiet
Despite this, because you are installing a service pack remotely, you do not need to open an rdp console session on the server to do so. Instead, you can use a function called Invocation program, which is the powershell feature that enables you to run remote processes.
you can run extract the contents of the service pack on the remote sql server, using the following code:
Invoke-Program -ComputerName SQLSRV -FilePath 'C:SQLServerServicePacksSQL2016SP2.exe' -ArgumentList "/extract:"C:TempSQLSP" /quiet"
Run the service pack installer
In this point, you are ready to begin the installation procedure. Using the Invoke-Program PowerShell function again, run the setup.exe installer that was extracted from the original file and provide /q and /allinstances changes to him. Tells the service pack installer not to open an installation window and to patch all instances of SQL on the server.
You can see an example of how to start the installer here:
Invoke-Program -ComputerName SQLSRV -FilePath "C:TempSQLSPsetup.exe" -ArgumentList "/q /allinstances"
Clean the installer
If everything goes fine, you will have an updated sql server after the installer finishes. Despite this, you have one last task to perform: clean.
Assuming you don't want to leave the files you had previously transferred to the server, delete them with the Remove-Item PowerShell command. Despite this, you must provide the path to the folder that extracted the original file and the original installer. Here's how to do it:
Remove-Item -Path "SQLSRVc$SQLServerServicePacksSQL2016SP2.exe","SQLSRVc$tempSQLSP" -Recurse -Force
Summary
You have successfully remotely installed a SQL Server Service Pack using nothing more than a PowerShell file and script. Now that you have the skills to upgrade a SQL Server for a server, you can easily extend this code to multiple servers.
To implement a service pack on multiple servers, use PowerShell constructs as a foreach loop. Put the code you learned in this post inside a foreach loop to quickly process one or a hundred SQL servers at the same time!!
