Automating SQL Server Service Pack / Patch / Hot fix apply

Question

Automating SQL Server Service Pack / Patch / Hot fix apply

sivaprasad

SSCertifiable

Points: 6183
More actions
October 12, 2005 at 1:22 am

#115304

In one of my task I have to apply the latest SQL Server Service Pack / Patch / Hot fix on all the SQL Server Boxes in our client environment. There are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and 2000 running on Window NT, 2000 and 2003 Platforms. We have been given deadline of one month to apply the SQL Server patches access all the box.
   It is also being advised to rollout this task Quarterly on every year as the Service pack / Patch are being released.
       Hereby I am looking for some advise on
1. The best process to follow on for this task:
2. Automating this task:
      Is anyone used any third party tools like "Opsware" to automate this process?
3. Issues and Risks:
         Issues and Risks which to be faced as part of this task .
4. Misc:
       Any sort of information that would help with this task.
Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 1

Hi, not sure if this is of much help however. I would be relunctant to automatically rollout SP's, hotfixes etc across servers. I appreciate you have over 400 boxes and limited timescale. In a lot of SP upgrades, the rollback senario can be a reinstall of SQL Server. So I'd be very careful. You can do unattended SQL installs, but in relation to SP upgrades I would be that be more cautious. I am sure other people on this forum may have done so in the past. I'd approach it in terms of Patches / Hotfixes need to be rolled out. What is involved in each upgrade. How long does it take. Do you need users off the server, must replication be stopped if you have use it. What about backups prior to rollout. I have had SP upgrades for SQL2K crash on me in the past ( ie, had to switch to named pipes as opposed to TCP/IP ). Anyway I'd be interested myself in other peoples opinion. Call me old fashioned, but I'd be very careful rolling out SP's. Regards Derek.

DBA-MANAMADURAI SSC Veteran Points: 252 More actions · Answer 2

Automatically rollout the SP's is not at all good approch.Particularly , you have got different OS.And hotfixes are not necessarily apply to all the servers, unless you have issues.Otherwise this itself will break so many thing.

DBA

Vichka Ten Centuries Points: 1218 More actions · Answer 3

put SP and other stuff on the couple disks and show to couple users how to do this and just go one by one, I had to do something similar, 400 users, we were done in 2 days from 9 to 4 🙂

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

First off automation is a no-no to many things can go wrong - the worst being that you have to recover (reinstall) a SQL Server. Second, having users assist in performing the task - no , they are not qualified as DBAs.

Now lets logical about the scale of the task...

Assume it will take one hours to apply SQL Server SP/hotfixes per server (this is highly optimistic given the mix of v6.5, v7.0 and SQL 2000).
Assume that you will need 1 hour for OS patching and maintenance as well for each server (the oprimistic estimate still applies.
Given that you have 400 servers to upgrade.

Now lets do some simple math here ...

400 servers * (1 hour for SQL Updates + 1 hour for Windows Updates) = 800 hours
800 hours / 40 hours per man week = 20 man weeks
So with a team of 5 people, taking exactly 2 hours per server and encountering absolutely no issues you can do it.

However, just because they want it done in a specific amount of time and it is mathematically possible does not mean that it is in fact do-able, or more importantly from a DBA perspective, the right thing to do.

One final note - I think the management goals need to be realigned a bit since you are still running very old releases of SQL Server (v6.5 & v7.0).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Each server must have a DBA or a System Admin or Application Admin, the person responsible for a particluar server and what is running on it.

Contact key people for each machine and point them to the network share where the TESTED patch is stored. By Tested I mean tested with several technologies like ASP, ASP.NET, CRM and other applications. Also post detailed instructions.

Let application support to notify users, select the appropriate time and install the updates.

In your email tell them to contact you if they feel they don't have sufficient expertise.

Yelena

Regards,Yelena Varsha

jasonhatfield01 Right there with Babe Points: 722 More actions · Answer 6

I agree, don't automate this. Although tempting, it will create more problems than it will solve. The issues that have been raised are all legitimate and need to be considered. One thing that hasn't come up yet is individual application testing. What happens when you apply a new service pack and the application(s) that connect to that server stop working?

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 7

I see that Microsoft is saying that SQL Server 2000 post sp4 patches will be available from Microsoft Update or WSUS, so maybe you should talk to your OS patch folks about SQL Server.

Andy