SQL Server Always On Availability Group (AG) Listener: step by step guide


Availability databases hosted on SQL Server Always On Availability Group (AG) can be connected using a unique Virtual Network Name (VNN) called Availability Group Listener. When Availability Group is enabled, clients can connect to databases in both primary and secondary replicas without explicitly specifying the SQL Server instance name. You don’t even need to know the instance name to connect to Availability Group (AG).

When you have configured read-only routing for secondary replicas and the application or client connection contains the application intent as read-only, the listener will redirect the connection to secondary readable replicas, Otherwise, the listener redirects both read-write and read-only intent connections to primary replica. If there is a fail-over, the listener will redirect connections to new primary and secondary connections based on the application intent.
Availability group listener consist of following objects:
  • Domain Name Systems (DNS)
  • Listener Port
  • One or more IP addresses (VIP)
The Listener is always owned by the SQL Server instance where the primary replica resides. At the time of failover, the new primary replica will own the listener.

Example:
  • DNS: salesag.mscorp.com
  • Port: 1433
  • IP: static or DHCP

Creating Availability Group Listener:

Availability Group Listener can be created while creating Availability Group or it can be created post creating the AG. The Listener creation options are available on Specify Replicas window and under the Listener tab.

If you want to skip the Listener creation while creating Availability Group, you can leave the default option Do not create an availability group listener now check the below image for reference:

New Availability Group - Specify Replicas
New Availability Group - Specify Replicas


Post creating the Availability Group, you can configure listener on Availability Groups -> Group Name -> Add Listener.

Either on create an availability group listener or New Availability Group Listener wizard, specify the Listener DNS Name, Port, Network Mode values. The following figure shows the example availability group listener from my lab.

Create availability group listener
Create availability group listener

You can also, configure Network Mode as DHCP (Dynamic Host Configuration Protocol). DHCP is limited to single subnet and it is not recommended for production environments. Choose the Static IP option from the drop down, if you want the multi-subnet availability group.

To Add IP address, click on Add button and provide the IP address at IPv4 Address text box.
You can also, configure Network Mode as DHCP (Dynamic Host Configuration Protocol). DHCP is limited to single subnet and it is not recommended for production environments. Choose the Static IP option from the drop down, if you want the multi-subnet availability group.  To Add IP address, click on Add button and provide the IP address at IPv4 Address text box.
Create availability group listener - Add IP Address wizard

Additionally, you can script out the changes to query windows and see T-SQL script. Codes from my demo cluster:

USE [master]
GOALTER AVAILABILITY GROUP [MSCORPAG]
ADD LISTENER N'mscorpag' (WITH IP((N'192.168.0.7', N'255.255.252.0')), PORT=1433);GO

You can configure port 1433, default SQL Server instance port for availability group listener. In that case, you don’t have to specify the port number explicitly on connection string or client connection. If you specify custom port, you have to explicitly specify it. If you have more than one SQL Server instances on the machine, I recommend you to use different port, because, the listener may be configured to different instance which is listening on different port.

Connecting Availability Group Listener:

To connect availability databases through availability group listener, specify the listener name on server name box as below:

Connecting to availability group listener in SSMS
Connecting to availability group listener in SSMS

I am using default port number, if you are using different port number, specify explicitly on the connection string. Example connection string with different port number provided below:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=msdb;user=rathish;password=pwd";

Listener and user permissions:

When you create an availability group listener, the cluster will create a computer object on domain controller and assign its computer name as virtual network name automatically, to perform this, the cluster should have Create Computer Object permission on your Active Directory.

Additionally, I have created availability group listener with the help of Domain Administrator by following below method.

Create a computer object on Active Directory and assign listener name as computer name to that object. The cluster (name of the cluster which own the availability group) will have the full control on the newly created computer object. Add an entry at Domain Name Systems (DNS) against that computer name with a static IP address. Now create listener on availability group. In troubleshooting section of this article, we look into the common error scenarios with listener creation permission.

On SQL Server, you need sysadmin privilege to create the availability group listener and at least ALTER AVAILABILITY GROUP permission to modify the listener.

Read-Only Routing and availability group listener:

In SQL Server Always On Availability Group (AG), read-only routing features provides the scalability by redirecting read-only connections (SELECT queries) to readable secondary replicas. Routing of connection to secondary replicas works, only when the applications or clients connecting availability databases through availability group listener. If you are connecting directly to SQL Server instance name, at the time of failover, connections will not automatically failover to new primary server.

