AG配置

--指定在此可用性副本当前拥有辅助角色（即它是辅助副本）时要生效的角色有设置：
ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DBServer1.test.local:6666'))

ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://DBServer2.test.local:6666'))

--指定在此可用性副本当前拥有主角色（即它是主副本）时要生效的角色有设置：
ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'DB2')));

ALTER AVAILABILITY GROUP CEPUDBHAO
MODIFY REPLICA ON
N'DB2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'DB1')));

select
b.replica_server_name
,a.routing_priority
,c.replica_server_name
from
sys.availability_read_only_routing_lists a
left join
sys.dm_hadr_availability_replica_cluster_states b
on a.replica_id = b.replica_id
left join sys.dm_hadr_availability_replica_cluster_states c
on a.read_only_replica_id = c.replica_id
order by a.replica_id

select a.*,c.is_local,c.role_desc,d.endpoint_url,d.read_only_routing_url
from sys.dm_hadr_availability_replica_cluster_nodes a
join sys.dm_hadr_availability_replica_cluster_states b
on a.replica_server_name = b.replica_server_name
join sys.dm_hadr_availability_replica_states c
on b.replica_id = c.replica_id
join sys.availability_replicas d
on c.replica_id = d.replica_id