Now that Availability Groups are becoming more widespread, I thought I would cover a topic which may be overlooked during the initial planning and installation of SQL Server in this type of environment. In order to truly have a fault tolerant configuration, some thought and planning must go into the setup of database connectivity.
I won't go into the details of setting up your AlwaysOn environment in this post, but for some additional help I suggest you take a look at Aaron Bertrand's post, "Troubleshooting AlwaysOn – Sometimes it takes many sets of eyes." Once your environment is configured, the next step in providing database connectivity is to create an Availability Group Listener using SQL Management Studio or T-SQL:
ALTER AVAILABILITY GROUP [GroupName]
ADD LISTENER N'ListenerName'
(WITH IP ((N'10.x.x.x', N'255.255.255.0')), PORT=1433);
AG Listener Connection Strings
Your Virtual Network Name (VNN) is registered in DNS and is always owned by the SQL Server instance where the primary replica resides. All of the IP addresses that are supplied while configuring the AG Listener are registered in DNS under the same virtual network name.
After you have created your AG Listener, you must make sure your clients can connect. Your application connection operates in the same manner it always has, however, instead of pointing towards a specific server in your connection string, you point towards the AG Listener.
AG Listeners can only be connected to using TCP, and are resolved by your local DNS to the list of IP addresses and TCP ports that are mapped to the VNN. Your client will attempt to connect to each of the IP addresses in turn until it either gets a connection or until it reaches a connection timeout. An important connection string parameter to consider using is MultiSubnetFailover. If this parameter is set to true, the client will attempt the connections in parallel enabling faster connectivity and if necessary, faster client failovers:
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI; MultiSubnetFailover=True
When a failover occurs, client connections are reset, and the ownership of the AG Listener moves to the SQL Server instance that takes over the primary replica role. The VNN endpoint is then bound to the new IP addresses and TCP ports of the new primary replica instance. Depending on the client, an automatic reconnect to the AG will occur, or the user may have to manually restart the affected application or connection.
Application Intent
One of the biggest reasons to implement Availability Groups is to provide the ability to leverage your backup or disaster recovery environments to offload work from your production environment. These servers can now be used for backups, analysis, ad-hoc queries and reporting, or any other operation in which having a read-only copy of the database is sufficient.
To provide read-only access to your secondary replicas, the ApplicationIntent connection string parameter is used. An optional read-only routing list of SQL Server endpoints can be configured on each replica. This list is used to redirect client connection requests that use the ApplicationIntent=ReadOnly parameter to the first available secondary replica which has been configured with an appropriate application intent filter.
Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI; MultiSubnetFailover=True;ApplicationIntent=ReadOnly;
Application Intent Filtering
To facilitate Application Intent from the clients connecting to your Availability Group, each SQL Server instance in the group should be configured with an appropriate Application Intent Filter. The filter determines which types of connections each replica will accept.
A primary replica which is configured to have Connections in Primary Role of “Allow all connections” will accept any connections made through the AG Listener. A primary replica configured as “Allow read/write connections” will reject any connection that specifies “ApplicationIntent=ReadOnly.”
When configuring replicas, you must also define whether or not each will be a Readable Secondary. A replica which is configured as “No” will refuse all connections. This replica is assumed to be used only for failover in a disaster recovery situation. If the secondary replica is configured as “Yes”, all connections will be allowed, but only for read access, even if “ApplicationIntent=ReadOnly” is not specified. Finally if the secondary is configured as “Read-only intent”, only clients that specify “ApplicationIntent=ReadOnly” will be allowed to connect.
Read-Only Routing
Now that we know how to configure Application Intent on the server instances, and create the necessary connection strings, we have to configure Availability Group read-only routing. When you connect to the AG Listener using the connection string as defined above the listener queries the primary replica instance and determines if the connection should be made to the primary (read/write) or to a read-only secondary. To accomplish this, a routing list must be created for EACH availability replica which is used if and when the replica assumes the role of primary. Typically, the best practice is to include every read-only routing URL for each read-only secondary replica with the local replica URL at the end of the list. Read-intent connection requests are routed to the first available readable secondary on the routing list, there is no load balancing between the secondaries.
First, modify each replica to provide the read-only routing URL:
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER01' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.mydomain.com:1433'));
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER02' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.mydomain.com:1433'));
Second, modify each replica to provide the read-only routing list to be used when the given replica is in the primary role:
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));
ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'COMPUTER02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
The routing URL should be in the form of “TCP://: ”. For help determining your URL, see this blog and script created by Matt Neerincx.
Conclusion
With your read-only routing configured, AG Listener created and your client applications using the correct connection strings, you should have a fully fault tolerant connection for your Availability Group. Make sure you take some time to test your connectivity, and the ability of your applications to follow your servers when they fail over.
I've run through this but am still seeing it connect to the primary no matter what, if I try this for example.
sqlcmd -S "mylistener" -d adatabase -Q "select @@servername" -K READONLY
Is there some other pre-requisite I missed?
You need to add the parameter ApplicationIntent=ReadOnly in order for your Listener Device to route to the designated read only secondary replica.
e.g. Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
as per this article http://msdn.microsoft.com/en-gb/library/hh710054.aspx
Can you please clarify a concept about AG Listeners. How does the AG Listener know which SQL Server Instance is the primary replica? Is it that the AG Listener is a cluster resource, which can only be owned by one node (through WSFC) and that's how the mapping occurs? If so, how does it handle secondary replicas that are read only? Thank you!
After doing this, i have connected my listener using ssms with additional parameter (ApplicaitonIntent=ReadOnly;) from both primary and secondary node. After that iam getting the below error. Please advise where iam wrong.
TITLE: Connect to Database Engine
——————————
Cannot connect to AG_Listener.
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
An existing connection was forcibly closed by the remote host
Hi ManoShankar,
Were you able to resolve this error, I am getting the same error.
Thanks
Hi Harneet,
Kindly check your routing URL, what is port of routing URL and End point?
Routing port should be 1433, run the below query and confirm it.
SELECT replica_server_name,Read_only_routing_url,endpoint_url,secondary_role_allow_connections_desc,primary_role_allow_connections_desc FROM sys.availability_replicas
Regards,
ManoShankar
Hi Harneet,
Kindly check your routing URL, what is port of routing URL and End point?
Routing port should be 1433, run the below query and confirm it.
SELECT replica_server_name,Read_only_routing_url,endpoint_url,secondary_role_allow_connections_desc,primary_role_allow_connections_desc FROM sys.availability_replicas
Regards,
ManoShankar
We are configured alwayson availability group node1 and node2. During the failover from primary to secondary we received connection timeout error during the failover.we lossed database connectivity.
That was the one thing I was not clear on, if the endpoint URL and read only routing URL were supposed to be the same. Once I changed the read only routing URL port from 5022 to 1433 I no longer got the connection error and it is working.
Then what is the use of setting Readable Secondary as Yes if I have to use ApplicationIntent=Readonly ?
Because there are two sides to the conversation. You don't necessarily want all secondaries to be used as readable (you may want to keep reporting traffic off a hot standby, for example), and you don't necessarily want all clients to be reporting/read-only since some of your applications should actually interact with the primary.
Think about a traffic light – the light turns green, but you still have to press the gas to go through the light.