As stated earlier, listener will redirect connections to both primary and secondary replicas. If you have configured any of the secondary replicas as readable and specify the application intent as read only, the listener will redirect connections secondary readable replicas based on the read-only routing configuration. Refer my previous article How toconfigure Read-Only routing on SQL Server Always On Availability Group for detailed explanation. Example connection string with Application Intent and default database given below:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly";
You must specify the ApplicationIntent and Default database on connection string for read-only routing to work, otherwise the connection will be redirected to primary replica only, later in this article, we will see the troubleshooting steps for this scenario.

Availability Group Listener and multi-subnet failover:

You should set the MultiSubnetFailover option as True, enabling this option provides faster failover when your availability group spans over multiple subnets. It is recommended to set this option as True, even, if the availability group only spans a single subnet. This provides additional optimisation, even for single subnets ate the time of failover.

Example connection string with multi-subnet failover option:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly;MultiSubnetFailover=True"; 

Monitoring Availability Group Listener:

SQL Server provides following catalog views to monitor availability group listener properties:

sys.availability_group_listener_ip_addresses: returns the virtual IP address of availability group listeners
sys.availability_group_listeners: returns the network name of availability group listeners
sys.dm_tcp_listener_states: returns the status of TCP IP address and port of availability group listener. 

Removing Availability Group Listener:

You can remove availability group listener, either using SSMS or using T-SQL. In SSMS, right-click on listener name and select the Delete options to remove the availability group listener.

Using T-SQL:

ALTER AVAILABILITY GROUP MSCORPAG REMOVE LISTENER 'mscorpag.mscorp.net';

Troubleshooting availability group listener issues:

Scenario 1: Unable to create availability group listener:

Error: Microsoft SQL Server, Error: 19457

Message:

The specified IP Address '' is not valid in the cluster-allowed IP range. Check with the network administrator to select values that are appropriate for the cluster-allowed IP range. (Microsoft SQL Server, Error: 19457)
Msg 19471, Level 16, State 0, Line 2The WSFC cluster could not bring the Network Name resource with DNS name '' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
Msg 19476, Level 16, State 4, Line 2The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.
Possible Causes:

Cause 1: The cluster name account does not have the Create Computer Objects in Active Directory Organizational Unit.

Solution: Grant Create Computer Object to Cluster account name.

 
How to grant Create Computer Objects to Cluster name account:

Select the Advanced Feature option by View -> Advanced Features:
Troubleshooting Availability Group Listener - View Advanced Feature on AD
Troubleshooting Availability Group Listener - View Advanced Feature on AD

Go to Advanced Security Settings for Organisational Unit (OU): Follow the highlighted part as below image:

Advanced Security Settings of Organizational Unit
Advanced Security Settings of Organizational Unit

Select the Create Computer Objects from permission tab:
Create Computer Objects permission to cluster name account
Create Computer Objects permission to cluster name account

Cause 2: The cluster user account, which has Account Operator permission on Active Directory by default can create up to 10 computer objects, and it is exceeds its limit now.

Solution: If your organisation polity allowed, grant Create Computer Object permission to cluster account, or prestage the required objects on the Active Directory, Organizational Unit (OU).

How to prestage computer objects for availability group listener:

When you create an availability group listener, a virtual computer object will be created at the particular organizational unit of active directory automatically.

You can prestage the virtual computer objects as follows:

Create a computer objects under the organizational unit where you hosted the cluster, check with your domain administrator, if you don’t have access to create objects on Active Directory Domain Controller

Add virtual computer objects on Active Directory for Availability Group listener
Add virtual computer objects on Active Directory for Availability Group listener

Grant Full control permission to cluster name account on newly created computer object. Note that, this name should be same as the listener name, you will be creating on availability group.

Full control permission on virtual computer object to cluster name account
Full control permission on virtual computer object to cluster name account

Scenario 2: Read-Only routing not working when connecting to listener

Error: ApplicationIntent=ReadOnly specified on application connection string and read-only routing configured on the availability group, but when connecting to listener, it is not redirecting to secondary readable replicas.

Possible Cause: You have not mentioned the default database on the connection string. For more information on this behaviour can be found here.

