How to stimulate a SQL Server resource failure in cluster

How to stimulate a SQL Server resource  failure in cluster?

If you like to perform a check in SQL Server cluster to identify if the SQL Server resources are failing over properly follow the below steps. you can use Step 2 to create a SQL Server hang and deadlocked scheduler dump in standalone server as well.

 

 

1.       Create a instance failures scenario by  issuing “shutdown” command to SQL Server which will terminate/Shutdown SQL Server. SQL Server resource DLL in cluster should detect that the SQL Server have failed and start the SQL Server resource automatically.

 

Steps

a.       Connect to SQL Server using an account which has Sysadmin privilege and execute Shutdown command.

 

 

2.       Create a SQL Server hang scenario by creating excessive blocking, once SQL Server exhaust all the available threads SQL Server will stop accepting connections and will go unresponsive. SQL Server resource in the cluster should detect that the SQL Server resource have failed and restart/failover SQL Server.

Steps

a.        Connect to SQL Server and below SQL Statements. (Do not close the session)

 


Create database test;
use test;
create table block  (a char(10));
insert into block values ('Test');
go 10
begin transaction;
update block set a ='Test1'

b. Download the Ostress.exe

c. Execute the following command in ostress.exe

ostress.exe -Sservername\Instancename -E -d test -Q "select * from test..block" -n 2000

Note: Replace servername\instancename with your server name and instance name. Above Ostress command creates 2000 connections and execute “select * from tempdb..block” which will in turn cause excessive blocking and make SQLServer exhaust all the available worker threads.

Is Alive check will fail once the threads are exhausted causing SQL Server to reach hung state. SQL Server resource DLL’s should detect that SQL Server is hung and restart/failover SQL Server.

 If you do not know what is SQL Server LooksAlive and IsAlive Check follow This blog

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s