Read-only load balancing in THE SQL Server AlwaysOn architecture Configure read-only routes for AlwaysOn availability groups
Abstract:
Compared with load balancing solutions such as mysql Router and HaProxy, the listener function of SQL Server is relatively weak. Without external solutions, virtual IP resources can be added to separate read/write from read-only. SQL Server 2016 provides a new approach to read-only load balancing.
Prerequisites:
An availability group must have an availability group listener. One or more availability replicas must be configured in secondary roles to accept read-only (Read-intent Only and yes are recommended)
You must connect to the server instance hosting the current master copy (not much to say, perform operations on the master node). If you use SQL logins, make sure the account is configured correctly.
Configure availability copies to support read-only routing
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N’COMPUTER01′ WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)); ALLOW_CONNECTIONS READ_ONLY for official documents, ALL 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 = ALL));
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’COMPUTER03′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N’COMPUTER03′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://COMPUTER03.contoso.com:1433′));
— Configure routing rules
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N’COMPUTER01′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘COMPUTER02′,’COMPUTER03′),’COMPUTER01’)));
— Query the priority relationship
SELECT ar.replica_server_name , rl.routing_priority , ( SELECT ar2.replica_server_name FROM sys.availability_read_only_routing_lists rl2 JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id WHERE rl.replica_id = rl2.replica_id AND rl.routing_priority = rl2.routing_priority AND rl.read_only_replica_id = rl2.read_only_replica_id ) AS ‘read_only_replica_server_name’ FROM sys.availability_read_only_routing_lists rl JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id
Copy code test configuration:
DataSource=xxx; InitialCatalog=test; IntegratedSecurity=False; UserID=xxx; Password=xxx; ApplicationIntent=ReadOnly;
ApplicationIntent=ReadOnly does not apply. You can use the ADO.NET or JDBC driver. Using JDBC, you can see that using the same listener as the Data Source will eventually poll the host name in the configured routing rules.
image.png
image.png
Other tests:
The secondary node is down. The SQL Server service of COMPUTER02 and COMPUTER03 is shut down. According to the previous rules, when COMPUTER01 is the primary node, if COMPUTER02 and COMPUTER03 are down, the access requests are accepted by COMPUTER01
MODIFY REPLICA ON N’COMPUTER01′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘COMPUTER02′,’COMPUTER03′),’COMPUTER01’))); Copy code test results meet expectations;
2. Manual failover (changing the primary node)
Readonly can only connect to COMPUTER02.
Added new rules when COMPUTER02 is used as host
MODIFY REPLICA ON N’COMPUTER02′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=((‘COMPUTER03′,’COMPUTER01′),’COMPUTER02’))); Copy code to test the new rule;
3. Manually remove the node. Remove COMPUTER01 from the list
MODIFY REPLICA ON N’COMPUTER02′ WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘COMPUTER03′,’COMPUTER02’))); Copy code summary:
Based on the above tests, the new policy can be set as: in the case of a 4-node server, we can set it as:
Normally, all read-only requests are directed to synchronous read-only node 1 and asynchronous read-only node 2, and the requests are polled between them.
When the performance of two read-only nodes reaches the bottleneck, add asynchronous read-only nodes 3 and 4 to the polling list so that the read pressure is shared among the four nodes.
After the off-peak period, remove nodes 3 and 4 from the polling list.
All new and removed nodes are Online, and services are not affected.