20

My company has grown from 50 SQL Servers to over 200 overnight in a merger acquisition. HELP!

My questions are:

  • How can I perform CU updates on this many servers and still have a life and keep my sanity? SCCM doesn't seem to be able to perform CU updates.

  • Has anyone else been able to perform CU updates on 100s of servers?

  • What are some other alternatives?

4
  • Shawn's method is certainly a cool method to do it. WSUS is a lot less cool, but more supported. Both are good solutions. Jul 2, 2015 at 21:31
  • 5
    What are your allowed outage windows on those machines? Can you take outages anytime you want, or do you have to do schedules for specific machines? Are clusters involved? Mirroring and replication? AlwaysOn Availability Groups? Batch jobs that might need to be stopped, like backups or ETL load jobs?
    – Brent Ozar
    Jul 2, 2015 at 22:12
  • "Cool" is not an objective way of describing something. =/ I was going to suggest an edit, but couldn't come up with anything that worked.
    – jpmc26
    Jul 3, 2015 at 1:04
  • There was a great reply to this question posted on Bret Ozars site in his article. "Why Nobody ever patches their sql Servers". brentozar.com/archive/2015/08/… Aug 6, 2015 at 16:27

2 Answers 2

15

The cool way is to use an unattended installation script that simply is called from each server, where the media for the installation is on a central directory on your network, that is accessible from each server. You will have to run the script in an elevated mode, which this is fairly easy in PowerShell.

You will need to use the CLI to extract the hotfix, I found that here. Then the CLI options for the SQL Server patch can be found here.

The following commands are what I used to apply the CU6 patch to the local SQL Server 2012 SP2 instance on my laptop. I changed to the directory I downloaded the hotfix to:

.\SQLServer2012-KB3052468-x64.exe /X:E:\Software\SQL_Server\2012\SP2_CU6\Extracted
cd Extracted
.\setup.exe /action=patch /instancename=SQL12 /quiet /IAcceptSQLServerLicenseTerms

enter image description here

enter image description here

You pretty much just work out writing this as a script for each server, then pull in that bit of the summary log (or just the whole log) to a central spot so you can go back and determine the status. Option 2 would be to just include a few lines of code to connect to each instance and verify the build number.

12

Unattended installation is the way to go - as @ShawnMelton mentioned. To deploy CU on 100's of servers, you can explore below options (test them, so you know what will work for you and your environment) :

  1. Sysinternals - PsExec will allow you to spawn installation on remote machines.

  2. PowerShell - Use remoting feature in PowerShell 3.0 and up to perform installation. e.g. Enter-PSSession -ComputerName <COMPUTERNAME>

  3. Old is Gold - using .bat file. <== this definately works with SCCM since you can deploy .bat files to multiple servers using SCCM.

    Below is a sample of installing SQL2008_SP2_and_CU_2k8_64bit (modify as per your needs). You can feed the bat file to SCCM so it can do a mass install.

    REM starting SQL SP2 cu install.
    "C:\temp\SQL2008_SP2_and_CU_2k8_64bit\CU\SQLServer2008-KB2467239-x64.exe" /allinstances /quiet
    REM SQL SP2 cu install complete
    
    REM optional   STEP to reboot your machine 
    REM Reboot the host before kicking off the install
    shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
    

As a side note (Caution !) :

I would never do this type of mass update to these many servers (100's) in one shot. Instead batch them up in manageable groups and then patch them in groups.

This way you can easily figure out if something goes wrong - e.g. if there is a bug in the CU you are installing, and it is fixed in a later CU or SP. Or installation wise also, if you are patching 100 servers in one shot and out of them 5 or 7 servers did not get properly patched, how are you going to figure that out ?

I manage more than 300+ servers in my company and I prefer to patch them in smaller batches on a weekly basis. SO you start with small number and once you gain confidence, you repeat the same.

EDIT:

Now dbatools has Update-DbaInstance.ps1 that can perform installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.