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.
Related articles in Always On Availability Group:
Always On Availability Group Read-Only Routing
Windows Server Failover Cluster Quorum configuration for SQL Server Always On
Adding SSISDB to Always On Availability Group
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 |
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 |
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 |
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 |
Go to Advanced Security Settings for Organisational Unit (OU): Follow the highlighted part as below image:
| Advanced Security Settings of Organizational Unit |
Select the Create Computer Objects from permission tab:
| 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 |
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 |
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 |
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 |
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!
Related Posts:
- SQL Server Always On Availability Group (AG) Listener: step by step guideAvailability databases hosted on SQL Server Always On Availability Group (AG) can be connected using a unique Virtual Network Name (VNN) called Ava… Read More
- Understanding and configuring Windows Server Failover Cluster Quorum for SQL Server Always OnWhat is quorum? SQL Server availability solutions such as Always On Availability Group (AG) and Failover Cluster Instance (FCI) depends on Windo… Read More
- Step by step guide to add SSISDB to SQL Server 2016 Always On Availability Group (AG)Starting from SQL Server 2016, you can add SSISDB as Availability Database in Always On Availability Group (AG). This enables high availability and a… Read More
- How to configure Read-Only routing on SQL Server 2016 Always On Availability Group (AG)?The great advantage of Always On Availability Group over other availability solutions is their ability to scale-out read operations (SELECT queries). … Read More
- Architecting reliable backup and recovery strategyI have been managing multiple databases, mostly in Microsoft SQL Server and MySQL server, both on on-premise and cloud. We have faced a lot of chal… Read More
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/310449.html