Post reply

script to get an alert when fail over occurs

  • November 10, 2009 at 10:37 am

    #213770

    Hi,

    We 3 node A/A/P cluster setup for SQL Server 2005. We had a failover yesturday and I get to know today morning while going through event viewer errors.

    Is there any script to get an alert when fail over occurs?

    thx

  • November 10, 2009 at 11:52 am

    #1077421

    That's usually set up at the server level, not through a script. I'm used to clustering being monitored by the servers involved. Don't think I've ever seen it done by a script.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 10, 2009 at 1:07 pm

    #1077468

    We implemented this by using an auto startup stored procedure that reads the computer's physical name and compares it to the last record stored in a table. If the names are different, it sends out an email to DBA to let them know that the server failed over. It then inserts a row into the table to track the current server.

    SELECT 'Running on Cluster Node: ' + CONVERT(VARCHAR(50),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

  • November 11, 2009 at 12:33 pm

    #1077990

    SERVERPROPERTY will work fine for a MS cluster, but for VERITAS cluster setups it returns the lanman name, we use the below script to get the physical node name, we have mixed setup.

    create table #Host (

    id tinyint identity(1,1),

    Ping_Txt varchar(255))

    Insert #Host (Ping_Txt)

    exec master.dbo.xp_cmdshell 'ping localhost -n 1'

    select @@servername, upper(substring(Ping_Txt,9,charindex('[',Ping_Txt)-10)) as Host,

    Ping_Txt from #Host where id=2

    drop table #Host

    Andrew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply