0

Looking to build a 4-node (servers A, B, C & D) AlwaysOn Windows 2016 failover cluster with 2 nodes in the primary datacenter for HA, and 2 other replica nodes in the DR datacenter for disaster/reporting purposes.

Since this is a multisite cluster, is there a way to configure to have two listeners (one for the Production/primary instance, the other for the DR instance) under the one AlwaysOn Availability Group.

Also, would SQL on all four nodes need to run under the same service account? Or can servers A, B be on one account while servers C, D run under another account.

Is this even feasible/possible? Thank you in advance for your help!! Hope everyone is safe & well during this time.

Improve this question

2 Answers 2

Reset to default
1

is there a way to configure to have two listeners

Sort of, but it's unusual. The more normal practice is to have a single AG Listener, with read-only routing configured. That way everyone connects to the same hostname, but read-only workloads will disconnect and reconnect directly to an available readable replica.

would SQL on all four nodes need to run under the same service account?

It's recommended, but not required, to use a single Group Managed Service Account for all the nodes. If the instances have different service accounts, Kerberos won't work when connecting to the AG Listener.

Improve this answer
2
  • Thanks David for the detailed response. I know you said it's unusual to have two listeners in the same AG; however, if it is insisted to be done, how do I do it? Basically, would like AG_Listener1 point to servers A & B with synchronous commit and auto failover. AG_Listener2 point to servers C & D with async commit (manual failover) between them and A & B. Did I just make things more convoluted? Hope not.
    – Chinesinho
    Commented May 15, 2020 at 3:28
  • It would depend on what the goal of having multiple listeners is. In the normal configuration the listeners always point to the primary. See learn.microsoft.com/en-us/sql/database-engine/…
    – David Browne - Microsoft
    Commented May 16, 2020 at 16:30
0

I am using Windows Server 2019 and SQL Server 2019 but these instruction should be about the same in other versions.

Configure multiple listeners for an availability group in Windows Failover Cluster Manager. Go to Cluster > Roles > Add Resource > Client Access Point and add the new listener. Next, while still in Failover Cluster Manager, bring the new listener online. After that, while still in Failover Cluster Manager, assign it to the AG. Now you are ready to go into SSMS. Open Object Explorer and connect to the primary node. Go to Always On High Availability > Availability Groups > [Availability Group Name] > Availability Group Listeners. You will see the new second listener here. Right click on it and choose properties and add the SQL port number in the pop up.

Here is an article I found helpful: https://techcommunity.microsoft.com/t5/sql-server/how-to-create-multiple-listeners-for-same-availability-group/ba-p/384028

Here is a YouTube video I found helpful: https://www.youtube.com/watch?v=wweCJ4netQk

If what you really want is a distributed availability group, distributed availability groups are described in this document: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15

Configuring multiple listeners for distributed availability groups are described in this document: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver15

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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