:::: MENU ::::

Failover all mirrored databases as a group

Ok, when using plain old database mirroring, I know, it’s old and almost obsolete now, there is no group failover so that all databases can be failed over if one goes, what I did was set up an alert that checks if one database has failed over, and that would start a simple agent job that failed over the rest.

There are a lot of ways to configure this, read up on it at https://technet.microsoft.com/en-us/library/cc966392.aspx

We opted to use the option of checking if a manual failover had been done, that is mirroring session state change 7. If your database mirroring is set up with automatic failover, than also add a check for state 8.

I’ll just copy in an example below where we have 3 databases, named DB1, DB2 and DB3, yeah I know, awesome imagination right? :-)

Alert is checking for a manual failover, thus state 7.

Create the alert and job on test servers first, do a couple of manual failovers to see that all databases failover. They failover serially, in the order set in the agent job, so depending on their size and unrestored transaction log it can take a while.


-- First we create the alert on both servers holding the mirrored databases

USE [msdb]
GO

/****** Object: Alert [Failover Databases] Script Date: 02/17/2016 10:32:05 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Failover Databases',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 AND

(DatabaseName = ''DB1'' OR DatabaseName = ''DB2'' OR DatabaseName = ''DB3'')',
@job_id=N'f5e58bbe-b574-477f-a3ec-b596c6766a22'
GO

-- Next we create the job, also on both servers.

USE [msdb]
GO

/****** Object: Job [Failover Databases] Script Date: 02/17/2016 10:33:15 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/17/2016 10:33:15 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Failover Databases',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Failover databases',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Failover] Script Date: 02/17/2016 10:33:15 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failover',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N''DB1''

AND mirroring_role_desc = ''PRINCIPAL'')
ALTER DATABASE DB1 SET PARTNER FAILOVER
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N''DB2'' AND mirroring_role_desc =

''PRINCIPAL'')
ALTER DATABASE DB2 SET PARTNER FAILOVER
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N''DB3'' AND mirroring_role_desc =

''PRINCIPAL'')
ALTER DATABASE DB3 SET PARTNER FAILOVER
GO',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Good luck!
//Richard


So, what do you think ?