Cluster services gives the high availability needed - that is great. But I
have never seen any discussion about what happens when a node fails - what do
you do to get everything back to the active-passive tandem.

I imagine there is not much difference in terms of recovery procedure for
either active or passive node. So I'm just going to make up a scenario that
we have encountered. The system hard drive (not the shared disk) on primary
node fails. Cluster fails over to the passive node. Following are the
problems I have at hand:
-After installing windows, I need to install driver and configure the
permission to access the SAN. There is no way I could do it since the
secondary node has exclusive access to the disks.
-Imagine I got that working, is there anyway to install SQL so SQL would
know this server used to be the primary node and attach the DB and translog
automatically
-Finally, there is no proper way to apply SQL 2000 service pack 3a.
Originally when the cluster was fully functional, the service pack was
applied to active node and that automatically upgrades passive node. Now we
have a machine without 3a and a machine with 3a already installed. See any
problem?

Consider all of the above as this one big question: What is a proper
procedure to restore a cluster when one of the node goes down? Whether it's
the active or passive node.

Once the hardware and Windows Cluster is reinstalled, you need to re-run
the SQL Server setup, select Advanced Options, then Maintain a Failover
Cluster. Here you can select to "add" the restored node back as a failover
SQL Server node. Search the SQL Books Online for "cluster node recover".

You can also run SQL SP3a on the passive node after adding it back using
step above. Since SQL is not running on this node only the local binaries
will be updated.

Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.

This posting is provided "as is" with no warranties and confers no rights.

Just as a side note, I have tested this procedure and it works perfectly.
You can even use it to do a rolling hardware upgrade on your cluster hosts
by 'failing' one at a time and replacing it.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"Chris Skorlinski (MS)" <ChrisSkor (AT) Online (DOT) Microsoft.com> wrote