一个磁盘I/O故障导致的AlwaysOn FailOver 过程梳理和分析

作者: 东山絮柳仔

下面是们在使用AlwaysOn过程中遇到的一个切换案例。这个案例发生在2014年8月,虽然时间相对久远了,但是对们学习理解AlwaysOn的FailOver原理和过程还是很有帮助的。本次FailOver的触发原因是系统I/O问题。大家需要理解,操作系统I/O出现了问题不一定立即触发SQL Server发生漂移,因为坏的槽点可能不在SQL Server实例所用到的位置,但是随着时间持续 和数据堆积,问题槽点可能扩大升级。们可以看到在本例中,第一次出现I/O问题到SQL Server 漂移间隔了16分钟,所以大家不要奇怪。们重点可以FailOver的过程和触发条件设置上,即文章的第二和第三部分。

一 . 系统 I/O 异常 Log追踪

1.1 10:36:12 发现I/O异常

1.2 10:45:43 显示个别读写花费时间较长

1.3 10:45:28 看似I/O严重

1.4 10:52:20 出现个别连接Fail 现象

(查看表中的最后一笔数据显示为10:53:17

. AlwaysOn FailOver 过程

2.1 系统提示需要FailOver 2.2 高可用性组的本地副本需要离线。

相关知识:Lease expired event from the cluster. Possible causes include loss of lease, possible network issues and sp_server_diagnostic query timeout. ) 2.3 错误提示信息显示,SQL Instance 和WSFC 连接异常。 2.4 可用性副本的角色发生变换。 2.5 角色为RESOLVING 无法访问DB

相关知识:When the role of an availability replica is indeterminate, such as during a failover, its databases are temporarily in a NOT SYNCHRONIZING state. Their role is set to RESOLVING until the role of the availability replica has resolved.)

此时: 通过SSMS管理器,连接数据也是不可以访问的,显示状态为不同步了。

三 . 相关知识点

3.1 什么是resourceDell ?resourceDell 的用途?

由于AlwaysOn可用性组是建立在Windows故障转移群集之上的,Alwayson可用性组需要一个群集resourceDell来连接Windows群集和SQLServer实例。由于可用性组是一个群集资源,Windows群集需要透过AlwaysOn的resourceDell来控制资源的上线/离线,检查资源是否失败,更改资源的状态和属性,以及发生各种命令给可用性副本实例。(AlwaysOn可用性组的资源类型是"SQLServer Availability Group”) AlwaysOn通过sp_server_diagnostics来检查可用性组的健康状况,不断地获得诊断信息。sp_server_diagnostics的评估结果会被用来和AlwaysOn可用性组的FailureConditionLevel设置相比较,来约定是否符合发生故障转移的条件。一旦条件满足,则可用性组就被切换到新的可用性副本上。 3.2 HealthCheckTimeout The HealthCheckTimeout setting is used to specify the length of time, in milliseconds, that the SQL Server resource DLL should wait for information returned by the sp_server_diagnostics stored procedure before reporting the AlwaysOn Failover Cluster Instance (FCI) as unresponsive. Changes that are made to the timeout settings are effective immediately and do not require a restart of the SQL Server resource. The resource DLL determines the responsiveness of the SQL instance using a health check timeout. The HealthCheckTimeout property defines how long the resource DLL should wait for the sp_server_diagnostics stored procedure before it reports the SQL instance as unresponsive to the WSFC service. The following items describe how this property affects timeout and repeat interval settings:

  • The resource DLL calls the sp_server_diagnostics stored procedure and sets the repeat interval to one-third of the HealthCheckTimeout setting.

  • If the sp_server_diagnostics stored procedure is slow or is not returning information, the resource DLL will wait for the interval specified by HealthCheckTimeout before it reports to the WSFC service that the SQL instance is unresponsive.

  • If the dedicated connection is lost, the resource DLL will retry the connection to the SQL instance for the interval specified by HealthCheckTimeout before it reports to the WSFC service that the SQL instance is unresponsive. 3.3 FailureConditionLevel The SQL Server Database Engine resource DLL determines whether the detected health status is a condition for failure using the FailureConditionLevel property. The FailureConditionLevel property defines which detected health statuses cause restarts or failovers. Review sp_server_diagnostics (Transact-SQL) as this system stored procedure plays in important role in the failure condition levels.

    |-----------|---------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
    | **Level** | **Condition**                                           | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
    | 0         | No automatic failover or restart                        | * Indicates that no failover or restart will be triggered automatically on any failure conditions. This level is for system maintenance purposes only.                                                                                                                                                                                                                                                                                                                                                             |
    | 1         | Failover or restart on server down                      | Indicates that a server restart or failover will be triggered if the following condition is raised: SQL Server service is down.                                                                                                                                                                                                                                                                                                                                                                                    |
    | 2         | Failover or restart on server unresponsive              | Indicates that a server restart or failover will be triggered if any of the following conditions are raised: SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings).                                                                                                                                                                                                                               |
    | 3         | Failover or restart on critical server errors           | Indicates that a server restart or failover will be triggered if any of the following conditions are raised: SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings). System stored procedure sp_server_diagnostics returns 'system error'.                                                                                                                                                         |
    | 4         | Failover or restart on moderate server errors           | Indicates that a server restart or failover will be triggered if any of the following conditions are raised: SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings). System stored procedure sp_server_diagnostics returns 'system error'. System stored procedure sp_server_diagnostics returns 'resource error'.                                                                                 |
    | 5         | Failover or restart on any qualified failure conditions | Indicates that a server restart or failover will be triggered if any of the following conditions are raised: SQL Server service is down. SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings). System stored procedure sp_server_diagnostics returns 'system error'. System stored procedure sp_server_diagnostics returns 'resource error'. System stored procedure sp_server_diagnostics returns 'query_processing error'. |
    

    3.4 通过 SQL 更改相关配置。 The following example sets the HealthCheckTimeout option to 15,000 milliseconds (15 seconds).

    ALTER SERVER CONFIGURATION 
    
    SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000;
    

    The following example sets the FailureConditionLevel property to 0, indicating that failover or restart will not be triggered automatically on any failure conditions.

    ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY FailureConditionLevel = 0;
    

    四 . 结语

    可用性副本的FailOver不仅仅取决于Availability Mode 和FailOver Mode,还要受限于FailureConditionLevel。 本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!

    原文创作:东山絮柳仔

    原文链接:https://www.cnblogs.com/xuliuzai/p/9886686.html

