Glenn Berry's SQL Server Performance

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.

Even though SQL Server Database Mirroring is deprecated in SQL Server 2012, it is still a very useful HA/DR technique that should work perfectly well for at least two-three major versions of SQL Server after the newly released SQL Server 2012. Microsoft’s deprecation language actually says that “the following SQL Server Database Engine features are supported in the next version of SQL Server, but will be removed in a later version. The specific version of SQL Server has not been determined.”  Based on past experience, this probably means you have between five to eight years before database mirroring is not supported in a  yet to be determined later version of SQL Server. It will continue to work in all prior versions of SQL Server, just as you would expect. Having said all of this, I want to make it very clear that if you are upgrading to SQL Server 2012, you will be using Enterprise Edition, and your database servers are in the same Windows Domain, then AlwaysOn Availability Groups is a much more flexible and robust solution compared to database mirroring. You should definitely be looking at AlwaysOn as a replacement for database mirroring and log shipping in that scenario.

If you are going to be using SQL Server 2008 R2 or below for a while, then you should not be afraid to use database mirroring as a part of your HA/DR solution (if it makes sense on its own merits), even though it has been deprecated in SQL Server 2012! Really though, you should be designing your HA/DR strategy based on your Recovery Point Objective (RPO), Recovery Time Objective (RTO), and available budget and resources, rather than based on your favorite technology. Fellow SQL Server MVP Paul Randal wrote an excellent Microsoft whitepaper called “Proven SQL Server Architectures for High Availability and Disaster Recovery”, that you can download from here.  I highly recommend that you read it if you are planning and designing an HA/DR architecture.

Assuming that database mirroring is something that is going to be a part of your HA/DR solution for a while, how do you actually get it designed and implemented, and how do you manage it with a demanding, real-life workload?  I hope to give you some guidance on all of this over a series of blog posts on the subject. How you specifically setup database mirroring depends on your overall HA/DR architecture, and your RPO/RTO requirements. For example, if you are trying to get some HA benefit from database mirroring (by itself), then you pretty much have to use synchronous database mirroring with a witness instance, since that is the only way you can get automatic failover of your databases. If you are looking to use database mirroring between geographically dispersed sites as part of your DR solution, then you would be more likely to be looking at asynchronous database mirroring (which requires Enterprise Edition).

For the purposes of this first post, let us assume that you want to have a good HA story within a single data center to start with, so you decide to use synchronous database mirroring with a witness instance. Here are a dozen checklist items that you need to consider as you are designing and implementing your database mirroring infrastructure:

1. The “principal instance” and the “mirror instance” should use identical hardware
2. Your database servers should be configured using standard hardware redundancy techniques
3. The “principal instance” and the “mirror instance” should use identical, but separate I/O subsystems
4. Your drive letters and directory structure for your SQL Server data files and SQL Server log files should be identical
5. You should be running the same version, edition, and build of SQL Server on both sides of the mirroring partnership
6. For simplicity, all members of the mirroring partnership should be in the same Windows Domain
7. All members of the mirroring partnership need to be able to ping each other, and they should be able to communicate on port 1433 and port 5022 (by default)
8. The “principal instance” and the “mirror instance” should have Windows Instant File Initialization enabled
9. You should get your VLF counts and log file autogrow sizes under control for all of the user databases that you want to mirror
10. All of the user databases that you want to mirror have to use the Full recovery model at all times
11. Make sure to get your index maintenance situation in good shape before you mirror a database
12. Use backup compression for your full and log backups

Here is a little bit of explanation of these guidelines:

The “principal instance” and the “mirror instance” should use identical hardware

The mirror instance could potentially be running your entire workload for an extended period in the event of a failover due to hardware failure, OS failure, etc. More importantly, if the mirror instance is severely underpowered compared to the principal instance, it can slow down your synchronous mirroring commits (although the main bottleneck is the write performance of the transaction log file drive on the Mirror).

Your database servers should be configured using standard hardware redundancy techniques

Regardless of what else you are doing in terms of HA/DR efforts, you should make sure to do the “standard” hardware HA techniques on each individual database server. These include things like installing the OS on a RAID 1 array with a dedicated hardware RAID controller, having dual power supplies plugged into different circuits, having multiple network connections plugged into different switches, having redundant paths for your storage, using RAID 10 for your storage, etc. This reduces  the chances of any single failure bringing down a server or causing data loss.

The “principal instance” and the “mirror instance” should use identical, but separate I/O subsystems

