Shulou(Shulou.com)06/01 Report--

Enhance AlwaysOn failover strategy by testing SQL Server database data and log drives

Translator's note:

This script is suitable for deployment in replicas configured for automatic failover in SQL Server 2012\ 2014.

Preface

In SQL Server 2012 and 2014, AlwaysOn availability groups in automatic failover mode do not incorrectly trigger failover at the database level. For example, if the data or log file of the user database fails, or if the user database is corrupted and causes the state to change from online to another state (such as doubt), the database cluster does not automatically fail over the availability group.

In SQL Server 2016, we added an additional configuration setting called DB_FAILOVER in the section of the create and modify availability group command, which triggers a failover if one of the data in the master replica is offline. This means that if AG is configured for automatic failover and the database in the synchronized state encounters a failure, an error at the database level will trigger automatic failover.

Add a way to add this script so that you can have the cluster perform automatic failover in the event of a disk failure. The VB script performs file writes in the path specified in the script. If the write fails, it means that the disk fails and the script fails. A failed script causes script resources in the cluster to fail. If the dependency on script resources is properly configured, this will cause the cluster to trigger a failover due to disk / VB script failure.

Because this script does not need to connect to or use SQL Server in any way, you can deploy for SQL Server 2012\ 2014\ 2016. However, the benefit in 2016 is not as good as configuring the enable parameter DB_FAILOVER, because a database-level failure results in automatic failover.

Translated from:

Https://blogs.msdn.microsoft.com/alwaysonpro/2016/01/14/enhance-alwayson-failover-policy-to-test-sql-server-database-data-and-log-drives/

The SQL Server 2012 and 2014 Magazine AlwaysOn Health Diagnostics detect the health of the SQL Server process in a variety of ways. However, there is no health test for the accessibility or viability of databases on AlwaysOn availability groups. If the disk hosting the availability group database or log file is lost, AlwaysOn Health Diagnostics does not detect the event and the application fails to access the database incorrectly at run time. Missing drives or errors accessing drives that host availability database data and log files can affect access to your production data.

As described in the elastic failover policy for automatic failover for availability groups:

"corrupted databases and questionable databases are not detected at any failure condition level. as a result, a database corruption or doubt (whether due to hardware failure, data failure, or other problems) will never trigger automatic failover."

Note: SQL Server 2016 enhances AlwaysOn health diagnosis for database health testing. If your AlwaysOn availability group turns on database health testing and the database state is converted to a non-ONLINE state (sys.databases.state_desc), the entire availability group will fail over automatically. You can refer to MSDN's "DB_FAILOVER" section on "CREATE AVAILABILITY GROUP".

You can enhance the availability of the database by checking for disk health. Add a normal script resource to your availability group resource group and do basic read or write tests on the drive that hosts the availability group database data and log files. The following describes how to add a normal script resource as a dependency on an availability group resource to enhance AlwaysOn health detection through a disk health check for toluene.

Use a normal script resource to do basic SQL Server data and log drive health checks

This is a high-level description of deploying common scripting resources to detect availability group database drive health.

Add a normal script cluster resource to the availability group resource group. Make availability group resources dependent on normal resource scripts. This way, if the script resource reports an IsAlive failure, the Windows cluster attempts to restart or fail over the availability group resource.

The normal script cluster resource IsAlive test creates a text file at a specific data drive location and a text file at a specific log drive location. If the file exists, the script will overwrite it.

The script is packaged as GenericScript_SQLIsAlive.zip, including:

Sqlisalive.vbs

This normal script is written in a Visual Basic script and deploys the Windows cluster IsAlive.

Add_SQLIsAliveScript.ps1

This PowerShell script adds normal script resources to your availability group resource group, and sets the available groups to depend on your normal script resources.

Readme.txt

Step-by-step guidelines for deploying regular script resources, and additional guidance on how to test scripts.

Deploy normal script resources

i. Configure the normal script sqlisalive.vbs.

Data and log drive paths:

Currently, normal scripts are configured to test the drive and path: C:\ temp\ data and c:\ temp\ log. For testing purposes, create these paths (primary copy and automatic failover partner secondary copy) on the local drive of each copy. After that, you can modify them to the appropriate drive and path where your database data and log files are located.

DataDriveFile= "c:\ temp\ data\ ScriptFileData.txt"

LogDriveFile= "c:\ temp\ log\ ScriptFileLog.txt"

ii. Configure and execute PowerShell scripts to deploy normal scripts to your usability group.

Note: this normal script only deploys IsAlive that runs every 60 seconds.

1. Make sure that your availability group has two copies configured for automatic failover.

two。 Copy normal script files to the same local storage path, like "C:\ temp\ sqlisalive.vbs" for all servers configured for automatic failover replicas.

3. Create a path for health check, C:\ temp\ data and C:\ temp\ log.

4. The Add_SQLIsAliveScript.ps1 script adds a normal script resource to your usability group, and adds a usability group resource that depends on you to the normal script resource. In Add_SQLIsAliveScript.ps1, modify the following variables:

Set $ag to your availability group name.

Set $listener to your availability group listener name. If your usability group does not have a listener, set $listener to "".

Set $scriptfilepath to the path and file name of your sqlisalive.vbs script.

5. On the server that hosts the master copy, run the PowerShell script Add_SQLIsAliveScript.ps1 to add normal script resources to your availability group resource group.

6. Open failover Cluster Administrator and check the availability group resource group to confirm that normal script resources have been added to the availability group resource group. The normal script should appear on the resource page and be online in the availability group resource group.

7. Verify that dependencies are created on normal script resources in the availability group resource.

Note:

The additional readme.txt contains guidelines on how to test the script resource to make sure it can fail over to your availability group resource.

Failure detection through normal script resource diagnosis

Generate a cluster log for the node hosting the master copy and search for "Data Drive Create File" or "Log Drive Create File" to locate the report of the success or failure of the normal script resource IsAlive:

00001b04.00002924::2015/12/07-17VOV 16VOU 41.798 INFO [RES] Generic Script: Entering IsAlive

INFO [RES] Generic Script: Data Drive Create File Succeeded 00001b04.00002924::2015/12/07-17VOV 16V 41.801

INFO [RES] Generic Script: Log Drive Create File Succeeded 00001b04.00002924::2015/12/07-17VOV 16V 41.801

Or, for example, you set the\ Data directory to read-only:

00001b04.00002924::2015/12/07-17 Generic Script: Entering IsAlive 41.801 INFO [RES]

00001b04.00002924::2015/12/07-17 Generic Script: Data Drive Create File Succeeded 41.804 INFO [RES]

00001b04.00002924::2015/12/07-17 Generic Script: Data Drive Create File Failed 41.804 INFO [RES]

00001b04.00002924::2015/12/07-17 Generic Script: Permission denied 41.804 INFO [RES]

00001b04.00002924::2015/12/07-17 RES 17 RES 41.804 ERR [RES] Generic Script: 'IsAlive' script entry point returned FALSE.'

00001b04.00002924::2015/12/07-17 RES 17 RES 41.804 Generic Script: Return value of 'IsAlive' script entry point caused HRESULT to be set to 0x00000001.

00001b04.00002298::2015/12/07-17 RHS 17 RHS 41.804 Resource sqlisalive IsAlive has indicated failure.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.