SQL Server 2017 Always On Linux

1. 修改现有可用性组 ag1 以支持只读路由:

ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node1'
    WITH ( SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) );
ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node1'
    WITH ( SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://node1:1433') );
GO
    
ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node2'
    WITH ( SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) );
ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node2
    WITH ( SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://node2:1433') );
GO

ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node3'
    WITH ( SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) );
ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON 
    N'node3'
    WITH ( SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://node3:1433') );
GO

提示:
sqlcmd 可以通过指定 -K ReadOnly 开关来对允许只读访问的任意次要副本指定只读访问

2.配置 Availability Group Listeners ：

ALTER AVAILABILITY GROUP ag1 ADD LISTENER 'dns_name' ( WITH IP ( ('10.0.0.1','255.255.255.0') ) );
GO

参考文档

1. https://docs.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017
2. ALTER AVAILABILITY GROUP (Transact-SQL)：
   https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-availability-group-transact-sql?view=sql-server-ver15