05 Always On Always On PART 05 Always

05 [ 第五部分 ] Always On 测试 ★Always On 读写测试 PART 05

Always On 测试 Always On读写分离 �算机 Node 1(群集�点 A) Node 2(群集节点B) Node-DC(域控) IP 主机 IP: 192. 168. 1. 101 MASK: 255. 0 DNS: 192. 168. 100 IP: 192. 168. 1. 102 MASK: 255. 0 DNS: 192. 168. 100 IP: 192. 168. 100 MASK: 255. 0 VIP-CLUSTER 192. 168. 1. 103 SQL Alwayson VIP 192. 168. 1. 104 SQL Alwayson listener 192. 168. 1. 105

Always On 测试 Always On读写分离

Always On 测试 Always On读写分离 --建立read指针 - 在当前的primary 上为每个副本建立副本对于tcp的链接 alter availability group test. AG modify replica on 'Node 1SQLA' with (secondary_role (read_only_routing_url='TCP: //Node 1. com: 5022')) alter availability group test. AG modify replica on 'Node 2SQLB' with (secondary_role (read_only_routing_url='TCP: //Node 2. com: 5022'))

Always On 测试 Always On读写分离 --为每个可能的primary role配置对应的只读路由副本 --list列表有优先级关系，排在前面的具有更高的有限级，node 1正常路由让node 1读，如果node 1故障了路由 才能让node 2读 alter availability group test. AG modify replica on 'Node 1SQLA' with (primary_role (read_only_routing_list=('NODE 2SQLB')))

Always On 测试 Always On读写分离 --查询优先级关系 select ar. replica_server_name, rl. routing_priority, (select ar 2. replica_server_name from sys. availability_read_only_routing_lists rl 2 join sys. availability_replicas ar 2 on rl 2. read_only_replica_id=ar 2. replica_id and rl. replica_id=rl 2. replica_id and rl. routing_priority=rl 2. routing_priority and rl. read_only_replica_id=rl 2. read_only_replica_id ) as 'read_only_replica_server_name' from sys. availability_read_only_routing_lists rl join sys. availability_replicas ar on rl. replica_id=ar. replica_id

Always On 测试 Always On读写分离 登入方式 C#连接字符串 server=侦听IP(192. 168. 1. 105); database=; uid(sa)=; pwd(*******)=; Application. Intent=Read. Only ssms：其它连接参数 ---数据库连接 ---仅意向读连接 Application. Intent=Read. Only ---读写连接 Application. Intent=Read. Write

Always On 测试 Always On读写分离 连接测试 1. Read. Only

06 [ 第六部分 ] Always On运维监控 ★Always On 运维 PART 06 ★Always On 监控

Always On 运维 Always On日志收缩 1. 查看是否关闭事务 dbcc opentran

Always On 运维 Always On读写分离 建议步骤 ====== 主数据 1. 建议在做任何操作之前做一个全备份： BACKUP DATABASE [数据库名] TO DISK='I: \XXXDatabase. Name. bak' 2. 从AG中分离数据库，命令如下： USE MASTER ALTER AVAILABILITY GROUP [AG名] REMOVE DATABASE [数据库名] 3. 在主服务器备份数据库日志文件 BACKUP LOG [数据库名] TO DISK='I: \XXXDatabase. Name_log. trn' 4. 设置主服务器上数据库日志文件初始大小为 10 G，增长量 200 MB： ALTER DATABASE [数据库名] MODIFY FILE (NAME=’Database. Name_log. ldf’, SIZE=10240000 KB, FILEGROWTH=204800 KB)

Always On 运维 Always On读写分离 副本数据库 在副本服务器还原数据库及日志文件 RESTORE DATABASE [数据库名] FROM DISK=’I: \XXXDatabase. Name. bak’ WITH FILE=1, MOVE ‘[数据库名]’ TO ‘数据库路径’, MOVE ‘[日志名]’ TO ‘日志路径’, NORECOVERY, REPLACE GO RESTORE LOG [数据库名] FROM DISK=’I: \XXXDatabase. Name_log. trn’ WITH FILE=1, NORECOVERY GO

