New SQL 2000 cluster failover problem 2

[ITsmyfault]

Hi -
Built a Win2k cluster using HP Netserver LPR's, an RS/12 storage array and Netraid 4M RAID controllers. Followed HP's "how to" doc to the letter. Cluster runs and the cluster group fails over and back fine.
Then I installed SQL2k and went to SP3 on it. The SQL service fails on failover and I am not sure why. The volume, the IP, the name all fail over but the SQL service, full text and the agent don't. After what looks like 3 attempts MSCS says "whatever" and fails SQL back to cluster 1 where it is fine again.
Been reading the cluster.log file but it's a little verbose!! It's a step in the right direction from the usual MSFT "service X didn't load" message but I can't really even tell where the error is...
Also not sure if this is a cluster group post or a sql group post, so a thousand pardons if I am in the wrong spot. New to sql!
Thanks for any pointers! Will post solution when I find it.

TIA,

Joe

 

[sguslan]

Do you have a private network card[heart beat] to support the cluster as well as another for public? If you do make sure to check that the public card is listed first on the ControlPanel->Network Connections->Advanced->Advanced Settings. ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[baddos]

I beleive you are having the sql agent setup to connect to node1 instead of the virtual server. Verify that it is setup to connect to the virtual server's name or IP address and not node1's.

-Bad Dos

 

[ITsmyfault]

Hi -

1 - thanks!
2 - using both a private IP heartbeat x-over cable as well as using the LAN nic for backup. Heartbeat nic comes first.

3 - Bad Dos - where is that setting on the sql agent? The closest setting seems to be "possible owners" in the "properties" tab of the agent in the cluster admin tool. Could not find anything in Ent Admin tool for SQL.. Do I need to check the registry for this? Possible owners lists "C1" and "C2" - my servers.. which seems right.

 

[SQLBill]

When you installed SQL Server, where did you install it at? It should be installed on BOTH nodes. You should have installed it on the primary node and then failed over and installed it on the second node.

-SQLBill

 

[baddos]

Open up Enterprise Manager.

Open up your vritual server, and then click on the management group.

Open up the properties of the sql agent.

On the connection tab, make sure it point to the clustered IP address for your SQL virtual server, and not the ip for node1.

-Bad Dos

 

[ITsmyfault]

Hi -
I did look there, but under "SQL Server Alias" where it says Local Host Server it is grayed out and has (default) in the box.. I take it that's a bad thing.. should I just reinstall?


re: the install - on SQL7 I *think* installing on both boxes was the proceedure but with 2k it installs to both automatically, at least if I read the directions right. SQL service is up and running on cluster 2 as well as one and I sure didn't put it there.
FWIW I was reading through this on the install:

http://www.sql-server-performance.com/sqlserver2000_clustering_install_main.asp

Thanks! - Joe

 

[SQLBill]

Yes, with SQL 2000 it does install automatically to both. But we had to reboot the primary node so that SQL Server failed over to the secondary node. Then when the primary came up, we rebooted the secondary node (which failed it back over to the primary node). Most installs of software require a reboot and SQL Server 2000 didn't work until we had rebooted both nodes.

-SQLBill

 

[baddos]

You shouldn't need to reinstall.

Try editing the registry on node2, and seeing this this helps.

Backup the registry first!

Goto this registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer$VirtualServerName\SQLServerAgent

Edit the "ServerHost" key to reflect the network IP of your Virtual Server.

-Bad Dos

 

[ITsmyfault]

OK.. good news.
Did reboots which probably helped but weren't the major issue (see below..), I left Cluster 1 off for a spell.. which got me to really look at Cluster 2 since it would not run SQL service.. Sure enough that registry entry was blank.. so I filled it in. But I noticed the sql service was still not starting and apparently couldn't find the DB.. so I looked at the cluster admin and all looked well.. then I looked at the drives - and the cluster drive no longer had a letter (visible in drive admin, but not "my computer".. so I gave it the correct letter, rebooted (for good luck) and now everything is fine fine fine. ! It's the little things..
I have failed SQL over and back a few times already and it seems very happy.

Thanks!!