文章列表

更多推荐

更多
  • CopyOnWriteArrayList 读写分离,弱一致性 CopyOnWriteArrayList 读写分离,弱一致性,为什么会有CopyOnWriteArrayList?,什么是弱一致性,fail-safe特性,总结,t实现的List都是非线程安全的,于是就有了Vector,它是基于Arra
  • 写操作系统之开发引导扇区 写操作系统之开发引导扇区本篇目标简略流程软盘bootGetFATEntry结尾boot代码全文int 13h数据分布根目录FAT表代码解读汇编指令div和mul代码
  • 写操作系统之达到进程 写操作系统之实现进程C语言和汇编语言混合编程切换堆栈和GDT中断外部中断实现单进程实现多进程特权级参考资料方法例程是什么怎么做代码讲解切换堆栈是什么实现机制--通俗版实现机制--严谨版代码8259A进程三要素启动进程代码代码详解多个进程
  • 写操作系统之搭建开发环境 写操作系统之搭建开发环境本篇目标开发环境虚拟软盘操作系统的最初形态开发机器nasmbochs杂项运行解说
  • 写操作系统之开发加载器 写操作系统之开发加载器loader功能Kernel加载内核CPU模式GDT选择子寻址方式进入保护模式重新放置内核参考资料代码注释功能流程图代码编译放入软盘es:bx实模式保护模式gdtptrELF复制段验证补充知识
  • 转 sql 检索慢的48个原因剖析 转 sql 查询慢的48个原因分析
  • 转 牢记!sql server数据库开发的二十一条军规sql收藏 转 牢记!SQL Server数据库开发的二十一条军规(SQL收藏)
    东北大亨

  • 转 五种提升 sql 性能的方法 转 五种提高 SQL 性能的方法提高 SQL 性能的方法从 INSERT 返回 IDENTITY内嵌视图与临时表避免 LEFT JOIN 和 NULL灵活使用笛卡尔乘积拾遗补零
    东北大亨

  • 就这样,我走过了程序员的前五年。一路风雨泥泞,前方阳光正好。 就这样,我走过了程序员的前五年。一路风雨泥泞,前方阳光正好。三年北京两年成都写在后面
    why技术

  • 2020 年录制的视频列表 老周2020 年录制的视频列表成于天性,行于自然。
    东邪独孤

  • 近期文章

    更多
    文章目录

      推荐作者

      更多