Mirror automatically fail back to its original Principal Server

Automatic failover only occurs as a result of the current Principal being offline. Database Mirroring does not have any mechanisms built in to set one server as the default active server. When the Mirror partner takes over as the active Principal partner, it will continue to serve as Principal until a failure triggers another automatic failover or a manual failover is performed.
You can set up this functionality on your own, however. It’s a simple process. Create a procedure to check the state and role of your mirror partners and manually fail them back to the original Principal if it is connected and in a synchronized state. Then set up a job to run this procedure every minute. Once the original Principal is back online and fully synchronized, the job will fail the server back within one minute.

I have tested this code and working fine in my environment but still please test this code in your test servers first before going to Implement in your Production 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 job to run 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.