SP4 upgrade sql 2K cluster Active/passive - need Advice.

Question

SP4 upgrade sql 2K cluster Active/passive - need Advice.

VIJAYRANGA KONDAPALLY

SSC-Addicted

Points: 451
More actions
January 22, 2006 at 5:44 pm

#166721

Hi ,
I am planning for sp4 install on sql2k cluster. We do not have a true microsoft cluster since
we use the Geo Cluster software solution as an alternative. Current sql server verion is sql2K-sp3a.
There are 2 publisher dbs on the server. Distribution db also resides on the same machine(I know it is a bad idea but
saving dollars is the reason we r using geo cluster software solution instead of true Microsoft certified solution).
The reason we r installing sp4 is to fix the "UMS scheduler hang" issue .
In case of any issues with sp4 during the install , i aim to use the roll back plan specified below.
I am hoping this is the fastest and cleanest possible roll back method.
1.Stop the sql server using EM prior to sp4 Install.
2.Make a copy of all the system and user db files and store them in a separate folder.
3.Start the sql server.
4.Install sp4. If everyting is OK fine else next steps.
5.Uninstall sqlserver using the sqlserver installation CD.
6.Install sqlserver.
7.Apply sp3a.
8.Reboot Nodes.
9.Stop sql server on Node1.
11.Copy the original user and system db (copied to separate folder in step 2) files to
their respective data folders.
12.Start the sql server.
Everything should be fine or will there be any problem.
Need your opinions regarding the plan. I am hoping the above plan will
allow me to avoid the process of restoring the system dbs , issues with replicated dbs and any
unexpected need for reconfiguring replication. Much thanks in advance .

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

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

nunYoBidnez Ten Centuries Points: 1128 More actions · Answer 1

Not sure about Geo Cluster, but I have SQL 2k on a Win 2k3 cluster and installed SP4 without issues. There is a MS kb for installing on a cluster, which is good to follow, the SP4 instructions also tell you how to perform the install. SP4 will stop the sql services, but it is better to control your own destiny. If I remember correctly MS says to:

Pause the node in the cluster, move the groups, install the SP, reboot, then repeat for the other node. If it fails I would first try the uninstall process, because the node is paused for the cluster.

Kindest Regards,

The art of doing mathematics consists in finding that special case which contains all the germs of generality.

Jeff Gray SSChampion Points: 10667 More actions · Answer 2

The plan you have should work fine. Make sure you rebuild to the same patch level you have currently. There are a few post-SP3 hotfixes that may be applied, unless you know otherwise.

To minimize the recovery time if things go wrong, you may want to consider the fact that you are relying on a disk-to-disk copy both to prepare the "snapshot" and to recover it, so:

1. copy the files to a different disk system, on a different array and controller. Otherwise you will have to share the bandwidth of the single system for the simultaneous reads and writes. A different machine, connected via a fast network (GbE) is good.

2. Possibly truncate and shrink the transaction logs before copying the files. This will have a varying degree of benefit, depending on the size of your log files. Make sure that you understand the effects this may have on your systems as the log re-grows afterwards.

good luck!

jg

nunYoBidnez Ten Centuries Points: 1128 More actions · Answer 3

Not to be difficult, but I disagree that your plan will work "as is".

First, you should only stop and start sql services from the cluster not EM or the OS. Second, As Jeff said in 1., use a different disk to backup your stuff. Third, it is never a good idea to install software on an active node. Four, why uninstall sql server when you can in most cases just uninstall the patch (sp4) on the passive node, reboot, the system should be as it was before installing sp4. If this doesn't work, as a last ditch effort uninstall sql server, then re-install with sp3a. It maybe useful to cause a checkpoint, so the system can be restored to the point in time before the sp4 install. Fifth, simply copying the databases back will not work, the master database in particular. Read some of the articles on the web about how to restore a master db. Your plan will cause a lot of problems if you proceed, without getting a clear idea of exactly what you are doing. Make sure you read the entire SP4 installation instructions, there are some programs that cause problems as well, MOM, SMS, these services should be stopped before installation. An once of prevention...

Kindest Regards,

The art of doing mathematics consists in finding that special case which contains all the germs of generality.

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

Actually, if you stop SQL services properly, copy off the MDFs and LDFs for all the databases, then install SQL exactly as it was before, then copy back all of the MDFs and LDFs and start SQL, you will be exactly where you were when you shut it down. No need to recover anything.

I've done it many times.

VIJAYRANGA KONDAPALLY SSC-Addicted Points: 451 More actions · Answer 5

Thank u all for your input. Sincerely appreciate it.I did read sp4 instructions prior to my post. My plan was to find the shortest possible path for sp4 install and possible rollback. Like Jeff mentioned , copying the dbs after installing the sql exactly to the original point has not caused any issues before (of course ,they were non cluster situations). Prior to implementing the copy process , i used to spend considerable time restoring all the sys and user dbs from their backups .

nunYoBusiness ,Quick question. If i stop the sql server from EM , will the cluster try to restart it . Is this the reason why i must use cluster admin. TIA.

The documentaion does not mention installation from active node is problematic. It only mentions stopping non essential services for a clean install. May i know why is it a bad idea to do the install from active node. Thanks again.

nunYoBidnez Ten Centuries Points: 1128 More actions · Answer 6

There are several assumptions I am making relative to your setup. To answer your question: stopping SQL services from EM or the OS signals a failure to the cluster, which fails over to the passive node, starting sql services and moving the groups to the passive node. It is best to go to the cluster admin, look in the group that has sql server and sql agent and take them offline there.

Installing patches on the active node is problematic from the stand point that patchs stop services and updates files that may be in use, which could indicate a failure to the cluster, and initate the failover process. Patches are not necessarily cluster aware, in this case (SP4) being a SQL Server patch is cluster aware. After re-reading the SP4 instructions it does state in section 3.4 to install on the active node, I must have done it this way as well, but it has been awhile, must be getting old. I remember there being a lot of warnings and cautions in the instructions, so I read over the instructions again just for grins. Sections 3.4, 3.5, and 3.8 are important for you. Even when it is painful it is good to learn! Or re-learn.

Here are some links that I have found helpful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;174799 (this is a good procedure when there are not specific directions in a service pack.)

http://www.microsoft.com/downloads/details.aspx?FamilyId=4773BF7E-21AE-4F1E-AD48-6CA739E10217&displaylang=en (hotfix for SP4)

http://download.microsoft.com/download/1/B/D/1BDF5B78-584E-4DE0-B36F-C44E06B0D2A3/ReadmeSql2k32sp4.htm (SP4 instructions: 3.4 Install on a Failover Cluster, 3.8 Un-install)

http://support.microsoft.com/?kbid=811168 (pre-installation checklist)

http://support.microsoft.com/kb/254321/ (do's, don'ts, and basic warnings)

http://support.microsoft.com/kb/243218/ (Installation order for SQL Server 2000)

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx (SQL Server 2000 Backup and Restore - highly recommended reading)

Kindest Regards,

The art of doing mathematics consists in finding that special case which contains all the germs of generality.

VIJAYRANGA KONDAPALLY SSC-Addicted Points: 451 More actions · Answer 7

nunYoBusiness,

Thanks a lot for taking time to explain. As of now , i think i have all the necessary pieces of backup plan to go ahead and install sp4 . Only problem is , i have been reading lot of horror stories about sp4 and it's aftermath. Another round of research for me.