Solution: Add Initial Catalog value in connection string.

Example SSMS connection string:
Read-Only Routing Connection parameters on SSMS
Read-Only Routing Connection parameters on SSMS

Scenario 3: Availability Group Listener - login time-out error

Error: You are unable to connect to availability group listener in a multi-subnet environment. This error usually occurs at the time of failover.

Possible Causes:

Cause 1: Your application uses legacy data provider that does not support the multi-subnet failover features.

Solution: Use the newer version of SQLClient drivers that supports multi-subnet features.

Cause 2: MultiSubnetFailover parameters are not used on the connection string.

Solution: Include MultiSubnetFailover=True parameter on connection string to fix this issue.

Example:

connUrl = "jdbc:sqlserver://mscorpag.mscorp.com:3306;databaseName=mscorp;user=rathish;password=pwd;ApplicationIntent=ReadOnly;MultiSubnetFailover=True";

Scenario 3: Availability Group Listener name not resolving to IP address

Error: After failover in a multi-subnet environment, ping command from client not resolving to new IP address of the listener and DNS entry of the listener name shows IPs of both subnets.

Possible Causes:

This error generally occurs, when listener is created using Failover Cluster Manager, rather than from SSMS.

Solution: Set the value of RegisterAllProvidersIP value as 0. This require restart of listener network name resource.

Example:

PowerShell:

Import-Module FailoverClusters
Get-ClusterResource dbcluster |Set-ClusterParameter
RegisterAllProvidersIP 0
Cluster.exe:

cluster /cluster: dbcluster res mscorpag /priv
RegisterAllProvidersIP=0
If still ping to listener returning wrong IP address, from application/client system, open command prompt as administrator and run the ipconfig /flushdns command.

Scenario 4:  Server cannot host the availability group listener IP address:

Error: Error 19456, Severity 16:

Message:

Error 19456, Severity 16: None of the IP addresses configured for the availability group listener can be hosted by the server '%.*ls'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can be hosted.
Solution: Add a new IP address manually to existing listener with different subnet.

Scenario 5: Active Directory Policy

Error: Error 8557

Message:

Error 8557 (Your computer could not be joined to the domain. You have exceeded the maximum number of computer accounts you are allowed to create in this domain. Contact your system administrator to have this limit reset or increased.)
A computer object is created automatically, whenever you create an availability group listener. Even if you drop the listener, the computer object remains there in Active Directory. By default, authenticated domain users can create up to 10 computer objects, when this limit exceeds, it will through an error at the time, availability group listener creation.

Solution: You may contact domain admin to clean up this computer objects or ask them to increase the default limit (I think it is a global variable).

Best practices:

  • Use static IP address for reliable connections and multi subnet failover
  • Use meaningful and unique listener name for each availability groups
  • Test it on staging server, before implementing it on production environment
  • Additionally, to avoid the accidental deletion on cluster objects, enable the Protect objects from accidental deletion option for Organizational Unit (OU).
Protect object from accidental deletion
Protect object from accidental deletion

I hope this article is helpful to you, if you have any queries or want to share your thoughts on availability group listener, please write it in comment section. Thank you!

