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.
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.
Create Procedure dbo.dbm_FailoverMirrorToOriginalPrincipal
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
Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'
Exec sp_executesql @SQL;
Set NoCount Off;
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.