0

I have configured SQL read scale AG (without the WSFC) and listener.

When creating the listener, I have given it a name SQLAGListernerTest2019 and entered the IP address of the primary SQL server.

Then the IT infra personnel have made some config at their end for the listener name to resolve to the primary SQL server IP (I think this is a new DNS entry).

I have forced a manual failover, and on the secondary (which is now the primary) I have dropped the listener and created a new listener with the same name but the IP address of the new primary SQL server.

But when users are connecting to the SQL server via the listener it is still attempting to connect to the original primary SQL server IP. How to fix this? Maybe a DNS config change is required? I couldn't find this in the documentation here: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver16#forced-manual-failover-with-data-loss

1 Answer 1

1

But when users are connecting to the SQL server via the listener it is still attempting to connect to the original primary SQL server IP. How to fix this? Maybe a DNS config change is required?

Read Scale AGs aren't hooked into the WSFC landscape; thus, they have no automatic way of creating or updating DNS - which is what WSFC does. Thus, if the DNS records were created manually, they will need to be updated manually. Drawbacks of not using WSFC.

4
  • Does this answer hold true even for an Listener for an AG (for example: read scale AG) between 2 FCIs? I think yes because although each FCI has their own WSFC, the AG itself isn't in a WSFC. Correct?
    – variable
    Commented Oct 4, 2022 at 18:13
  • The listener would not be a cluster resource, so it'd still be manual, even in your FCI situation. Commented Oct 4, 2022 at 18:30
  • New learning for me that using WSFC and creating AG listener (assuming AG nodes participate in WSFC) auto creates dns mapping for the listener. Thanks.
    – variable
    Commented Oct 4, 2022 at 21:07
  • 1
    @Variable - Creating a listener in an WSFC integrated AG asks the cluster to create a network name resource, which is backed by a computer object in active directory. The network name resource handles DNS CRUD. Commented Oct 4, 2022 at 21:10

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.