38 comments:

  1. QuickBooks is the Best grade accounting software designed for small and medium businesses. It can be utilized to become the overall performance and comfort of doing business by streamline and automating sustained tasks.

    quickbooks support

    ReplyDelete
  2. Thanks for sharing information. Choosing computer accessories from leading IT store offer great discount and value for your money Computer Store Australia | All in One Pc Australia

    ReplyDelete
  3. Dedicated hosting is really a beneficial form of web hosting that come with enormous advantage to the master of the website. ark server hosting

    ReplyDelete
  4. Nice Blog!
    QuickBooks provide 24*7 tech Support to resolve any QuickBooks issues and problem.
    Dial our Quickbooks Customer Support +1-844-908-0801

    ReplyDelete
  5. Our QuickBooks Support experts are available 24/7 to provide reliable solutions for resolving your issues without any hassle. You have to dial our toll-free helpline number: +1-818-492-9977
    https://www.xactons.com/quickbooks-phone-number/

    ReplyDelete
  6. QuickBooks support number +1-877-343-9333 by certified experts, Oregon USA QuickBooks is the most desired software when it involves handling one’s finances and managing it. This software has equipped most QuickBooks users with such a lot of strength that they feel accomplished. QuickBooks support number or Online online is one such software that has instilled the right sense of using your money.
    https://www.cloudnetworkusa.com/quickbooks-support/

    ReplyDelete
  7. Having problems while using the QB accounting software, then don't get wooried, our highly-skilled QuickBooks Support experts will fix this issue in a few minutes.

    ReplyDelete
  8. Are you facing QuickBooks issues? Call on our QuickBooks Support Number +1-888-885-7555 and settle down all your issues under the supervision of our technical support team. We are available 24/7 to assist you. All services are available all over the USA.

    ReplyDelete
  9. QuickBooks automated standard accounting tasks, such as entering transactions, performing calculations and organizing data to keep your books balanced. QuickBooks imports your bank transactions and organizes them. If your QuickBooks does not work, you can request and call QuickBooks Support Phone Number +1-888-308-0575
    http://youraccuracy.com/quickbooks-support/

    ReplyDelete
  10. Read more about Snowplow Parenting: A Controversial Parenting Style

    ReplyDelete
  11. Very helpful content!
    Get QuickBooks Support from our expert for any glitch and error regarding QuickBooks.
    Dial Quickbooks Support Phone Number +1-844-908-0801.

    ReplyDelete
  12. Nice Blog!
    If you have any sort of problem in QuickBooks. Give a call on QuickBooks Customer Service Phone Number 1-855-6OO-4O6O.Our technical experts are available 24/7 to give assistance.
    View on Map:QuickBooks Customer Service

    ReplyDelete
  13. I hve experience YoWhatsapp is one of finest version. must try

    ReplyDelete
  14. Worried about Direct deposit in Quickbooks ? Get in touch with QuickBooks expert for instant solution.
    Click here to Know how to do Direct deposit in Quickbooks
    Dial for fix QuickBooks error or support on QuickBooks toll-free Number 1-844-908-0801

    ReplyDelete
  15. Worried about QuickBooks install error 1603 ? get in touch with QuickBooks expert for instant solution.
    Click here to Know how to fix QuickBooks install error 1603
    Dial for fix QuickBooks error or support on QuickBooks toll-free Number 1-844-908-0801

    ReplyDelete
  16. Well explained Informative Blog !
    If you find any of the problems that relates to your software functioning. Place a call to our QuickBooks experts anytime by dialling QuickBooks Premier Support Phone Number 1-855-6OO-4O6O.

    ReplyDelete
  17. Nice Blog !
    Facing any issues in QuickBooks? Give us a ring on QuickBooks Customer Service Phone Number 1-855-511-6911.Find solution for all your error code trouble. It doesn’t matter how complex the error would be. Let our experts handle the issue with thorough consultancy

    ReplyDelete
  18. If not, then dial QuickBooks Customer Service Number in Oregon +1-844-442-1522 and get the best technical assistance from accredited professionals.

    ReplyDelete

  19. Hey! Nice post. It is one of the best things that I have read on the internet today. To fix QuickBooks issues, dial, QuickBooks Phone Number in Arizona +1-844-442-1522 and get instant technical assistance from experts.

    ReplyDelete
  20. The Kansas City Chiefs are a Chiefs Game Today professional NFL team based in Kansas City, Missouri. You can watch Kansas City Chiefs game live streaming online. Get the latest Kansas City Chiefs schedule, TV channel, news update here.Chiefs NFL Game Live
    Chiefs Football Live
    Live Chiefs Game
    Kansas City Chiefs Game Live

    ReplyDelete
  21. You have touched good quality points here. In whatever way continue writing.
    branding firms

    ReplyDelete
  22. It was not first article by this author as I always found him as a talented author. Tony Soprano Leather Jacket

    ReplyDelete
  23. Are you looking for instant support while accessing QuickBooks? Don’t worry!! Simply call on our QuickBooks POS Support Phone Number +1(844)233-3033, and get abstained from all your worries.
    https://tinyurl.com/yy33sxqb

    ReplyDelete
  24. Are you facing difficulty while working on QuickBooks Payroll? Don’t get troubled!! You have approached the right place!! By just reaching out to our QuickBooks Payroll Support Phone Number +1(844)233-3033, you can refrain from all your worries.
    https://tinyurl.com/yy33sxqb

    ReplyDelete
  25. You can directly call on our Quickbooks Upgrade Support Phone Number +1(844)233-3033, and get benefited with instantaneous help so as to fix all your QuickBooks Pro concerning issues

    ReplyDelete
  26. I want to thank Dr Emu a very powerful spell caster who help me to bring my husband back to me, few month ago i have a serious problem with my husband, to the extend that he left the house, and he started dating another woman and he stayed with the woman, i tried all i can to bring him back, but all my effort was useless until the day my friend came to my house and i told her every thing that had happened between me and my husband, then she told me of a powerful spell caster who help her when she was in the same problem I then contact Dr Emu and told him every thing and he told me not to worry my self again that my husband will come back to me after he has cast a spell on him, i thought it was a joke, after he had finish casting the spell, he told me that he had just finish casting the spell, to my greatest surprise within 48 hours, my husband really came back begging me to forgive him, if you need his help you can contact him with via email: Emutemple@gmail.com or add him up on his whatsapp +2347012841542 is willing to help any body that need his help.

    ReplyDelete
  27. The team’s response time is commendable UX San Francisco, and its collaborative nature makes for an agreeable engagement.

    ReplyDelete
  28. Our the purpose is to share the reviews about the latest Jackets,Coats and Vests also share the related Movies,Gaming, Casual,Faux Leather and Leather materials available Smokey And The Bandit Jacket

    ReplyDelete
  29. Do you need help with QuickBooks POS issues? So get connected with our experienced and certified experts at QuickBooks POS Support Phone Number +1(844)233-3O33 and find answers to all your queries affiliated to the software. Our team of experts assist you the way you want the moment you want.
    https://tinyurl.com/y55f2f95
    https://tinyurl.com/y4dsctnb
    https://tinyurl.com/yywtgfz9
    https://tinyurl.com/y2znqk2t
    https://tinyurl.com/y4lu77ul

    ReplyDelete
  30. Due to the occurrence of issues in QuickBooks from now and then you are facing lots of drawbacks, right? Not anymore!! As we are here for you, just make a call at QuickBooks Support Phone Number USA +1(844)233-3033 and make a way out of such a situation easily with the help of experts
    https://tinyurl.com/y4xllh8h
    https://tinyurl.com/y5a827z6
    https://tinyurl.com/y63hfxhu
    https://tinyurl.com/y2s2shud
    https://tinyurl.com/y6e3g3a4
    https://tinyurl.com/yyzr98ts

    ReplyDelete
  31. Omg I Finally Got Helped  !! I'm so excited right now, I just have to share my testimony on this Forum.. The feeling of being loved takes away so much burden from our shoulders. I had all this but I made a big mistake when I cheated on my wife with another woman  and my wife left me for over 4 months after she found out..  I was lonely, sad and devastated. Luckily I was directed to a very powerful spell caster Dr Emu who helped me cast a spell of reconciliation on our Relationship and he brought back my wife and now she loves me far more than ever.. I'm so happy with life now. Thank you so much Dr Emu, kindly Contact  Dr Emu Today and get any kind of help you want.. Via Email emutemple@gmail.com or Call/WhatsApp +2347012841542

    ReplyDelete
  32. I was surfing net and fortunately came across this site and found very interesting stuff here. Its really fun to read. I enjoyed a lot. Thanks for sharing this wonderful information Jual Elementor Pro

    ReplyDelete
  33. With 24/7 availability and experience, our team can help you out any time and with anything affiliated with QuickBooks. So when you stuck with any of the issues don’t keep yourself entangled in it reach us at Access QuickBooks Payroll Support +1-855-533-6333 to get instant and effective solutions and eliminate the problem at the root level. Call us now!
    Quickbooks Enterprise Support Phone Number +1-855-533-6333
    Quickbooks Support Phone Number +1-855-533-6333

    ReplyDelete
  34. If you need help with errors in QuickBooks accounting software. Then make sure to dial the QuickBooks Payroll Support +1-855-533-6333 to access the most favorable and customized support service. This number empower by our certified, well trained, and highly qualified QB experts who carry years of experience under their belt of tackling numerous technical and non-technical glitches of the software.Call us now!
    Quickbooks Enterprise Support Phone Number +1-855-533-6333
    Quickbooks Support Phone Number +1-855-533-6333

    ReplyDelete