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.

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