1

I have a 2 node windows 2012 cluster and SQL Server 2012 installed on both. In this I have added a database on nodeA as primary and one on nodeB as secondary. I want to add more 2 databases which will utilize more CPU. So I was thinking I should add one more primary node nodeC which will have nodeB as secondary and have one listener for all.

Is this possible?

  • No. The listener can only have one active node, the other nodes can be readable secondaries or additional spares. If the databases need to talk to each other (cross database queries) then they should be on the same listener and you need to expand resources. However, if they are separate and you just want to possibly over utilize your DR nodes, then you can add an instance to the existing SQL Servers and then have them cross failover. – Jonathan Fite Jun 20 '18 at 12:28
0

The architecture of an availability group is that there is one primary node that owns all databases in the availability group. A listener has an IP address, and it would be impossible to have that IP address online on multiple nodes at once.

The only way you can use another node (NodeC) as a primary while the current node (NodeA) is also a primary is to create an additional availability group. You could set up this new availability group so that only NodeC and NodeB are server replicas, or, you could also include NodeA as a replica as well if desired.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.