Always On 运维 Always On读写分离 主数据库 AG中重新加入数据库 右键 Add Database > Select Databases > Connect to Replicas > Join Only => Success

Always On 运维 Always On读写分离

Always On 监控 SELECT ar. replica_server_name as '节点服务器名', adc. database_name '数据库名', ag. name AS 'ag名', drs. is_local as '本地数据库', --0是实例，1是本地数据库 drs. is_primary_replica as '主副本', --1是主副本，0是辅助副本 drs. synchronization_state_desc as '同步状态描述', drs. is_commit_participant as'提交关系' , --异部提交 0，同步提交 1 drs. synchronization_health_desc as '同步健康状态', NOT_HEALTHY不健康、PARTIALLY_HEALTHY 部分健康、HEALTHY健康 drs. log_send_queue_size AS '主库尚未发送到辅库日志记录量_kb', drs. log_send_rate AS '日志记录发送到辅助数据库的速率_kb',

Always On 监控 FROM sys. dm_hadr_database_replica_states AS drs INNER JOIN sys. availability_databases_cluster AS adc ON drs. group_id = adc. group_id AND drs. group_database_id = adc. group_database_id INNER JOIN sys. availability_groups AS ag ON ag. group_id = drs. group_id INNER JOIN sys. availability_replicas AS ar ON drs. group_id = ar. group_id AND drs. replica_id = ar. replica_id ORDER BY ag. name, ar. replica_server_name, adc. database_name; https: //docs. microsoft. com/zh-cn/previous-versions/sql-server-2012/ff 877972(v=sql. 110)

Always On 监控 查看主副本和辅助副本 IF Server. PROPERTY ('Is. Hadr. Enable')=1 begin select AGC. name as '可用性组名' , RCS. replica_server_name as 'SQL群集节点名' , ARS. role_desc as '副本角色' , AGL. dns_name '侦听器名' from sys. availability_groups_cluster as AGC inner join sys. dm_hadr_availability_replica_cluster_states as RCS on RCS. group_id=AGC. group_id inner join sys. dm_hadr_availability_replica_states as ARS on ARS. replica_id = RCS. replica_id inner join sys. availability_group_listeners as AGL on AGL. group_id=ars. group_id WHERE ars. role_desc='PRIMARY' --where ars. role_desc='SECONDARY' --限定主副本，如果不限定，则可以查看所有副本 End

Always On 监控 Always. On所用到的DMVIF Server. PROPERTY ('Is. Hadr. Enable')=1 select select select select * * * * from from from from sys. dm_hadr_cluster_members sys. dm_hadr_cluster_networks sys. availability_groups_cluster sys. dm_hadr_availability_group_states sys. availability_replicas sys. dm_hadr_availability_replica_cluster_nodes sys. dm_hadr_availability_replica_cluster_states sys. dm_hadr_availability_replica_states sys. dm_hadr_auto_page_repair sys. dm_hadr_database_replica_states sys. dm_hadr_database_replica_cluster_states sys. availability_group_listener_ip_addresses sys. availability_group_listeners sys. dm_tcp_listener_states

Always On 监控 SQL Server TCP侦听器信息 SELECT listener_id , ip_address, is_ipv 4 , port, type_desc , state_desc, start_time FROM sys. dm_tcp_listener_states WITH (NOLOCK) OPTION (RECOMPILE)

Always On 监控 AG状态 DECLARE @HADRName VARCHAR(25) SET @HADRName = @@SERVERNAME SELECT n. group_name , n. replica_server_name , n. node_name , rs. role_desc , db_name(drs. database_id) AS 'DBName' , drs. synchronization_state_desc , drs. synchronization_health_desc FROM sys. dm_hadr_availability_replica_cluster_nodes n JOIN sys. dm_hadr_availability_replica_cluster_states cs ON n. replica_server_name = cs. replica_server_name JOIN sys. dm_hadr_availability_replica_states rs ON rs. replica_id = cs. replica_id JOIN sys. dm_hadr_database_replica_states drs ON rs. replica_id = drs. replica_id WHERE n. replica_server_name <> @HADRName