未加星标

Automating SQL Server patching

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

Howdy everyone, today I’m gonna talk about SQL Server patching.

While this operation is pretty straightforward and very rarely causes issues by itself, there is nothing less entertaining than going from server to server, RDP to RDP, launching the same executable over and over. Which opens an automation opportunity for us!

Let me introduce you a new tool from the dbatools toolset: Update-DbaInstance . It will take care of SQL Server patching and all the associated with it activities. It supports updating to latest or specific version of SQL Server, allows you to restart the computer after the upgrade is finished, and, most importantly, you can run it against multiple servers in parallel .

Prerequisites Elevated session

This command is designed to be run in an elevated session. It is a must for the local installations, when you’re running the command against a localhost, but, strangely enough, it’s also required when you’re running it against remote machines and you patch repository is on a network drive (which I’m guessing is the case 99% of the time). Why? See the next prerequisite:

Powershell remoting and Credentials

Unsurprisingly, performing remote patching through Powershell requires Powershell remoting to be already configured. There is one more requirement, though, for running sessions remotely: if your patch repository is a network folder, there is no way default remoting session would be able to get them. It would run into the infamous double-hop issue and fail to delegate your current credentials when accessing that folder.


Automating SQL Server patching

The solution to that is to pass credentials to the remote server, which would authenticate you on that remote server properly. This is why, specifying explicit -Credential parameter will help you in such scenarios. In such doing, you are implicitly switching to the CredSSP authentication protocol which allows you to securely pass credentials to the remote host.


Automating SQL Server patching
Using CredSSP protocol without configuring it

The command will attempt then to configure CredSSP protocol on both client and server this is why the elevated session is required even on the client machine. Such dynamic connection configuration is only supported on Powershell 3.0 or higher: both client and server should comply with this requirement.


Automating SQL Server patching
Properly configured CredSSP with Credentials passed through the remote session

If for any reason, CredSSP configuration and/or connection has failed, the command will ask you if you want to try using a less secure protocol (Default) to pass credentials to the remote server.

With all that said, if the server is able to reach the patch file without authentication, you won’t need to specify credentials at all.

Patch repository

You’ll need to have you SQL Server update files stored somewhere where the server can reach them: a network drive or a local folder. The command would find relevant KB based on execution parameters and look for the file name recursively in specified path(s).

Important thing about the patch repository is that all the KB files should maintain their original naming, which in all cases follows a predefined file name template:

SQLServer*-KB*-*x*.exe

Having such file stored in the folder will ensure that the update command finds it among the files in the folder, but keep in mind that folders with huge number of files might slow down the search. You can specify this folder by using the -Path parameter.

Executing the command

There are a few ways you can execute the command and most of them are covered in the examples you can get from running Get-Help Update-DbaInstance -Examples


Automating SQL Server patching
Never forget about the power of Get-Help

Some of the features worth mentioning:

By default the command will try to update the server to the latest-and-greatest service pack and cumulative update You can specify -Type to choose between installing service packs, cumulative updates or both -Restart will restart the computer after the installation is successful. Very helpful when installing multiple updates at once each of them will require a restart. It will also restart the computer prior to installing the updates in case a pending restart is already queued in the system. -Version will allow you to target a specific version of SQL Server on the machine, as well as define the update level you want to reach. Some of the examples: SQL2008SP4 upgrade all SQL2008 instances to SP4 2012SP3CU11 upgrade all SQL2012 instances to SP3 CU11 2016 upgrade all SQL2016 instances to the latest version available SP4 upgrade all instances found on the computer to SP4, regardless of their version Specifying more than one computer in -ComputerName will initiate a parallel update once all the update paths have been discovered and confirmed This command is considered as a high-risk command, which means it would ask you to confirm each update path before executing it. To disable that behavior use -Confirm:$false It can be a good idea to run the command in the -WhatIf mode first to assess the impact of it.
Automating SQL Server patching

A simple execution of the command will go through the following phases:

-Path -Restart

Update process in all its glory

Conclusion

This command is designed to turn a mundane patching process into a simple task, which now can be performed often enough to keep up with the pace of SQL Server cumulative update releases. Hope you’ll find a use for it in upcoming 2019.

Merry Christmas and Happy New Year!

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Automating SQL Server patching
本站链接:https://www.codesec.net/view/627776.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(363)