SQL Server 2008 – Setting a Default Principal Server

sql serversql-server-2008-r2

This should be a pretty simple answer but I can't find anything on it searching the web.

SQL Server 2008 R2, mirrored with a witness:

  • Node1 (Principal)
  • Node2 (Mirror)
  • Node3 (Witness)

If Node 1 fails, Node 2 takes over as it should, however when Node 1 recovers, Node 2 remains the principal.

Is there a way to make Node 1 the primary/default server so that when it's available, it's always the principal server?

Best Answer

This is not an out of the box feature of SQL Server. And here's why:

After a fail-over scenario, when the formerly primary server is back online, that doesn't necessarily mean that it should be put back into the primary role. Just because the server is online doesn't mean it's necessarily totally healthy from a hardware/software perspective (after all, it just went down). What if the issue is going to happen again in an hour? Do you really want to be playing ping pong with mirroring nodes every time the primary is down? There should be a manual QA of the node before putting it (preferably after business hours) back into the primary role.

Simply put: failing over automatically outside of a disaster situation adds unnecessary risk to your database up-time.

This article on SQL Server Central provides the following stored procedure to facilitate the action you describe, but I'd caution against doing it once again.

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;
Related Question