locked
Launch Script (T-SQL code) when failover occured RRS feed

  • Question

  • Hi,

     We've been using mirroring on our database and it always worked fine but because of some third party software i now need tthe possibility to launch script when failover occured. Here is the problem, we have a third party application that create planning for months to come. This application can run for up to 10 hours and while it's running it fill some SQL table. Last week because of a power outage we got a failover. When it occured that third party software was running.  I've talked with the company who made that third party and to resume the process i first need to update some table and then relaunch their EXE.

    So here what i've done so far. I've created a ALERT and using WMI EVENT ALERT i'm doing something like

    SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8
    

    Then I've linked to that alert a JOB. That JOB currently has 2 steps. First i'm doing this to update their required fields:

    DECLARE @MyRole as int
    DECLARE @BD_Ready as varchar(20)
    SET @BD_Ready = ''
    
    SELECT @MyRole = mirroring_role
    FROM sys.database_mirroring 
    WHERE database_id = DB_ID('DB_PROD')
    
    IF @MyRole = 1
    BEGIN
    	SELECT @BD_Ready = state_desc FROM sys.databases WHERE name = 'DB_PROD'
    	WHILE @BD_Ready <> 'ONLINE'
    	BEGIN
    		SELECT @BD_Ready = state_desc FROM sys.databases WHERE name = 'DB_PROD'
    		WAITFOR DELAY '00:00:15'
    	END
    	--CODE FOR UPDATE TABLE PROCESSING
    END
    
    

    Secondly i'm calling the EXE.

    So far it work but not all the time. The part where the JOBS call the EXE always work all the time. My problem is the first step where i'm trying to update the table. When doing a manual failover it never works but if i tried for example to "simulate" a network lost connection then it's working fine. So i was wondering did i'm doing the right thing ? At first i had some problem because when the UPDATE was occuring the database was not online and ready so that's why i've added the WHILE LOOP but it's probably not the best solution. Is there a way to know when the mirror is up and running and then launch my JOBS ?

    Many thanks,

    Thursday, May 19, 2011 2:50 PM

Answers

  • Can't you create SQL Job on both Principal and mirroring server and enable or disable SQL Job upon db failures occuring. here is nice article to enable and disable SQL Agent job on mirroring failovers

    http://blogs.msdn.com/b/sqlcat/archive/2010/04/01/using-sql-agent-job-categories-to-automate-sql-agent-job-enabling-with-database-mirroring.aspx

     

     


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Sianspheric Thursday, May 19, 2011 5:23 PM
    Thursday, May 19, 2011 3:15 PM

All replies

  • Can't you create SQL Job on both Principal and mirroring server and enable or disable SQL Job upon db failures occuring. here is nice article to enable and disable SQL Agent job on mirroring failovers

    http://blogs.msdn.com/b/sqlcat/archive/2010/04/01/using-sql-agent-job-categories-to-automate-sql-agent-job-enabling-with-database-mirroring.aspx

     

     


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Sianspheric Thursday, May 19, 2011 5:23 PM
    Thursday, May 19, 2011 3:15 PM
  • Thanks you very much RamJaddu.  I've put in place your idea and it works great ! Much more simple than what i was trying to do.

    Thursday, May 19, 2011 5:23 PM
  • Great....its pleasure to share the knowledge
    http://uk.linkedin.com/in/ramjaddu
    Thursday, May 19, 2011 6:29 PM