Alwayson failover Process Analysis caused by one disk I/O failure

The following is a Failover case when using alwayson. This case occurred in August 2014. Although it was a relatively long time ago, it is helpful for us to understand alwayson's failover principles and processes. The cause of this failover is system I/O problems. You need to understand that an I/O problem in the operating system may not trigger SQL Server drift immediately, because the bad slot may not be used by the SQL server instance, however, as time continues and data accumulation occurs, the problem slot may expand and upgrade. We can see that in this example, the first time the I/O problem occurs, the SQL Server drift interval is 16 minutes, so don't be surprised. We can focus on the Failover process and trigger condition settings, that is, the second and third sections of the article.

I. System I/O exception log tracking

1.110:36:12I/O exceptions found

 

1.2 10:45:43 it is shown that individual reads and writes take a long time

1.310:45:28Seemingly serious I/O

1.4 10:52:20 an individual connection fail occursSymptom

(View the last data in the table10:53:17)

 

II . Alwayson failover Process

2.1System prompts failover

 

2.2The local copy of the high availability group needs to be offline.

(Related Knowledge:Lease expired event from the cluster. Possible causes include loss of lease, possible network issues and sp_server_diagnostic query timeout .)

2.3Error message display, SQL instanceAnd wsfcConnection exception.

2.4The role of the availability replica is changed.

 

2.5The role is resolving.Unable to access DB

(Related Knowledge: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 .)

In this case, data cannot be accessed through the SSMs manager, and the status is not synchronized.

3. Related Knowledge points

3.1What is resourcedell?? ResourcedellPurpose?

Because alwayson availability groups are built on Windows failover clusters, alwayson availability groups require a cluster resourcedell to connect windows clusters and sqlserver instances. Because the availability group is a cluster resource, a Windows Cluster needs to use alwayson's resourcedell to control the resource going online/offline, check whether the resource fails, and change the resource status and attributes, and various commands to the availability copy instance. (The resource type of the alwayson availability group is "sqlserver availability group ")

Alwayson uses sp_server_diagnostics to check the health status of the availability group and continuously obtain diagnostic information. The evaluation result of sp_server_diagnostics is used to compare with the failureconditionlevel settings of the alwayson availability group to determine whether the conditions for failover are met. Once the conditions are met, the availability group is switched to the new availability copy.

 

3.2Healthchecktimeout

The healthchecktimeout setting is used to specify the length of time, in milliseconds, that the SQL server resource DLL shocould 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 valid tive 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 shocould 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 callthe 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.3Failureconditionlevel

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.4PassSQLModify configurations.

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;

Iv. Conclusion

The failover of the availability replica depends not only on availability mode and failover mode, but also on failureconditionlevel.

 

 This article is copyrighted by the author and cannot be reproduced without the consent of the author. Thank you for your cooperation !!!

Alwayson failover Process Analysis caused by one disk I/O failure