3 node clustering in sql server 2008 R2

Question

3 node clustering in sql server 2008 R2

Viewing 15 posts - 16 through 30 (of 56 total)

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

Elliott Whitlow (2/21/2012)
I think you need to understand the cluster quorum settings because for a three node cluster the only way to allow a 2 of 3 failure is the No Majority Quorum setting.

Not sure who it was aimed at but I dont have any involvement in windows anymore so I dont have to. Only at a high level. Although I do remember some of what I implemented 3 years ago at previous company. Worth bearing in mind that a lot of companies segregate these teams as they grow.

Very educational though.

Elliott Whitlow (2/22/2012)
Isn't that what I said? 😛
CEWII

Perry Whittle (2/22/2012)

1. Node Majority
designed for an odd number of nodes, can sustain the failure of 1 node in a 3 node cluster or 2 nodes in a 5 node cluster, etc.

Which is why I suggested it since its a 3 node cluster. A failover can occur without the loss of both nodes so its still good practice to have the standby node specc-ed to take both.

Pyman SSChampion Points: 13830 More actions · Answer 2

Thanks for all the inputs. Right now I have an issue:

Here is what I did.

Node A Installed sql server as New sql server failover cluster installation

Node B Installing SQL Server as New SQL Server failover Cluster Installation (this is failing)

The error I get when I am installing is "The resource should not exist for MS SQL Server". Once this is complete I can add a node on Node c for Node B.

Node C Added a node for an existing cluster (for Node A)

Please let me know if I am doing it right?

Thanks a ton for all your help.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Pyman SSChampion Points: 13830 More actions · Answer 3

I get the error when I try to configure cluster resource group.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Are you trying to use the same group for the new instance on node b that is already in use for the instance on node a

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Pyman SSChampion Points: 13830 More actions · Answer 5

I could get rid of this issue but when I failover both the Nodes to Node C I am not able to connect to SQL Server from SSMS. I am able to see that the Node C is the owner of Quorum now as well as both the SQL Server services running on Node C.

The errors I am getting are:

Cannot connect to SQLCLUSTERNODE1.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Cannot connect to SQLCLUSTERNODE2\TESTFORNODE2.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I am able to connect to sql server on Node A and Node B when the services are running on their respective nodes, I meant if there is no failover. I am not sure if this is related to quorum.

Please advice.

Thanks for all your help again.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

What is the mode you are using? Node Majority?

How many nodes are up?

Do the SQL Services show as running?

CEWII

Pyman SSChampion Points: 13830 More actions · Answer 7

What is the mode you are using? Node Majority?
How many nodes are up?
Do the SQL Services show as running?
CEWII

I am using No Majority (can sustain failures of all nodes except 1)

Both Node A and Node B are down (I pulled out the network cable) and Node C (the passive node) is UP and I see SQL Services from Node A and Node B are running on Node C and also Node C is now the owner of Quorum disk.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Elliott Whitlow

SSC Guru

Points: 102402

February 23, 2012 at 9:12 am

#1450942

Is the SQL Browser running?

CEWII

Pyman SSChampion Points: 13830 More actions · Answer 9

Yes I do see the SQL Browser running on the Node C.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Pyman SSChampion Points: 13830 More actions · Answer 10

Also I see is the CLuster Service running on Node C. Is this required too?

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

Without the Browser service the named instance is unlikely to connect.

Your cluster manager should look something like this:

CEWII

Pyman SSChampion Points: 13830 More actions · Answer 12

Yes I have the browser service running. Also, your screenshot matches mine except that I have 2 of those (one for each sql).

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Pyman SSChampion Points: 13830 More actions · Answer 13

Please find attached screen of my cluster manager.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 14

Ok, as I read that you have a default instance and a named instance. And that looks fine to me. I have two as well but am only showing one.

I'd look in SQL Server Configuration Manager at the item SQL Server Network Configuration, then Protocols for ....

I would expect to see 3 enabled, Shared Memory, TCP/IP, and named pipes. This is usually synchronized in the background by cluster services with registry replication.

Have you looked at the errorlog of the 2 SQL servers to see that the appear to be good?

CEWII

Pyman SSChampion Points: 13830 More actions · Answer 15

Ok, as I read that you have a default instance and a named instance. And that looks fine to me. I have two as well but am only showing one.
I'd look in SQL Server Configuration Manager at the item SQL Server Network Configuration, then Protocols for ....
I would expect to see 3 enabled, Shared Memory, TCP/IP, and named pipes. This is usually synchronized in the background by cluster services with registry replication.
Have you looked at the errorlog of the 2 SQL servers to see that the appear to be good?
CEWII

Yes. I have already checked the protocols and the three are enabled and I dont see any errors or warnings in my error log. I wonder if it has got anything to do with network settings. I have also verified if SQL allows remote connection and the Node C has allow remote connections to this computer enabled. And also able to ping to node C and telnet 1433 from the client to Node c and the port is in listening mode.

Whats strange is I am able to launch SSMS and connect to both instances from Node C after failover. And from my laptop i am able to connect to instances on node a and node b before failover through SSMS. But I get above errors when I try to connect from ssms after failover.

“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams