Thanks for the reply, Shawn. I'll try to justify each of your statements. I realize there are many ways to get the patches pushed out, but this is merely one way that I found I had the best control over the installation and didn't really need to do much after kicking off the initial batch file. With the volume of systems to patch and the different teams we need to work with to get maintenance windows, this seemed to meet our goals.
Shawn Melton (5/3/2011)
A few questions, pure curiosity....Why not use WSUS to deploy the service packs and CU for SQL Server? How does your shop handle the OS updates if you do not have WSUS in place?
Our shop uses SCCM to push the OS updates and they send them down as an advertisement. The reason I chose not to use WSUS or SCCM is that with our maintenance windows, we wanted to get everything done as tightly as possible. SCCM could certainly do the job, but we had some time constraints that couldn't be met with building the advertisements, testing them out on a few boxes, etc. There was an external team dependency as well for utilizing SCCM. Then, we have the issue of some SCCM agents not functioning as desired for normal Windows patches, so it didn't seem to be reliable. As for using WSUS, I never really looked into it, but some of our hosts are not internet-connected (behind firewalls), so downloading the patches directly would be a problem, and for hosts that are internet connected, we'd need to run windows update multiple times to get the updated CU's after the SP is installed, wouldn't we?
Why reboot prior to starting the SP install? I would imagine that is the easiest way to get all connections closed into the SQL Server instance, but that adds on to downtime for patching.
A lot of our boxes are up for extended periods of time and are pending reboots from previous OS patch installations. The fact that they are up for as long as they are and are pending reboots from other patches is another situation we are working to address, but rebooting prior to the install ensures we don't come across any pending reboot issues as well as "freshen" up the OS for the patching.
How do you handle errors/issues if the SP install breaks? I did not notice any thing in your batch file examples that catch if the SP installation errors and stops. If the SP errors your sequence would continue wouldn't it? My concern would be the additional reboots and the CU patch would continue and attempt to install itself.
Good observation. While the sequence itself will still continue, the only "downside" to it continuing would be the additional reboots. The CU installer won't have anything to update if the SP failed. Because I've used this on multiple systems with minimal issues, I couldn't personally justify the added logic and complexity to handle the errors.
A suggestion to improve your process would be to put this in PowerShell. I'm not saying that batch files aren't still useful, but they are outdated 😀 Especially with use of Window Server 2008 R2 and SQL Server 2008 you have added power in controling the environment and can do more checks and balances with each step of your processes. You could still do some with SQL Server 2005 and Window Server 2003 if you have PowerShell installed/enabled. WMI calls can get you the version and service state of SQL 2005 very quickly. This would also allow you to automate checking the bootstrape log and verifying the build number of the instance before starting each installation, also giving you a snapshot of it before and after.
This is a good point, and I have started to dabble in Powershell for some other tasks, but I just didn't have the expertise nor the development time with Powershell to build this out. I also chose to go with batch files as many people are still fairly familiar with them and, quite simply, they still work 🙂
The ability to do remote calls with PowerShell could also be a benefit in having one monitoring server do this, which would allow a central location to check status of all your installations.
Again, this is a very good point. For now, we utilize SQL Server 2008 policies as well as the central management server to audit our patch levels. While that doesn't delve into the setup bootstrap, we usually have change control tickets in place and scheduled maintenance windows to get the patching done. We do validate the setup bootstrap logs after the patch process completes when we make sure any residual files, if any, are cleaned up from the patch process. We have found that this process using the batch files saves us time of having to click through the GUI, reboot the system, etc.
I truly appreciate your detailed feedback as it has triggered some new thoughts for me going forward. If anything, I hope this article spawned some new ideas around patching.
Cheers,
Steve 😎