SQL Cluster: SQL Listener question between Primary and DR sites?

Duchemin, Dominique 2,006 Reputation points
2023-11-04T00:28:40.9866667+00:00

Hello,

I have a SQL Cluster with two Nodes:

-          Primary: Node P

-          Disaster Recovery: Node D

 

1.       How does the SQL listener should work between the Primary Site (P) and the DR Site (D)?

 1.a. Should it work across sites? P to D and also D to P…

Or

1.b. Should we do a DNS change for the A Record and work ONLY after a failover including the SQL Listener? Meaning the Listener point to a A Record on P Site when the Active Node is on P Site and the A Record pint to a A Record on D Site when the Active Node is on D Site.

Or

1.c. Should we do a DNS change and have two A Records, one for P and one for D which will be available anytime?

 

2.       If working across site this is preventing to know which site is used by the SQL listener after a failover especially in an exercise…

 2.a Should we have the SQL Listener from P Site working with servers in D Site?

And

2.b Should we have the SQL Listener from D Site working with servers in P site?

  

Currently we have only one IP (P) working, it is the SQL Listener for the Primary Site… and we will need to use 1b to get the Listener on the DR site…

PLISTENER is a “A Record” which is currently pointing to P Site IPs as the Active site P (Primary) is NodeP with SQL Server on SQLNodeP.

PLISTENER the same “A Record” will be pointing to D Site IPs but it is not set for now (the record does not exist as A Record, only the PTR exists!!!) with NodeD with SQL Server on SQL NodeD.

 

Thanks,

Dom

 

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
{count} votes

Accepted answer
  1. Dan Guzman 9,236 Reputation points
    2023-11-06T23:19:13.5366667+00:00

    Do you have a multi-subnet failover AG or a distributed AG? SQL Server and WSFC can take care of updating DNS automatically during failovers.

    In the case of a multi-subnet AG (which supports automatic failover when replica is synchronous), both listener IP addresses are automatically registered in DNS if the RegisterAllProvidersIP property is set to1 (the default). No DNS changes during failovers are needed since both IP addresses are registered. Clients also need to specify MultiSubnetFailover=true in the connection string to avoid connection latency.

    If RegisterAllProvidersIP=0, then only the active primary listener IP address is registered in DNS. WSFC will automatically update DNS to the IP address to the active primary node during failover. This is needed if you have legacy client drivers that don't support the MultiSubnetFailover connection string keyword.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Bjoern Peters 8,876 Reputation points
    2023-11-06T16:52:10.0866667+00:00

    Welcome to the Q&A Forum; this is a great place to get support, answers, and tips.

    Thank you for posting your question; I'll be more than glad to help you out.

    I don't understand your environment, and please explain a little bit deeper.

    What kind of environment do you have? A failover cluster + a DR Standalone, a Standalone with Replication/Log-Shipping, or an Availability-Group Cluster with 2 or 3 nodes (+ DR node?)

    What do you want to achieve in what case... "just" a DR scenario?

    With any automation or all manual?

    Is that DR node just a DR node (nothing else!), or is it just another Standalone server that keeps a "replicate" of your production databases?

    Based on my understanding of your scenario, my stomach and my experience... I would suggest that a DNS change would be the easiest and most elegant method to move an application from server a to server b.

    I hope my answer is helpful to you,

    Your

    Bjoern Peters

    If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.