Search code examples
sql-serveralwayson

SQL Server AlwaysON read from multiple seconderies

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 enter image description here

enter image description here

Solution

  • 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/