January 23, 2013 at 8:50 pm
Comments posted to this topic are about the item Grouped Failover, a 2008R2 version of Availability Groups
January 27, 2013 at 1:59 am
A usefull script to have,especially with "all or nothing" apps like sharepoint.
Thx for sharing 🙂
March 21, 2013 at 1:18 pm
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
May 2, 2016 at 4:23 pm
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply