SQL Server Administration: AlwaysOn Enhancement - Load Balancing for Readable Secondaries
-by Ginger Keys
One of the great features of using AlwaysOn is the ability to use the secondary replicas for read only operations. Prior to AlwaysOn SQL2016, the listener would direct any read-only requests to the first available replica, even though you might have several secondary replicas available, and you might have preferred the read operations go to secondary #3 or #4, instead of #2. Now in SQL2016 the list of readable secondaries are presented to the listener in a round-robin fashion. By executing TSQL statement similar to the following, the workload will adjust in the event of failover:
In addition to configuring your availability group routing lists, you must also ensure that the client's application connection string use an application intent of read-only in the connection string when connecting to the AG listener. If this is not set in the client application connection string, the connection will automatically be directed to the primary replica. Following is an example of a connection string:
Server=tcp:AGListener,1433; Database=AdventureWorks;IntegratedSecurity=SSPI; ApplicationIntent=ReadOnly
Try not to mix synchronous and asynchronous replicas in the same load balance group since there could be a lag in the data that would cause confusion.
___________________________________________________________________________
SQLRx has worked hard to minimize the impact of monitoring SQL Servers to the point that we are able to monitor high transaction systems with little impact (1% load) on the target system. Contact us today to help you monitor your SQL servers!
|
