Updating multiple SQL instances simultaneously

  • [アーティクル]

A customer I was working with has undergone a lot of growth recently, and to support their development efforts has deployed a LOT of dev/test environments.  As often happens with rapid growth and expansion, these environments were in varying states depending on the care and feeding they'd been receiving, and we were helping them get a handle on these environments, standardizing their management and configuration with a variety of tools and best practices.  One piece that sorely needed to be addressed was getting all of the SQL instances (which included databases instances as well as SSRS and SSIS servers) up to a common patch level.  As they had so many servers to patch, they asked if I could write something to help (they do have patch management software, but didn't use it for SQL service packs of CUs).

I went ahead and wrote a small PowerShell snip which I share here which uses the ForEach -parallel workflow which takes a list of servers and, on each one simultaneously runs the service pack.  It's not really very interesting, although I did discover that when you connect to a remote share within the Invoke-Command window, the identity comes across as anonymous, so injecting the credentials with a PSDrive fixed that.

Anyhow, there's a lot more you could do with this, and you could extend the pattern for running about any executable.  You'd also likely not want to do this in a production environment, especially with clustered SQL instances, as there you'd want to deliberately patch the passive node(s), roll the instance and patch the remaining node, instead of the "Blindly apply patch restart" school of updating.  But it did do a great job of simultaneously (and quickly I might add) get a few dozen SQL instances up to a common patch level.

Enjoy.
$targetServers = @("Server1","Server2","Server3","Server4") $patchSource = "\\fileshare1\SQLMedia" $patchFile = "\SQLServer2012SP3-KB3072779-x64-ENU.exe" workflow ApplyUpdates { param ( $targetServers, $patchSource, $patchFile, $cred) foreach -parallel ($server in $targetServers) { $defaultDestPath = "\\$server\c$\temp" $defaultDestFull = $defaultDestPath + $patchFile $defaultSourceFull = $patchSource + $patchFile InlineScript { Invoke-Command -ComputerName $using:server -ArgumentList $using:Cred, $using:patchSource, $using:patchFile -ScriptBlock {` param ( $cred, $patchSource, $patchFile) New-PSDrive -name PatchDrive -Root $patchSource -PSProvider FileSystem -Credential $cred $installSource = "PatchDrive:\" + $patchFile $arguments =@() $arguments += "/quiet" $arguments += "/allinstances" $arguments += "/IAcceptSQLServerLicenseTerms" Start-Process $installSource -wait -ArgumentList $arguments -verb runas Restart-Computer -Force } } } } $cred = Get-Credential ApplyUpdates $targetServers $patchSource $patchFile $cred

Comments

  • Anonymous
    May 01, 2019
    Hi,I have used this script to update in lower environment through remotely, installation went fine but setup bootstrap log is not updating.Regards,Aleem.