x

How to get alert when the SQL Server failover occurs ?

Hi ,

Is there any way to get the alert without using any third party tool whenever the SQL Server Failover occurs from one node to other...

Thanks

Basit Khan
more ▼

asked Aug 22, 2012 at 01:57 PM in Default

basit 1 gravatar image

basit 1
499 51 61 85

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Step 1 : First create table with name "CLUSTERFAILOVERMONITOR" with one column "PreviousActivenode"

Step 2 : Insert the value of Current active node

Step 3 Create the procedure which check the value of current active node and value from the table if it found difference it will send the alert

Create proceudre Clusterfailovercheck 
as 
Declare @var1 varchar(30)
SELECT @var1= PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR

-- Creating the table which store the current active node value

CREATE TABLE PHYSICALHOSTNAME
(
VALUE VARCHAR(30),
CURRENT_ACTIVE_NODE VARCHAR(30)
)

INSERT INTO PHYSICALHOSTNAME
exec master..xp_regread 'HKEY_LOCAL_Machine',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
'ComputerName'

declare @var2 varchar(30)
SELECT @VAR2=CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME

if @VAR1<>@VAR2

Begin

EXEC msdb..sp_send_dbmail @profile_name='DBAMail',
@recipients='basit.khan@company.com',
@subject=' Failover occurrence notification - InstanceA,
@body='Cluster failover has occured for instance InstanceA. Below given are the previous and current active nodes.',
@QUERY='SET NOCOUNT ON;SELECT PREVIOUS_ACTIVE_NODE FROM CLUSTERFAILOVERMONITOR;SELECT CURRENT_ACTIVE_NODE FROM PHYSICALHOSTNAME;SET NOCOUNT oFF'

update CLUSTERFAILOVERMONITOR set PREVIOUS_ACTIVE_NODE=@VAR2

End

DROP TABLE PHYSICALHOSTNAME

Step 4 : Create a job which execute the procedure at any specific time what you need....

more ▼

answered Aug 24, 2012 at 12:15 PM

basit 1 gravatar image

basit 1
499 51 61 85

(comments are locked)
10|1200 characters needed characters left

There are a couple of methods.

You could create a startup stored procedure that executes when SQL restarts which captures the restart and emails out to you, or create it as a SQL Agent job that runs on startup. IF you go with the proc ensure that you put in a wait for 10 seconds or you might try to send the email before the email engine has actually started.

Here's basic code to send the information on the restart and include the node that the instance is currently running on (you will need database mail already configured)

DECLARE @node VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

DECLARE @header VARCHAR(128) =  'SQL Server '+ UPPER(@@servername) + ' restarted!'
DECLARE @Message VARCHAR(max) = 'SQL Server '+ UPPER(@@servername) + ' restarted.

Now running on node: ' + @node 


exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile
     ,  @recipients = 'youremail@yourcompany.com' 
     ,  @subject = @header
     ,  @body =  @message
     ,  @body_format = 'HTML' --default is TEXT
more ▼

answered Aug 22, 2012 at 02:44 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x92
x11

asked: Aug 22, 2012 at 01:57 PM

Seen: 1432 times

Last Updated: May 17, 2013 at 02:04 AM