Printed 2017/01/16 08:57AM

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

By Robert Davis, 2009/02/22

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
@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
     Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'

     Exec sp_executesql @SQL;

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.