Grouped Failover, a 2008R2 version of Availability Groups

  • Comments posted to this topic are about the item Grouped Failover, a 2008R2 version of Availability Groups

  • A usefull script to have,especially with "all or nothing" apps like sharepoint.

    Thx for sharing 🙂

  • Hi

    you can actually automate this process, i mean when a failover happens we can trigger to failover rest of the mirrored dbs in the group.

    try to modify little bit and create scheduled job that runs every min with this script. let me know if you find any issues.

    ----------------

    set nocount on;

    declare @ctime datetime

    declare @ptime datetime

    declare @cstate tinyint

    declare @pstate tinyint

    declare @groupid int

    declare @actionid int

    declare @i int = 1

    declare @total int

    declare @database_id int

    set @actionid = 0;

    Declare @html varchar(250)

    Declare @srv_name varchar(100)

    declare @role varchar(20)

    declare @status nvarchar(500)

    declare @Mirrored_databases table (Id int Identity(1,1), databaseid int, Is_failedover bit);

    Insert into @Mirrored_databases (databaseid, Is_failedover)

    select database_id, 0

    FROM sys.database_mirroring with (nolock)

    where mirroring_state is not null

    select @total = COUNT(*) from @Mirrored_databases;

    select @ctime = MAX(local_time)

    from [msdb].[dbo].[dbm_monitor_data] with (nolock)

    select @ptime = MAX(local_time)

    from [msdb].[dbo].[dbm_monitor_data] with (nolock)

    where local_time <= DATEADD(minute,-1,@ctime)

    WHILE (@i <= @total)

    BEGIN

    SET @database_id = -1 -- reset each time

    SELECT @database_id = databaseid from @Mirrored_databases where Id = @i;

    SET @cstate = 255; -- reset each time

    SET @pstate = 255; -- reset each time

    select @cstate = [role]

    from [msdb].[dbo].[dbm_monitor_data] with (nolock)

    where local_time = @ctime and database_id = @database_id

    select @pstate = [role]

    from [msdb].[dbo].[dbm_monitor_data] with (nolock)

    where local_time = @ptime and database_id = @database_id

    IF (@cstate = 1) AND (@pstate = 0)

    Update @Mirrored_databases

    set Is_failedover = 1

    where Id = @i;

    SET @i = @i + 1;

    END

    IF EXISTS (select 1 FROM @Mirrored_databases where Is_failedover = 1)

    BEGIN

    -- calculate your group id based on failedover db

    -- complete this section

    exec dbo.spFailoverGroup @groupid = @groupid, @actionid = @actionid

    -- also excute any other maintenance jobs to run after failover like disable/enable jobs, change connection strings etc

    END

  • Thanks for the script.

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

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