SQL Server AlwaysOn Availability

Question

I have two server SQL Server 2016 and I want to implement the AlwaysOn Availabiliy.

The first server is Read_Write

The second server is Read_Only

I configured an AlwaysOn Availability as folowing :

CREATE AVAILABILITY GROUP ag
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = OFF,
DTC_SUPPORT = NONE,
FAILURE_CONDITION_LEVEL = 3,
HEALTH_CHECK_TIMEOUT = 30000)
FOR DATABASE DATABASE_NAME
REPLICA ON N'server1' WITH (ENDPOINT_URL = N'TCP://server1_adress:port', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://server1_adress:port')),
N'server2' WITH (ENDPOINT_URL = N'TCP://server2_adress:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, BACKUP_PRIORITY = 50, PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=('server1')))
LISTENER N'listener_server' (
WITH IP
((N'ip', N'mask')
)
, PORT=port);

My problem is all queries are routed to the first server.

How must I configure the AlwaysOn to balance read queries between the two servers?

Thanks.

Answer 1

First of all you have to define a special connection string to tell the SQL Server to connect to a read-only replica (called "application intent"):

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

In the second step you have to allow read-only access to all nodes in case of being the secondary:

ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'server1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = 
READ_ONLY))
GO
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'server2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = 
READ_ONLY))
GO

Furthermore add read-only routing URL to server2:

ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'server2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = 
N'TCP://server2_adress:port'));
GO

In the next step you have to define a routing list which server should be connected to when a specific server is the primary one. You already defined a READ_ONLY_ROUTING_LIST. But only with one server. What happens when server1 breaks? You should define a routing list for every possible primary node.

ALTER AVAILABILITY GROUP [ag] 
MODIFY REPLICA ON
N'server1' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('server2','server1')));

ALTER AVAILABILITY GROUP [ag] 
MODIFY REPLICA ON
N'server2' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('server1','server2')));
GO

For the new load balancing feature of AlwaysOn in SQL Server 2016, you need at least a third read-only node which allows read-only connections and configure a "nested" routing list:

ALTER AVAILABILITY GROUP ag 
MODIFY REPLICA ON N'server1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=
(('server2', 'server3'), 'server1')));