Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 SP/CU Patching Through Windows Scheduled Tasks


SQL 2008 SP/CU Patching Through Windows Scheduled Tasks

Author
Message
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
Comments posted to this topic are about the item SQL 2008 SP/CU Patching Through Windows Scheduled Tasks
paul.millar
paul.millar
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 473
Simple and works is welcome in a complex world :-)
david.dennis
david.dennis
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
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.
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
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
david.dennis
david.dennis
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18
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?
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
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.
Shawn Melton
Shawn Melton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 3496
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 :-D 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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
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 :-)
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109
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 :-D 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 Cool
S. Kusen
S. Kusen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1094 Visits: 1109

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search