SQL Server AlwaysON read from multiple seconderies

Question

I configured always-on on one of my environments .

SQL1 = Primary
SQL2 = Slave1 (readonly)
SQL3 = Slave2 (readonly)

My Question:
I want that my app will read from both slaves servers (for example SQL2 and SQL3 in case that they are the slaves and SQL1 is the master) .

Is it possible and if yes, how I do it

I alread configured the routing tables Like:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON N'SQL1' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL1.aws.ir:1433'));
GO

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON N'SQL2' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL2.aws.ir:1433'));
GO

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON N'SQL3' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL3.aws.ir:1433'));
GO


ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL1' WITH 
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL2',N'SQL3')))
GO

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL2' WITH 
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL1',N'SQL3')))
GO

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL3' WITH 
(PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'SQL1',N'SQL2')))
GO

10X

You have done the routing urls,but have you enabled readable secondaries — TheGameiswar, Jan 31 '17 at 11:27
I attched to the question AG properties . that is what you ment ? — itzik Paz, Jan 31 '17 at 11:52
i cant see images(they are blocked)..what is the issue you are facing now..I dont see any issue as well,since you have enabled read only routing list — TheGameiswar, Jan 31 '17 at 11:56
I have No issue . you asked "but have you enabled readable secondaries" , how I do it ? — itzik Paz, Jan 31 '17 at 11:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134474/discussion-between-thegameiswar-and-itzik-paz). — TheGameiswar, Jan 31 '17 at 11:59

score 0 · Answer 1 · answered Jan 31 '17 at 11:33

0

You need to create a Listener. It's a virtual network name to which clients can connect in order to access a database in a primary or secondary replica of an Always On availability group.

Read some more info about AGs here https://msdn.microsoft.com/en-us/library/hh213417.aspx#AGlisteners

answered Jan 31 '17 at 11:33

Igor Micev

1,514
1
17
23

Yes, I set up a listener ! – itzik Paz Jan 31 '17 at 11:40
If read-only routing is configured for one or more readable secondary replicas, read-intent client connections to the primary replica are redirected to a readable secondary replica. Read some more from the link... – Igor Micev Jan 31 '17 at 11:44
from what you are saying , I understand that the traffic from the app will split between the slaves . in my example between SQL2 And SQL3 ? – itzik Paz Jan 31 '17 at 11:48
Yes, if the request is read-only then it won't ping the master but the slaves (the secondaries). The routing and management is done by the AlwaysOn automatically. – Igor Micev Jan 31 '17 at 11:51
Are you sure about it . Is'nt it from SQL 2016 ONLY ? (I had 2012 SP1 Enterprise Edition) – itzik Paz Jan 31 '17 at 12:50
It's the same for SQL Server 2012 as well. You can check it in the version of the site, or here - https://msdn.microsoft.com/en-us/library/hh213417(v=sql.110).aspx – Igor Micev Jan 31 '17 at 13:41

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I want that my app will read from both slaves servers (for example SQL2 and SQL3 in case that they are the slaves and SQL1 is the master)

this is not possible ,unless you are in SQL2016..

SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it would direct the connection to the next replica in the routing list.

When you have multiple secondary replicas available for read, it is not possible to spread the read load across those replicas.

but with 2016,you can configure load-balancing across a set of read-only replicas.

for Example,

ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON N’SQL16N1′ 
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘SQL16N3’, ‘SQL16N2’), ‘SQL16N1’)));

In the above routing list

The first incoming read-only connection will be routed to SQL16N3, the second read-only connection will be routed to SQL16N2, the third read-only connection will be routed to SQL16N3, the fourth read-only connection will be routed to SQL16N2, and so on

References:
https://blogs.msdn.microsoft.com/alwaysonpro/2016/05/02/sql-server-2016-alwayson-availability-group-enhancements-load-balance-read-only-routing/

only on Enterprise editions. Standard does not allow readable secondaries. — Ricardo C, Feb 14 '17 at 15:43

SQL Server AlwaysON read from multiple seconderies

2 Answers2