Rick Pittser

Configuring Availability Group connectivity

November 19, 2013 by in System Configuration | 10 Comments
Save time monitoring and managing performance in the most challenging data environments.  More
Answers.SQLPerformance.com

Upload your tough execution plans and get helpful query tuning advice from renowned experts like Paul White.

Visit Site

Featured Author

Paul Randal, CEO of SQLskills, writes about knee-jerk performance tuning, DBCC, and SQL Server internals.

Paul’s Posts

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.”

rp_ag_connections

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.

rp_ag_readable

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.