SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouped Failover, a 2008R2 version of Availability Groups


Grouped Failover, a 2008R2 version of Availability Groups

Author
Message
Remko de Boer
Remko de Boer
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 239
Comments posted to this topic are about the item Grouped Failover, a 2008R2 version of Availability Groups
Theo Ekelmans
Theo Ekelmans
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1840 Visits: 816
A usefull script to have,especially with "all or nothing" apps like sharepoint.

Thx for sharing Smile
unaur
unaur
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 207
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
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32054 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search