How to Configure Read-Only Routing for an Availability Group in SQL Server 2016
The SQL Server Always On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always On Availability Group provides a high availability solution on the groups level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.
SQL Server 2016 supports up to eight replicas. By default, the secondary replicas do not allow any workload, which is the same as the secondary party of the SQL Server Mirroring site, where the replica will be used only for failover purposes in a disaster recovery situation. The secondary replicas can be also configured as an active readable secondary to allow read-only access to all secondary databases, as the data in the secondary databases is near real time data. Setting the readable secondary as Read-Intent Only will allow the secondary server to serve the read-only workload only if the connection string of the application contains Application Intent=Readonly parameter. Connections with Application Intent value equal to ReadOnly can be enforced to secondary replicas using a new Always On Availability Group feature called Read-Only Routing. In this way, the queries that will perform read only processes that are directed to the Always On Availability Group listener will be redirected to the secondary replicas instead of the primary replica.
In SQL Server 2012 and 2014 versions, the read-only workload redirection process will redirect the queries specifically to the first secondary replica defined in the routing list, unless it was not accessible, and then it will direct the connection to the next replica in the routing list. The other secondary replicas will not participate in serving the read-only workload, limiting the load balancing mechanism to only one replica. As a workaround, the Read-Only routing list can be updated periodically, so that you will make sure that all replicas will be used to serve the read-only workload. SQL Server 2016 removes this limitation by introducing the native load balancing mechanism, as we will see later.
Read More: How to Configure Read-Only Routing for an Availability Group in SQL Server 2016