<\/p>\n
In Always On Availability Group Architecture, you can read from the Secondary Server.<\/p>\n
So how do you use it in your applications?<\/p>\n
In this article we will be examining the details of this issue.<\/p>\n
For example, you have two nodes SQLNODE01\\INSTANCE01 and SQLNODE02\\INSTANCE01.<\/p>\n
And between these two nodes, an availability group named AG1 is defined.<\/p>\n
Primary Instance is the SQLNODE01\\INSTANCE01. The AG1 uses 1435 as a port.<\/p>\n
First, you should execute the below script.<\/p>\n
Open the cmd command line and type “ping SQLNODE01” and type the name you see on the command line instead of “SQLNODE01.mydomain.com”.<\/p>\n
use master\r\ngo\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE01\\INSTANCE01' WITH\r\n(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE01\\INSTANCE01' WITH\r\n(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP:\/\/SQLNODE01.mydomain.com:1435'));\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE02\\INSTANCE01' WITH\r\n(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));\r\n\r\n\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE02\\INSTANCE01' WITH\r\n(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP:\/\/SQLNODE02.mydomain.com:1435'));\r\n\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE01\\INSTANCE01' WITH\r\n(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE02\\INSTANCE01')));\r\n\r\nALTER AVAILABILITY GROUP [AG1]\r\nMODIFY REPLICA ON\r\nN'SQLNODE02\\INSTANCE01' WITH\r\n(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE01\\INSTANCE01')));<\/pre>\n <\/p>\n
With the above script, we activated the read intent option and made the route operations.<\/p>\n
You should add “ApplicationIntent = ReadOnly” at the end of the connection string for queries that are intended to be read from the application.<\/p>\n
If you are going to connect via SSMS, you must first click on Options to add the database name you want to access to the Connect To Database section and add “ApplicationIntent = ReadOnly” to the Additional Connection Parameters section.<\/p>\n
In this way read only connetions will be directed to the secondary server. In this way, you can distribute the select load or forward your report queries to your secondary server.<\/p>\n
Before SQL Server 2016, if there are more than one replica, the Selects will go to the first read only replica. With SQL Server 2016, read operation can be done by distributing it to secondary servers in load balancing logic. You can configure it as follows.<\/p>\n
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQLNODE01\\INSTANCE01' \r\nWITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE02\\INSTANCE01','SQLNODE01\\INSTANCE01')));\r\n\r\nALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQLNODE02\\INSTANCE01' \r\nWITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLNODE01\\INSTANCE01','SQLNODE02\\INSTANCE01')));<\/pre>\n\n<\/div>\n