Using Powershell to Install and Patch SQL Server
56K warning! Remember when we used to say that? Man, what a time to be alive!
I'm writing this post for my own edification, perhaps it'll benefit someone else, too. I've always had a penchant for automation, particularly mundane administrivia like configuring common Windows facets or installing software. Fortunately, I would argue that SQL Server is one of the "most automatable" software packages available, with some important changes as of v13 (2016) bringing more of the mysterious (and important) features to the installer UI.
However, I still remember the days of 2008, R2, and 2012 - back when the current SQL installer product was young and, well, slow. Building a Failover Cluster Instance took north of an hour just waiting for all of the installer rules to process between each arduous click. So much so that for a previous employer, I wrote an unattended installation builder IN DOS BATCH (LOL, right? Sign of the times, perhaps.) The purpose was simply to allow an administrator to pop through all of the major scenarios for a local instance or a failover cluster without waiting on the UI to validate your inputs on each iteration. Fortunately, this improved in 2014 and was as far as I can tell revamped with all of the installer changes that came in 2016. What a welcome change.
First, we'll start with service packs, cumulative updates, and QFE/hotfix packages. For as long as I can remember, particularly since 2008, SQL Server builds are totally cumulative. The build number is your guide to serialization - I don't know who maintains this crazy list, but they're really doing the community a great favor. Inside we're able to easily follow the build number trial from RTM to current. Aside from Service Packs, there is no need to install any package between your current build and the latest build. Note that on rare occasion, Microsoft will make an exception and provide security-only patches against the SP build of a particular version rather than including all of the CU fixes that preceded it. This can be seen in 2014 Build 5214.
When patching SQL Server from a command line, you're welcome to use Powershell or Command Prompt. There are benefits and caveats to each, both of which I consider out of scope for this posting. For syntax assistance, see this link.
This command will apply SQL Server 2016 Build 5206 on every instance where this command is ran. Note that I provided this command with the /qs command line switch. QS tells the installer to run in "quiet simple" mode, which presents all of the UI elements but no prompts (just like I mentioned earlier). You can switch this to /q and you'll receive nothing for the duration of the command, just a returned prompt when complete.
What if you want to run this command remotely? I know I do, I have more than one server and I can't actually walk up to any of them. I'm also staunchly against playing RDP bingo, wasting a SQL server's time and resources fooling with UI elements. It's all super unnecessary, so let's let these expensive computers compute already!
The above example has two options - using an Active Directory OU to seek out servers to patch (you can apply additional filters) or simply providing a hand-jammed list of servers manually. Internally, I actually query the established Central Management Server for my given environment and apply whatever filters via SQL. Example below.
One decision implied above is also important. Powershell relies on security contexts when executing; using invoke-command (or enter-pssession) presents a very narrow context by default. You need to pass a credential in order to expand that context. In the above invoke-command example, I'm running the SQL installer from a local disk rather than a common SMB/UNC path. If I were to try and use an UNC path, the process would fail as I wouldn't have permissions within my context. Working around this requires functioning CredSSP configuration. If that name reminds you of recent nightmares, have a look here. MS recently pushed down changes through Windows Update that limited CredSSP support for older encryption methods. So long as your servers are all patched up to date, this isn't the challenge. There are some great comments from Jack Neff here that references MS "Hey Scripting Guy" article contents here that I think help tie this all together. Essentially, you have to configure CredSSP using "Enable-WSManCredSSP" with the flag -role (Client|Server) for both the client and the server(s). This may be more configuration than you're comfortable with or more configuration than you deem necessary. I tend to agree and do not use this in my production environments - YMMV, of course.
Fortunately, the above scenarios work for both the installer and subsequent patching. This behavior works for standalone instances or FCIs. I typically use the configuration file method when installing SQL Server - especially for FCIs. With this method, you can contain 3 separate "lists" of configuration parameters:
I'm writing this post for my own edification, perhaps it'll benefit someone else, too. I've always had a penchant for automation, particularly mundane administrivia like configuring common Windows facets or installing software. Fortunately, I would argue that SQL Server is one of the "most automatable" software packages available, with some important changes as of v13 (2016) bringing more of the mysterious (and important) features to the installer UI.
However, I still remember the days of 2008, R2, and 2012 - back when the current SQL installer product was young and, well, slow. Building a Failover Cluster Instance took north of an hour just waiting for all of the installer rules to process between each arduous click. So much so that for a previous employer, I wrote an unattended installation builder IN DOS BATCH (LOL, right? Sign of the times, perhaps.) The purpose was simply to allow an administrator to pop through all of the major scenarios for a local instance or a failover cluster without waiting on the UI to validate your inputs on each iteration. Fortunately, this improved in 2014 and was as far as I can tell revamped with all of the installer changes that came in 2016. What a welcome change.
First, we'll start with service packs, cumulative updates, and QFE/hotfix packages. For as long as I can remember, particularly since 2008, SQL Server builds are totally cumulative. The build number is your guide to serialization - I don't know who maintains this crazy list, but they're really doing the community a great favor. Inside we're able to easily follow the build number trial from RTM to current. Aside from Service Packs, there is no need to install any package between your current build and the latest build. Note that on rare occasion, Microsoft will make an exception and provide security-only patches against the SP build of a particular version rather than including all of the CU fixes that preceded it. This can be seen in 2014 Build 5214.
When patching SQL Server from a command line, you're welcome to use Powershell or Command Prompt. There are benefits and caveats to each, both of which I consider out of scope for this posting. For syntax assistance, see this link.
This command will apply SQL Server 2016 Build 5206 on every instance where this command is ran. Note that I provided this command with the /qs command line switch. QS tells the installer to run in "quiet simple" mode, which presents all of the UI elements but no prompts (just like I mentioned earlier). You can switch this to /q and you'll receive nothing for the duration of the command, just a returned prompt when complete.
What if you want to run this command remotely? I know I do, I have more than one server and I can't actually walk up to any of them. I'm also staunchly against playing RDP bingo, wasting a SQL server's time and resources fooling with UI elements. It's all super unnecessary, so let's let these expensive computers compute already!
The above example has two options - using an Active Directory OU to seek out servers to patch (you can apply additional filters) or simply providing a hand-jammed list of servers manually. Internally, I actually query the established Central Management Server for my given environment and apply whatever filters via SQL. Example below.
One decision implied above is also important. Powershell relies on security contexts when executing; using invoke-command (or enter-pssession) presents a very narrow context by default. You need to pass a credential in order to expand that context. In the above invoke-command example, I'm running the SQL installer from a local disk rather than a common SMB/UNC path. If I were to try and use an UNC path, the process would fail as I wouldn't have permissions within my context. Working around this requires functioning CredSSP configuration. If that name reminds you of recent nightmares, have a look here. MS recently pushed down changes through Windows Update that limited CredSSP support for older encryption methods. So long as your servers are all patched up to date, this isn't the challenge. There are some great comments from Jack Neff here that references MS "Hey Scripting Guy" article contents here that I think help tie this all together. Essentially, you have to configure CredSSP using "Enable-WSManCredSSP" with the flag -role (Client|Server) for both the client and the server(s). This may be more configuration than you're comfortable with or more configuration than you deem necessary. I tend to agree and do not use this in my production environments - YMMV, of course.
Fortunately, the above scenarios work for both the installer and subsequent patching. This behavior works for standalone instances or FCIs. I typically use the configuration file method when installing SQL Server - especially for FCIs. With this method, you can contain 3 separate "lists" of configuration parameters:
- Failover cluster data (DNS name, object name, IP address, disks, etc.)
- Instance servicing data (componentry, accounts/passwords, and various options)
- Common instance parameters (the bland stuff that rarely gets changed)
Using that method, I cut the first FCI script first, then omit 50% of it for the subsequent FCI additions for a given instance. In the image below I've omitted what I'm considering to be common instance parameters for the sake of readability. I didn't post this as a Gist as I felt it was important to ensure that noone uses this example configuration verbatim - everyone has a different configuration objective!
Using the same general Powershell script above, you can install SQL Server remotely by referencing a fully populated configuration file. For a list of all available parameters, check here. Just remember - using invoke-command requires you to reference all objects locally, so you must copy your exe(s), configuration files, updates, or whatever else locally prior to calling the command. Also, as of SQL 2012 you can now use the UPDATESOURCE parameter during installation to reference a path containing SP/CU/QFE patches for SQL Server.
Have any questions? See any errors? Think I should wear more decorative hats? Drop a comment below or ping me @sqlmadhadr.
Have any questions? See any errors? Think I should wear more decorative hats? Drop a comment below or ping me @sqlmadhadr.
Comments
Post a Comment