If you want or need to use synchronous mirroring, you need to make sure that the storage subsystem on the mirror instance can keep up with the I/O demands that it will see as a result of database mirroring. This is particularly important for the logical drive where your transaction log file is on the mirror instance. Having an inadequate I/O subsystem on the mirror will slow down all of your write operations on the principal server. I call that “dragging around a boat anchor”, and it is no fun at all! Make sure you don’t have the principal and mirror instances using the same SAN for both sides!  No matter what SAN vendors tell you, SANs can and do fail. If you are in a SAN shop, you really need both sides of the mirroring partnership to use a completely different SAN (which may be quite expensive). Personally, I have had very good results using high performance DAS on both sides of the mirroring partnership.

Your drive letters and directory structure for your SQL Server data files and SQL Server log files should be identical

While it is possible to restore a database to initialize database mirroring using the MOVE option, it is not a good idea. Where this will be a problem is if you decide to add a new database file to the Principal some time in the future. Since all DDL commands on the Principal get replayed on the Mirror, if you create a new file in a mirrored database using a drive letter or path that does not exist on the Mirror, your database will go into a suspended status until you can duplicate the path on the Mirror. You really should have a standardized drive letter and directory structure for all of your SQL Server instances. For example, I like to use L:\SQLLogs for log files, P:\SQLData for data files, T:\TempDB for TempDB files, etc.

You should be running the same version, edition, and build of SQL Server on both sides of the mirroring partnership

While you can mirror from older versions to newer versions (and then make a one way, one time failover from old to new to upgrade), you really should be on the same major version, service pack, and build of SQL Server on both sides of the database mirror. Both sides of the mirror have to be on the same edition of SQL Server. While the Witness instance can be running Express Edition, I like to also use that instance for other things, like a Remote Distributor for replication, a central management server, etc., which means you cannot use Express Edition for the Witness instance.

For simplicity, all members of the mirroring partnership should be in the same Windows Domain

It is possible to set up database mirroring across different domains using certificates, but that is fairly complicated. Using different domains also makes it nearly impossible to do something like running a database backup from the Principal directly to a file share on the Mirror (if you are using a domain account for the SQL Server Service account on both sides of untrusted domains).

All members of the mirroring partnership need to be able to ping each other, and they should be able to communicate on port 1433 and port 5022 (by default)

Database mirroring uses pings to check network connectivity between the members of the mirroring partnership. It also uses TCP port 5022 for the database mirroring endpoint (although you can use a different port if you want to). If these ports are not open between the servers, you are not going to be able to setup a mirroring partnership, end of story. I would also say, no matter what your network or operations staff tells you, you should trust but verify!  You can create a new, empty database on your Principal, and then try to mirror it to your Mirror instance. This is a lot quicker and easier than going though the work to mirror a large production database only to find out that you cannot do it because a port is blocked.

The “principal instance” and the “mirror instance” should have Windows Instant File Initialization enabled

This allows the required full database restore to initialize your mirror to go much, much faster. It also lets you grow data files much more quickly. This only works for SQL Server data files, and it is done by granting the “Perform volume maintenance tasks” right to the SQL Server Service Account on the instance, using the local Group Policy Editor. You will have to restart the SQL Server Service for this to go into effect.

You should get your VLF counts and log file autogrow sizes under control for all of the user databases that you want to mirror

Having a high VLF count in your transaction log file will slow down write performance and make database restores take a lot more time. If your VLF count is more than a couple of hundred, I would go through the easy steps to take care of that before you mirror your database. You also want to set your autogrowth increment for the log file to an appropriate size to keep your VLF counts from surging in the future.

All of the user databases that you want to mirror have to use the Full recovery model at all times

This means no switching to bulk-logged model for data loads, and it means you will have to be running regular transaction log backups based on your RPO goals, your write transaction rates, and the size of your transaction log file.

Make sure to get your index maintenance situation in good shape before you mirror a database

Index maintenance, whether it is a reorganization or a rebuild, will generate lots of transaction log activity that will have to be sent over to the mirror. This is particularly sensitive with synchronous database mirroring. It is just a good idea to try to get your database in fighting shape before you start mirroring it. You will also want to avoid “sledgehammer” index maintenance, where you rebuild all of your indexes on a regular basis, regardless of whether they actually need it.

Use backup compression for your full and log backups

This makes your backups and restores go faster (typically two-three times faster), and also makes the backup files much smaller, which makes them easier to copy across the network. This is very important when you are trying to initialize a database mirror of a large, write intensive database. It can save you many hours of effort and waiting!

So that is it for the first post in this series. More detailed information on some of the gritty details will be coming in future posts.