May 2, 2011 at 11:19 pm
Comments posted to this topic are about the item SQL 2008 SP/CU Patching Through Windows Scheduled Tasks
May 3, 2011 at 2:23 am
Simple and works is welcome in a complex world 🙂
May 3, 2011 at 3:25 am
I like the idea of scripting the tasks. I do not like the idea of putting passwords in batch files. Can the scheduled tasks be set through the GUI? I think your article is great. I am going to see if these same methods can be applied with other servers (Exchange, Share point,etc) I am just concerned from a security perspective.
May 3, 2011 at 7:08 am
david.dennis (5/3/2011)
I like the idea of scripting the tasks. I do not like the idea of putting passwords in batch files. Can the scheduled tasks be set through the GUI? I think your article is great. I am going to see if these same methods can be applied with other servers (Exchange, Share point,etc) I am just concerned from a security perspective.
You can certainly schedule them through the GUI. Since we do it on so many systems, our installation repository is locked down to my 6 team members, and we're cleaning up the materials after the installation is complete, we sort of accept the risk of having the PW in the batch file.
One approach could be to have an active directory account created simple to perform this patching, provision the account only for access on systems that you are patching, and the risk of the PW being in the batch file would be decreased.
Thanks for checking out the article!
Regards,
Steve
May 3, 2011 at 7:11 am
I will give that a whirl. I will also get back to you on the other servers or do you already have info as to whether it will work on other servers?
May 3, 2011 at 7:32 am
david.dennis (5/3/2011)
I will give that a whirl. I will also get back to you on the other servers or do you already have info as to whether it will work on other servers?
I've used this method on a number of systems without any issues. We copy the materials local to the C:\temp directory to execute, and we have done this on both win 2003 32-bit and win 2008 r2 64-bit. The article is fairly long as it is, so I didn't include a win 2003 64-bit version, but it would be the same as the Win 2003 32-bit explanation except with the 64-bit exe's for the actual patches.
May 3, 2011 at 11:33 am
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?
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.
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.
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.
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.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 3, 2011 at 11:45 am
S.K. (5/2/2011)
Comments posted to this topic are about the item <A HREF="/articles/Administration/72733/">SQL 2008 SP/CU Patching Through Windows Scheduled Tasks</A>
Great post ... only one question .. how can I pass instance names .. if I multiple instances on same server and I want to only patch specific instance...
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
May 3, 2011 at 12:12 pm
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 😎
May 3, 2011 at 12:15 pm
Great post ... only one question .. how can I pass instance names .. if I multiple instances on same server and I want to only patch specific instance...
For a specific instance name, replace "/allinstances" with "/instancename=YourInstanceNameHere" and replace YourInstanceNameHere with the instance name you'd like to patch.
I believe, but am not 100% certain, that MSSQLSERVER would be the instance name for the default instance.
Thanks for the feedback,
Steve
May 3, 2011 at 12:32 pm
S.K. (5/3/2011)
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?
Yes, I have heard of folks that have issues with SCCM and it is not a popular tool to use in patching SQL Server. WSUS acts a little different than the Windows Update agent on a server/desktop. WSUS gets a list of all patches available after they are released from Microsoft. There is a sync task that is used to "sync" your WSUS server with Microsoft servers for the patches that are available and shown needed within your servers/desktops.
It has been a few years since I administered a WSUS environment but of what I do remember you would pretty much put your SQL Servers in a group. Within that group you tell WSUS what product to check for patches on, yours being SQL Server. It will then sync with Microsoft to find out what the current patches/CUs are and if that server has them installed. You can require prior approval before deploying the patch or CU that is needed.
As far as Internet connectivity goes there are ways around that I believe with the new version of WSUS. I do believe you can manually put the patch or CU within the repository for WSUS. With firewalls I have seen environments that will have one WSUS that is allowed Internet access only to the Microsoft WSUS server to download the patches. Then the internal WSUS servers all talk to that server like a parent-child relationship. Each WSUS server will sync up to the one WSUS that has Internet access. This provides a more secure environment and does not require opening up Internet access to all your servers.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 3, 2011 at 1:00 pm
Yes, I have heard of folks that have issues with SCCM and it is not a popular tool to use in patching SQL Server. WSUS acts a little different than the Windows Update agent on a server/desktop. WSUS gets a list of all patches available after they are released from Microsoft. There is a sync task that is used to "sync" your WSUS server with Microsoft servers for the patches that are available and shown needed within your servers/desktops.
I suppose it becomes a matter of personal preference. I'll definitely check out WSUS to see what it has to offer, but it sounds like another tool we'd need to administer, whereas the batch files or powershell scripts are items we can have full control over without really having to rely on agents or tooling to roll out the updates.
Best Regards,
Steve
May 3, 2011 at 10:25 pm
Thanks for the nice article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 10:46 pm
SQLRNNR (5/3/2011)
Thanks for the nice article.
Thanks, Jason!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply