Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 SP/CU Patching Through Windows Scheduled Tasks Expand / Collapse
Author
Message
Posted Monday, May 2, 2011 11:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964
Comments posted to this topic are about the item SQL 2008 SP/CU Patching Through Windows Scheduled Tasks
Post #1102101
Posted Tuesday, May 3, 2011 2:23 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:00 AM
Points: 60, Visits: 384
Simple and works is welcome in a complex world
Post #1102163
Posted Tuesday, May 3, 2011 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 6:18 AM
Points: 6, 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.
Post #1102184
Posted Tuesday, May 3, 2011 7:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964
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
Post #1102302
Posted Tuesday, May 3, 2011 7:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 6:18 AM
Points: 6, 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?
Post #1102307
Posted Tuesday, May 3, 2011 7:32 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964
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.
Post #1102330
Posted Tuesday, May 3, 2011 11:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:54 PM
Points: 860, Visits: 2,394
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1102556
Posted Tuesday, May 3, 2011 11:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:09 AM
Points: 736, Visits: 3,728
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
Post #1102563
Posted Tuesday, May 3, 2011 12:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964
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
Post #1102600
Posted Tuesday, May 3, 2011 12:15 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:52 PM
Points: 968, Visits: 964

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
Post #1102606
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse