SQL Server Database Mirroring Tips and Tricks, Part 3

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.

image

Figure 1: General tab for SQL Agent Alert

 

image

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.

This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and tagged . Bookmark the permalink.

12 Responses to SQL Server Database Mirroring Tips and Tricks, Part 3

  1. csm says:

    I think it’s better create a table where you could map the relations between databases, so instead of write several times the execution of sp_FailoverUserDatabase, just create a cursor to read this table and execute this sproc with the name of the secondary database that the principal depends on

  2. Hi Glenn,
    First of all, thanks for your posts about database mirroring.

    In my case, I want ZERO data loss to the databases and because of third-party application, I can’t implement automatic failover.

    So, I think that is a good choice to use synchronous database mirroring without Witness.

    To implement ZERO data loss in ambients that you can’t change de application, I think this is a good choice.

    What do you think?

    • Glenn Berry says:

      You are paying a write performance penalty with synchronous mirroring, but if your network latency is good, and the I/O performance for the log drive on the mirror is good, it should not be too much of a hit. Still, I am not too sure why you cannot add a witness instance to your mirroring partnership, even with a 3rd party application.

  3. Great Stuff Glen, thanks!

  4. spe109 says:

    Thanks Glenn, nice article, really simple and easy to follow.

  5. Oliver Holloway says:

    Glenn, while your point about witnessing if synchronous is interesting, in my own experience in mirroring a dozen instances and hundreds of databases, there are scenarios where doing so simply does not work for the data owners. For example, if mirroring is being done for a third-party app, and there is no facility to completely automate the repointing of the app, then there is a need to manually fail over the app (for example, by masquerading DNS or perhaps having to manually alter a config file). Only two of my instances are witnessed, and frankly, they give me the most guff because the data owners are convinced they like that 10-second default timeout, even though the network has the occasional thirty-second blip. With respect, perhaps the brush is too broad.

  6. Hello Glenn,

    I’ve played around with the WMI event and the status 8 (Automatic Failover with Witness). When I just turn off the VM, where the current principal is running, the WMI event is only raised on the (old) mirror. But how can I do a “ALTER DATABASE xyz SET PARTNER FAILOVER” on the mirror. I have to do that on the principal, which was just shutdown… Do I miss here anything?

    Thanks

    -Klaus

    • Glenn Berry says:

      If you shut down the VM where the original principal is running (or simply shut-down the SQL Server Service), then the Witness and the original Mirror should notice this and start an automatic failover. My technique is meant to initiate a failover after a failure that does not take down the entire server or the SQL Server Service, such as losing a drive where the SQL Server data files for a mirrored database are located.

      • Hello Glenn,

        Thanks for your answer. This means your approach works only for a manual failover? If yes, are there any other ways to failover the other databases in the case of an automatic failover? I know that the answer is no, but maybe you have a trick😉

        Thanks

        -Klaus

  7. Glenn Berry says:

    Klaus,

    I create two SQL Server Agent Alerts. One for a manual failover and one for an automatic failover. This is designed to give you similar functionality to a SQL Server 2012 AG. The idea is if you get a manual failover or an automatic failover of a particular mirrored database, the Alert will fire, and then you can have it call an Agent job that calls a stored procedure in the master system database that fails over the other databases that you want to also failover. This should do what you are asking for. It worked well for me at Avalara.

  8. John says:

    I need to sync logins with users in databases after Availability Group Faiover. I have been looking but have not yet found similar WMI events for Availability Groups. Do you know if there are any? I can not use contained databases as I also need to run transactional replication from and to the two Availability Groups.

    • Glenn Berry says:

      If you can use Windows authentication, this is not an issue. If you must use SQL Server authentication, I like to create logins on each have the same SID, so that there is no isue with orphaned users after a failover.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s