Read-Only AG Routing

John Handra shows how to configure read-only routing on an Availability Group:

NOTE: SQL Server 2016 introduces load-balancing across the secondary replicas in your AlwaysOn Availability Group. To utilize this feature, the above routing list will need to be modified so that SQL Server directs incoming read-only connections across the servers you choose to be load-balanced. You will have to use nested parentheses around the server instances you want to be part of the load-balanced group.

Read-only routing requires some additional setup and possibly load changes, but it can give you a nice performance gain by off-loading some of your read requests.

Related Posts

Synonyms And Availability Groups

Allen McGuire shows how to maintain synonyms between instances in an Availability Group: I’m a big fan of using synonyms to point to objects in other databases, but if you’ve tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because […]

Read More

Synchronizing Availability Group Objects

Derik Hammer has a process to maintain logins, backup devices, linked servers, SQL Agent details, and more between Availability Group nodes: A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29