Read Only Routing

In the SQL Server AlwaysOn structure, the primary server responds regardless of the purpose for which the request comes (read or change). Secondary servers, which can be multiple, are used for high availability and disaster recovery scenarios.

With Read-Only Routing, you can have requests to the database that only involve reading existing data route to secondary servers.

To do this, you must first move the setting to “Read-intent only” by clicking on the list in the “Readable Secondary” column on the AG Properties screen. In the event of a potential failover, you must do this in all replicas so that all your servers can respond to read-only requests.

On the application side, simply add the ApplicationIntent=ReadOnly parameter to the connection string used when sending read-only requests to the database.

Below you can see a sample connection string.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

If a connection is to be made through the SSMS, you must add the database name that you will access the Connect To Database section by clicking Options on the SSMS, and the “ApplicationIntent=ReadOnly” parameter in the Additional Connection Parameters section.