Mirror automatically fail back to its original Principal Server

The Automatic failover in Database Mirroring occurs as a result of the current Principal being offline. This Database Mirroring don’t have any mechanisms built in to set one server as the default Principle or active server. Whenever the Mirror server takes over the role of active Principal partner, it will continue to serve as Principal until a failure triggers that means another automatic failover or a manual failover should be performed.
We can set up this functionality by our own. Create a Stored procedure to check the state and role of your mirror server and manually fail them back to the original Principal Server if it is Active or connected and in a synchronized state. Then set up a job to run this procedure every minute or as per your environment . Once the original Principal is Active or online and also is fully synchronized, then the job will fail back the server within one minute.

Please do test this code in your test servers first before going to Implement in your Production Environment I tested this code and is working fine in my environment

Create this Stored Procedure on Mirror Server :

USE [master]
GO

/****** Object: StoredProcedure [dbo].[dbm_FailoverMirrorToOriginalPrincipal] Script Date: 07/05/2013 12:09:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[dbm_FailoverMirrorToOriginalPrincipal]
@DBName sysname
As
Declare @SQL nvarchar(200)

Set NoCount On;

/*
If database is in the principal role and is in a synchronized state
then fail database back to original principal
*/
If Exists (Select 1 From sys.database_mirroring
Where database_id = db_id(@DBName)
And mirroring_role = 1 -- Principal partner
And mirroring_state = 4) -- Synchronized
Begin
Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

Exec sp_executesql @SQL;
End

Set NoCount Off;
GO

Use below code to set up a SQL Agent job by running above store procedure for every five minutes ( depends on your Environment ) on Mirror Server to Failback the Database to it Original Principle Instance:

USE [msdb]
GO

/****** Object: Job [FailoverToPrimary] Script Date: 07/05/2013 11:59:52 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 07/05/2013 11:59:52 ******/
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'FailoverToPrimary',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [DBFailover] Script Date: 07/05/2013 11:59:53 ******/
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'exec dbo.dbm_FailoverMirrorToOriginalPrincipal ''DatabaseName''
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_jobschedule @job_id=@jobId, @name=N'every5min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20120401,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'362d2815-5471-4fc6-a030-2581c9a836c5'
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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.