Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Mirroring FAQ: Will the Mirror automatically fail back to the original Principal when it comes back online?

Question: Will the Mirror automatically fail back to the original Principal when it comes back online?

This question doesn't usually come up until someone has experienced an unplanned failover and something that they forgot about has been failing as a result. Hopefully this question will start coming up while planning for database mirroring.

My answer:

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.

The procedure:

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;

Please Note:

You may wonder why I use dynamic SQL for this procedure rather than just hard coding the database name. If you hard code the database name and try to create the procedure while the database is in the mirror role, it will fail because it will be referencing a database that is offline.

Comments

Posted by sbarak on 23 March 2010

Probably a stupid question, but are we supposed to modify any part of your script with our actual database name?

I tried executing the procedure as is and it returns an error.

Posted by sbarak on 23 March 2010

OK it was a stupid comment, figured it out. Nice script, Thanks!

Posted by Robert Davis on 25 March 2010

Thanks for the feedback!! I'm glad you found it to be helpful!!

Posted by andrew.may on 24 August 2010

Brilliant piece of code, thanks very much!

Posted by isc_fausto on 6 February 2012

Seems like two years late for this party, but this is really helpful! (so helpful that I decided register and leave a comment).

Posted by Mikael Hallin on 8 October 2012

Very helpful article! Thanks!!!

Posted by yuriy.petrenko on 18 December 2012

Hi, sorry for being complete beginner. Could someone clarify if anything needs changing in this script, if yes what exactly? I can't get it working.

Thanks

Posted by yuriy.petrenko on 18 December 2012

Hi, sorry for being complete beginner. Could someone clarify if anything needs changing in this script, if yes what exactly? I can't get it working.

Thanks

Posted by yuriy.petrenko on 18 December 2012

Ignore, my comments, managed to get it working. For someone as noob as me, this needs to be declared and executed in master db.

Posted by alanspeckman on 5 February 2014

DECLARE @SQL NVARCHAR(MAX) = N'';

Select @SQL += CHAR(13) + CHAR(10) + 'ALTER DATABASE '

+ QUOTENAME(d) + ' SET PARTNER FAILOVER'

FROM

(select d = dbs.name

from

sys.database_mirroring dm

join sys.databases dbs

on dm.database_id = dbs.database_id

where dbs.database_id > 4

and dm.mirroring_role = 1

and dm.mirroring_state = 4) as x

EXEC sp_executesql  @SQL

Posted by alanspeckman on 5 February 2014

You don't need this "dbs.database_id > 4", but I put it there for clarity.

Leave a Comment

Please register or log in to leave a comment.