0

We are setting up SQL Server 2014 AG with multiple secondary readonly instances since we have some significant queries that are killing our primary. What we would like to do is put a Load Balancer in front of the readers to help scale out.

The web application is .NET/IIS. Are there any settings that the SqlConnection, SQL Server, or the LB will need, such as sticky sessions or Connection Pool Settings)? Or any other surprises that we may run into?

Thad
  • 101
  • 3
  • The main surprise you'll run into is the LB not knowing which is primary after a failover occurs and you kill your new primary. I'm not wondering if replication would be a better fit for a reporting workload that is that intense. One more item to consider is blocking your redo thread on the secondary replicas. – Sean Gallardy Apr 11 '15 at 01:44
  • The load balancer is for the ReadOnly secondaries, the primary is setup on an Active/Passive failover cluster so it should not failover to the readers automatically. – Thad Apr 13 '15 at 15:19

1 Answers1

0

You'll need to make sure the listener is properly configured. Where the instance would be in the connection string, you'll use the listener name. In this way you don't need to know who is primary or secondary. Second, as part of the connection string you will need to add "applicationintent=readonly". This tells the listener how to route the connection request when all the request is doing is performing a query.

Also, there is some routing you will need to be aware of. The following link walks you through it,but basically you need to configure each server in the AG and then set up the routing for each.

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Modify and add as required

Steve Mangiameli
  • 186
  • 6