One of the challenges you face with SQL Server database mirroring is making sure that all of the databases that are dependent on each other will failover together, so they are running on the same side of the database mirroring partnership at all times.  There is no built-in support for this with database mirroring, but SQL Server 2012 lets you use AlwaysOn Availability Groups to have multiple databases failover to a different node all together. You do need SQL Server 2012 Enterprise Edition in order to use AlwaysOn Availability Groups. If you want to get somewhat similar functionality with SQL Server database mirroring, you can try using the techniques that I will outline in this post.

First, you need to be running synchronous database mirroring on each database in order to do a manual failover. If you are going to pay the write performance penalty (due to the two-phase commit process) for synchronous database mirroring, then you really should also have a Witness instance so you can have automatic failover capability. It is really not recommended to run synchronous database mirroring without also having a witness instance, since you hurt write performance without getting the benefit of automatic failover.

These steps will help you make sure that all of your dependent databases are running on the same side of the mirror, and that they will failover together at roughly the same time (at least within 15-30 seconds of when the “main” database fails over).

Step 1: Create a stored procedure called sp_FailoverUserDatabase in the master database of the Principal and the Mirror instance of your database mirroring partnership. I use the sp_ prefix for the stored procedure name on purpose, since this SP will be in the master database. You must be running in the context of the master database when you do a database failover with ALTER DATABASE  databaseName SET PARTNER FAILOVER;

USE [master];
GO
/* sp_FailoverUserDatabase ===================================================
Description : Failover a user database from the master database
Used By: Only used to monitor the database instance               
 
Last Modified           Developer         Description 
-----------------------------------------------------------------------------
12-27-2011              Glenn Berry       Created     
=============================================================================*/CREATE PROCEDURE [dbo].[sp_FailoverUserDatabase]
(@DatabaseName nvarchar(128))
AS
    SET NOCOUNT ON;
    DECLARE @MirroringRole tinyint = 0;
    DECLARE @SQLCommand nvarchar(255);
      
      
    -- Get mirroring role for database
    SET @MirroringRole = (SELECT mirroring_role
                          FROM sys.database_mirroring
                          WHERE DB_NAME(database_id) = @DatabaseName);  
    
    -- Must be in Principal role                                  
    IF @MirroringRole = 1  -- Principal
        BEGIN
            SET @SQLCommand = N'ALTER DATABASE ' + @DatabaseName + N' SET PARTNER FAILOVER;';
            EXECUTE (@SQLCommand);
        END                                   
      
    RETURN;

Step 2: Create a new database called ServerMonitor on both the Principal and the Mirror instance of your database mirroring partnership. This database should use the Simple recovery model, and you do not want to mirror it. You want two completely separate copies of the database on each side of the mirror. You could also use an existing “Utility” type database that you might be using for storing instance level metrics.

Step 3: Create a stored procedure called DBAdminSynchronizeMirroringStatus in the ServerMonitor database of the Principal and the Mirror instance of your database mirroring partnership. You will want to modify it to use your database names.  This stored procedure will be called by a SQL Agent job.

USE ServerMonitor;
GO
/* DBAdminSynchronizeMirroringStatus =========================================
Description : Get database mirroring status for your "main" database and 
              failover appropriate databases if needed
Used By: Only used to monitor the database instance               
 
Last Modified           Developer         Description 
-----------------------------------------------------------------------------
12-27-2011              Glenn Berry       Created   
=============================================================================*/CREATE PROCEDURE [dbo].[DBAdminSynchronizeMirroringStatus]
AS
      SET NOCOUNT ON;
      DECLARE @MirroringRole tinyint = 0;
                        
      -- Get mirroring role for your "main" database
      SET @MirroringRole = (SELECT mirroring_role
                            FROM sys.database_mirroring
                            WHERE DB_NAME(database_id) = N'MainDatabaseName');   
 
      IF @MirroringRole = 2 -- Mirror
            BEGIN
                  -- MainDatabaseName failed-over, so failover other databases
                  EXEC [master].dbo.sp_FailoverUserDatabase N'DatabaseOne';
                  
                  EXEC [master].dbo.sp_FailoverUserDatabase N'DatabaseTwo';
                  
                  EXEC [master].dbo.sp_FailoverUserDatabase N'DatabaseThree';
                  -- Add more databases as needed. Make sure to change the database names!
                    
            END
                                          
      RETURN;

Step 4: Create a new SQL Server Agent job called “Synchronize Mirroring Status” that simply calls the DBAdminSynchronizeMirroringStatus stored procedure in your ServerMonitor database. Make sure this job is enabled, but do not have it running on a schedule!  You only want it to be called by the SQL Server Agent Alert that you are going to create in the next step.

Step 5: Create a new SQL Server Agent Alert to detect automatic database mirroring state changes for your “main” database. This needs to use a Windows Management Interface (WMI) query like this:

SELECT * FROM Database_Mirroring_State_Change WHERE DatabaseName = ‘MainDatabaseName’ AND State = 8  

State = 8 means that you had a database mirroring state change for that database due to an automatic failover. This could have happened because of a loss of network connectivity between the Principal instance and the Mirror instance. You will want to change the name of the database and the name of the SQL Server Agent Alert (to something like “Account Database Automatic Mirroring Change”), assuming your main database is called Account. Figures 1 and 2 show the basics for how to configure this SQL Agent Alert. You would also want to have the Alert notify your operators with an e-mail and page as you deem appropriate.

Figure 1: General tab for SQL Agent Alert

Figure 2: Response tab for SQL Agent Alert

Step 6: Create a second SQL Agent Alert job to detect manual database mirroring state changes for your “main” database. This needs to use a Windows Management Interface (WMI) query like this:

SELECT * FROM Database_Mirroring_State_Change WHERE DatabaseName = ‘MainDatabaseName’ AND State = 7

State = 7 means that you had a database mirroring state change for that database due to a manual failover. This will happen when you manually failover this “main” database. You will want to change the name of the database and the name of the SQL Server Agent Alert (to something like “Account Database Manual Mirroring Change”), assuming your main database is called Account.

So there you have it, a pretty simple way to “roll your own” Availability Group kind of functionality using database mirroring.

Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 Tagged: Database Mirroring