Chapter 34. Using database mirroring to become a superhero!

Glenn Berry

This chapter covers how to use database mirroring not only to increase availability in the traditional sense, but also for less common situations that are not typically mentioned in the SQL Server documentation or literature. One example is using database mirroring to seamlessly upgrade from SQL Server 2005 to SQL Server 2008 with a 10–15-second outage. Another example is using database mirroring to move multiple terabytes of data from one storage system to another with a 10–15-second outage. This chapter reveals these secret techniques.

Why should I use database mirroring?

SQL Server 2005 introduced database mirroring, and SQL Server 2008 enhanced it. Back in the pre-SQL Server 2005 days, you had several choices for high availability solutions with SQL Server, including failover clustering, log shipping, and replication. In many situations, database mirroring offers key advantages over all older technologies. It is simple to set up and administer, it offers fast automatic failover, it works at the database level (rather than at the instance or table level), and it can be used with commodity-level hardware.

Database mirroring offers fast failover (usually much faster than failover clustering), and automatic failover (unlike log shipping or replication). It also avoids the single point of failure that you have with the shared storage in failover clustering. In addition, it is much easier to administer than failover clustering (where adding and removing drives can be pretty painful, for example).

How does database mirroring work?

If you are unfamiliar with how database mirroring works, here is a quick primer.

You have one user database, which must be running with the FULL recovery model, which is called the principal. The principal is available for client connections, and does most of the work. You also have a complete, synchronized copy of that database (which must be running on another SQL Server instance, preferably on another server). The copy, known as the mirror, is not available for client connections. Optionally, if you want automatic failover, you have a third instance of SQL Server, hopefully on its own server, which is the witness. The witness can run on older hardware, and it can be SQL Server Express Edition. Its job is to help determine when a failover is required, by communicating with the principal and mirror. One witness instance can be used for multiple principal/mirror pairs if needed, because the witness is not doing intense work, and it can also be used as a Central Management Server with SQL Server 2008.

All of the write activity on the principal (including DDL and DML) is sent over your network to the mirror, where it is replayed on the mirror. Depending on how your database mirroring is configured, this may be happening synchronously or asynchronously. You must be running in high-safety mode (synchronous) with a witness, to allow automatic failover. If you are running Enterprise Edition of SQL Server, you can run in high-performance mode (asynchronous), which does not allow automatic failover, but can still be useful for many situations.

With database mirroring, you have two complete copies of your data, which can be a good thing. Failover clustering uses shared, external storage, usually a storage area network (SAN), where drives are owned by a particular node in the cluster. Shared storage is a common single point of failure. If unavailable, the cluster service will not start, and SQL Server will not start on the cluster.

From a storage perspective, having a second copy of the data with the mirror can be expensive, but it protects you from the single point of failure you have with a failover cluster (assuming the mirror database is on a different storage system than the principal database). This second copy of your data is ready to go after recovery has completed. The mirror database can be available much faster than a database restored from backups or SAN snapshots. As a DBA responsible for multiple, missioncritical databases, having the databases mirrored gives me additional peace of mind, because the mirrors represent additional copies of the data, over and above my normal backups and SAN snapshots.

How do you set up database mirroring?

You have several ways to do this, depending on your objectives, available infrastructure, and budget. You should invest sufficient time and resources in testing your infrastructure (including server and network hardware) to make sure it can handle your anticipated workload reliably. Make sure you have enough disk space available to accommodate possible log file growth if you encounter temporary issues with the mirroring session.

If you need or want a robust, high availability solution with automatic failover and the ability to run comfortably on the mirror server for an extended period, consider using these best practices.

Make sure that both the principal and mirror have identical hardware:

• Server model
• Number and types of CPUs
• Amount of RAM
• Amount of I/O capacity, space, and RAID level on separate storage devices

Using best practices is important to ensure that the mirror can keep up during normal operation and especially when you do log-intensive operations like index rebuilds or reorganizations. This will also ensure that you can comfortably run your workload on the mirror hardware for an extended period.

Make sure that both the principal and mirror are running a 64-bit version of Windows Server 2008 or better. Windows Server 2008 R2 will be 64-bit only; and it is likely that the next version after SQL Server 2008 will be 64-bit only; therefore, time is running out on the 32-bit era. Improved memory management alone is a great reason to switch to a 64-bit version.

You should choose a 64-bit version of the Enterprise Edition of SQL Server 2008, because Enterprise Edition has several valuable enhancements (such as parallel log restore and log stream compression) that are important for database mirroring. SQL Server 2008 native backup compression is helpful when you are initially setting up a mirror, because your backups and restores will be smaller and will usually complete more quickly. The compressed backup files will be easier to move from the principal to the mirror (because they are smaller). SQL Server 2008 also offers automatic page repair for database mirroring. SQL Server 2005 database mirroring also works well, although it lacks the enhancements that were added in SQL Server 2008.

Both sides of the partnership should be running the same build of SQL Server, that is, the same Service Pack and Cumulative Update level. If you want automatic failover, you must have a witness instance, which should be on the same build of SQL Server as the principal and mirror.

You also should standardize on common drive sizes, drive letters, and paths for certain SQL Server uses, such as data files, transaction logs, tempdb, backups, and so on. For example, you might decide to use P:, Q:, R:, and S: for data files; L: for your transaction log; T: for tempdb; and G: and H: for database backups, for both the principal and mirror sides of the mirroring partnership. This is important, because if you decide to add a new data file to your principal, the G: drive, for example, but there is no identical drive letter and path on the mirror, your database mirroring state will be suspended (and the transaction log will not be truncated) until you correct the situation. Backup drives should also have the same drive letters, because if you end up running on the mirror hardware for an extended period, you would want to be running your normal full and transaction log backup routine on the mirror side (which is now the principal). Having identical drive letters and paths makes this much easier. Even though tempdb cannot be mirrored, you still should include it in your drive letter standardization efforts, for consistency’s sake, because that will help you make sure that both instances are configured identically.

One valid objection that you may hear regarding database mirroring is that it is only available at the user database level; therefore, it does not include things like logins and SQL Agent jobs. This is easy enough to correct with a little preparation. You should try hard to use Windows Authentication instead of SQL Server Authentication for your logins, and then create the same Windows logins on the mirror instance. This will avoid the possibility of orphaned users that you can get with SQL Server Authentication. If you must use SQL Server Authentication, you can create the SQL Server logins on the mirror instance with the same SID as on the principal instance, which will also avoid the orphaned users issue. The queries in listing 1 show how to create a SQL Server login with an identical SID on the mirror instance.

Listing 1. Script to create a login with identical SID on the mirror instance

-- Get the SID for an existing login
SELECT [name], sid
FROM sys.server_principals
      WHERE type_desc = 'SQL_LOGIN'
-- Create SQL Server login with a specific SID
--(to avoid orphaned user problem)
CREATE LOGIN yourloginname WITH PASSWORD =
  'yourpassword',
sid = 0xA306A5DFBF321A4D98D71520DAE1C1D3, CHECK_POLICY
  = OFF

You can script SQL Agent jobs out on the principal, and then create them on the mirror ahead of time, keeping them disabled on the mirror until you need them. However, ensuring that the SQL Agent jobs are maintained on the mirror will require some discipline. Job changes are migrated over time.

You should make sure that both sides of the mirroring partnership are properly configured for SQL Server 2008, depending on your type of workload. For both online transaction processing (OLTP) and decision support system (DSS) types of workloads, I would grant the Lock Pages in Memory and Perform Volume Maintenance Tasks rights to the SQL Server Service Account. Prior to SQL Server 2008 SP1 CU2 (Build 2714), the Lock Pages in Memory right worked only with the Enterprise Edition of SQL Server 2005/2008. The Perform Volume Maintenance Tasks right is necessary for the Windows Instant File Initialization feature to work. This allows SQL Server to allocate file space for data files (during database creation, file grows, and database restores), without zeroing out the file first. This can pose a small security risk, but I think the benefit far outweighs the risk. Database restores are much quicker with Windows Instant File Initialization enabled. This is important when you are trying to establish a mirror on a busy production database.

You should also set instance level options with sp_configure for things like max server memory (MB), max degree of parallelism, optimize for ad hoc workloads, clr enabled, and backup compression default to appropriate values depending on your workload type. Max degree of parallelism is usually set to “1” for OLTP workloads and half the number of processor cores for DSS workloads. Optimize for ad hoc workloads and backup compression default should both be enabled in most cases. If you have any .NET CLR assemblies in the database that you want to mirror, you need to make sure that clr enabled is set to “1” on the SQL Server instance where the mirror database will live.

How do you prepare the mirror?

Finally, after all of the preliminary configuration work, you are ready to prepare the mirror for database mirroring. If possible, you should try to do this during non-peak hours, and you should also consider deferring normal index maintenance tasks while you are trying to establish the mirror, so that log-intensive operations are minimized as much as possible. This will make it much easier to establish the mirror.

To prepare the mirror, you need to take a full database backup of the user database that you want to mirror (which must be running in FULL recovery model). Using backup compression, whether it is SQL Server native backup compression or a solution from a third party, is highly recommended in this situation, because it will make the backup file(s) much smaller, and it will allow the full backup to complete much more quickly. When the full backup is complete, you will need to copy the database backup to the mirror instance. Speed is of the essence here, because your full backup is falling behind the live database each second during this process.

If you are using a modern SAN on your principal instance, you may be able to snap the backup drive(s) over to the mirror host nearly instantaneously. Otherwise, you will have to copy the backup file(s) over your network to the mirror host, which may take quite a bit of time. If both servers are running Windows Server 2008, network copy time can be reduced courtesy of the improvements in Server Message Block 2.0. If the backup file(s) are compressed, that will obviously reduce the copy and restore time even further.

After the backup file(s) are available on the mirror host, you should restore the database with the NORECOVERY option. This is important. If you restore with RECOVERY (which is the default for both the SQL Server Management Studio (SSMS) GUI and T-SQL), you will not be able to establish the mirror, and you will have to restore all over again. You should always script out the restore, to double-check it, and you should change the progress update setting to 1 percent, so that you can get a better estimate of how long the restore will take. Depending on the size and activity level of your database, and your hardware and network infrastructure, this full backup, copy, and restore sequence could take several hours. You can dramatically reduce this time frame by having a robust I/O subsystem, using backup compression, making sure the mirror instance SQL Server Service account has the Perform Volume Maintenance Tasks right, and using Windows Server 2008.

Again, depending on your level of write activity on the principal and the time elapsed for the full backup, copy, and restore sequence, you will probably have taken one or more transaction log backups on the principal during this period. You should also use backup compression here if possible. Each of these backup files should be copied over to the mirror instance, where they need to be restored with NORECOVERY, in sequence. Depending on how long the full backup took, some of the initial transaction log backup restores will be skipped when you try to restore them, because their log sequence numbers will be included in the full backup. With SQL Server 2008, you must back up and restore at least one log backup; otherwise you will not be able to establish the mirror.

For example, if your full backup, copy, and restore took six hours, your mirror will be about six hours behind the principal after the full restore is finished. You will usually find that the transaction log restores go relatively quickly, and you will start to rapidly catch up to the principal. If you are taking hourly transaction log backups on the principal, you will typically see the restore take a few minutes (depending on several factors, mainly I/O capacity on the mirror), which will allow you to eventually catch up to the principal. It is important to try to catch up as much as possible, because SQL Server will refuse to establish the mirror if it is too far behind the principal.

After you are caught up as closely as possible, you need to create the mirroring endpoints and issue the appropriate ALTER DATABASE commands to establish the mirror. The database mirroring wizard in SQL Server Management Studio (SSMS) works well enough for this, but beware that it will want to start the mirroring session in high-safety mode (synchronous) by default, which I do not recommend. You can also use T-SQL to do this. In most cases, you should start database mirroring in high-performance mode (asynchronous), to make sure that the performance of the principal is not negatively affected by the mirror trying to get fully synchronized. The SSMS tool called Database Mirroring Monitor, which was introduced with SQL Server 2005 SP1, makes it much easier to watch what is going on as the databases get synchronized. You can also watch some database mirroring specific counters in Performance Monitor (under SQL Server: Database Mirroring), and you can run some system catalog and Dynamic Management View (DMV) queries to monitor database mirroring. Some of these are shown in listing 2.

Listing 2. Queries to monitor database mirroring

-- Basic info about mirrored databases
SELECT DB_NAME(database_id) AS 'database_name', *
FROM sys.database_mirroring
WHERE database_id > 4 -- eliminate system databases
AND mirroring_state != NULL
-- Check mirroring endpoints
SELECT *
FROM sys.database_mirroring_endpoints
-- Check witness status
SELECT *
FROM sys.database_mirroring_witnesses
-- Check mirroring connections
SELECT *
FROM sys.dm_db_mirroring_connections
-- Check auto page repair history (SQL 2008 only)
SELECT DB_NAME(database_id) AS 'database_name', *
FROM sys.dm_db_mirroring_auto_page_repair

You should not rely on the status message in the Object Explorer tree in SSMS to tell you the status of your database mirror, because it does not auto refresh. If you do not manually refresh the database status in Object Explorer, you will never know the true status of the database mirroring session. This will give you a false sense of security and well-being, because the status might say Principal, Synchronized, even if the true status is something else like Principal, Suspended (unless you manually refresh it). Perhaps a future version of SQL Server will have a feature that would let you configure SSMS to let it automatically refresh this status periodically. In the meantime, you can use Database Mirroring Monitor to configure alerts that can automatically notify you about the status of your database mirror. You can also query sys.databases and check the log_reuse_wait_desc column to see if the description is DATABASE_MIRRORING (which tells you that mirroring has a problem), as you see below:

-- Get log reuse wait description
SELECT [name], log_reuse_wait_desc
FROM sys.databases

If a problem with your database mirroring session is preventing the transaction log from being truncated internally when you take a transaction log backup, the transaction log on the principal database will start to fill up and eventually grow (if auto-grow is enabled). This is usually caused by a communications problem or a REDO queue on the mirror.

One final tip about establishing your first database mirroring session on a production server is to try creating a small test database on the principal for a trial run on that server. Create an empty database running FULL recovery model, then take a full backup and log backup (even though it is empty, with no activity). Then restore the full backup and log backup, with no recovery. Finally, create the endpoints and alter the database to create the mirror. This should take you less than five minutes, and it will validate connectivity and security for all of the servers and instances involved. It will also validate that you are performing all of the tasks correctly, in the right sequence, before you try it on your production database. This tip is also useful for troubleshooting if you run into problems later on a production server.

One last thing you need to take care of is to talk to your developers about how and where they store their application-level connection strings, and what data access technologies they are using to connect to your database. With most data access technologies, you will be able to use transparent client redirect, by adding a failover partner to the connection string. This will allow clients to automatically connect to the server that is acting as the principal before and after a failover, whether it is automatic or manual. This modified connection string should be in place before you attempt a database failover. If the modified connection string is not available, implicit client redirection will usually work, but explicit client redirection is a better choice. If you ever need or want to remove mirroring from the database, you should remove the failover partner information from the connection strings for that database.

Now you have mirroring set up and running well, with no performance issues. What superhero functions can you perform with database mirroring?

Using database mirroring for routine maintenance

Routine maintenance on a database server is much more difficult than the equivalent maintenance on a middle-tier server (such as a web or application server). With a middle-tier server that is behind a load balancer, you can remove the server from the pool, allow it time to drain its pending requests, and then do whatever is required, whether it be applying Windows updates, updating the BIOS or firmware, or updating the application. It is easy to do rolling maintenance in the middle tier. Database mirroring makes rolling maintenance almost as easy for the data tier. Let’s imagine that you need to apply a critical Windows update on your database servers in production. You have tested this update in a development/QA environment to make sure that it does not cause issues for SQL Server or your application(s), and you have confirmed that the update requires a reboot of the server.

If you had a standalone database server with no high availability solution in place, you would be looking at about five to ten minutes of downtime for all of the applications that use that server (because that is about the average time it takes most commodity level servers to completely reboot and then restart SQL Server). The downtime varies based on your hardware and environment, and could be much longer. That one standalone database server restart would use up all your downtime for the entire year if you are trying to achieve five nines availability, which is 99.999 percent.

With a SQL Server Failover Cluster, you would be looking at about 60–90 seconds of downtime for each node as you moved an instance from one node to another to perform a rolling upgrade. SQL Server Clustering takes much longer to fail over than database mirroring does, because it has to start SQL Server on the new node when you fail over. With database mirroring, you would be able to perform the same routine maintenance with either one or two 15–30-second failovers (if the mirroring state is SYNCHRONIZED and there is no REDO queue on the mirror), depending on whether you had equivalent hardware on both sides of the mirror. How would you do this?

Before you start, you should confirm that you have valid failover partner information in all of your application connection strings. You would also want to defer index maintenance that was scheduled during this time frame, because you don’t want extra transaction log activity. Then you would apply the Windows update to the server where the mirror copy of the database was running, and reboot the server. After the server comes back up and the SQL Server is restarted, you can watch in Database Mirroring Monitor, until the database(s) are again fully synchronized. Depending on your hardware and workload, this might take awhile.

After the database(s) are synchronized, you would manually fail over from the principal to the mirror (thereby swapping their roles). This will usually take less than a minute depending on your workload and hardware. During this relatively short time, your applications will be unavailable, but after the mirroring roles are switched, your applications will automatically reconnect (with no user intervention), assuming the failover partner information is correct in the connection strings, and the application has been coded to reconnect after a failure. If you have multiple mirrored databases on that server, you would manually fail over each of those databases. Finally, you could apply the Windows update to the original principal server, which now has the mirror database(s), and then allow it to restart. If your hardware is equivalent on both sides, you could run on the normal mirror side until the next time you needed to do some maintenance (assuming you have a SQL license). If not, you can wait for the mirror to become synchronized, and then failover again to be back on the original server. Either way, database mirroring can give you much less downtime (seconds instead of minutes) than any other method.

If you do not have equivalent hardware and I/O capacity on both sides of the mirror, you can use a variation on this strategy. If your budget will not allow you to buy sufficient hardware and I/O capacity for the mirror, you can run database mirroring in high-performance mode (asynchronous) during normal operations. That way, your principal database is not slowed down waiting on your mirror hardware and I/O subsystem during times of peak workload. In high-safety mode (synchronous), transactions are not committed on the principal until they have been sent to the mirror, committed there, and then acknowledged back to the principal. When it comes time for routine maintenance, you follow nearly the same sequence of steps, with a few key changes.

You apply the Windows Update and reboot the mirror server, and you wait for the mirror to get synchronized again. On the principal side, you switch from high-performance to high-safety mode, which is a quick and easy change. You should once again confirm that the database is fully synchronized, which might take awhile, and then failover the database from the principal to the mirror. The failover usually takes less than a minute. After the roles are switched, go to the new principal, and quickly switch back to high-performance mode. Next, you apply the Windows update to the original principal server and allow it to reboot. Then you wait for the database to get fully synchronized and then switch back to high-safety mode and fail over that database back to the original server. Finally, you switch back to high-performance mode, and you are done, with two relatively quick failovers.

One drawback to running normally in high-performance mode is that you lose your automatic failover capability, which requires high-safety mode, with a witness. It also makes it more difficult to quickly recover from losing the principal database. If you ever permanently lost the principal database (due to a catastrophic hardware failure, for example), you could break the mirroring partnership and recover the orphaned mirror by running the commands below:

-- Remove the mirror
ALTER DATABASE yourdatabase SET PARTNER OFF

-- Restore the database on the mirror side
RESTORE DATABASE yourdatabase WITH RECOVERY

You could always restore from your regular SQL Server backup set. This could take quite awhile, during which time your application(s) will be down. If the database(s) had a mirroring state of SYNCHRONIZED when the failure occurred, you could be back up and running much more quickly on the broken mirror, with no data loss.

Using database mirroring to upgrade to SQL Server 2008

Let’s say that you are ready to upgrade your production database servers from SQL Server 2005 running on Windows Server 2003 to SQL Server 2008 running on Windows Server 2008. This technique would also work with an upgrade to SQL Server 2008 running on Windows Server 2003, but you would lose the benefits of Windows Server 2008. You could do this several ways, each with varying degrees of risk and downtime.

One way would be to upgrade both the operating system and SQL Server in place, on the existing server. This would be risky (in my opinion), and would incur at least a couple of hours of downtime for both upgrades, assuming everything went smoothly. I would never upgrade in place like this, unless I had no other choice. Another method would be to install a fresh copy of Windows Server 2008 on a new server, get it fully patched and configured, then install SQL Server 2008, and get it fully patched and configured (including transferring logins, SQL Agent jobs, and so on). After the new server was completely ready and tested, you could use one of the following methods:

• Detach your SQL Server 2005 database(s), and copy (not move) them over to the new server and attach them.
• Go through a full backup, copy, and restore sequence from the old server to the new server.
• Use database mirroring to minimize the downtime.

The detach/attach route would be faster than the other methods, but you would still be offline for the time it took to copy the database files to the new server. This time could be minimized by using a third party compressed backup or using a SAN snapshot to copy the database files to the new server. Detach/attach is slightly riskier than the other two methods, because you will upgrade the copy (not the original, which is still back on the old server) of the database to 2008 format when you attach it in SQL Server 2008. If the attach does not succeed, you still have the original on the old server. Be sure you never detach your database and move the live copy to the new server to attach it there. This is a good way to lose your live copy if the attach does not succeed.

The full backup, copy to new server, and restore sequence would take much longer than the detach/attach route, but it is safer. Depending on the size of your database(s) and your infrastructure, you would probably be looking at several hours of downtime with a full backup/restore sequence.

You could also use log shipping to get your data from the old server to the new server. You would need to change the old database to read-only before you switched to the new server, and then back up the tail of the log and restore it on the new server. This would be more complicated to implement, but it would be low risk and have relatively short downtime (probably a few minutes).

A much better solution would be to use SQL Server Database Mirroring to migrate from SQL Server 2005 on the existing server to SQL Server 2008 on a new server with a single relatively short outage. This works well, is low risk, and is easy to implement. One caveat is that your SQL Server 2005 instance must be on Build 3215 (SP2 Cumulative Update 5) or better. If you are on an older build of SQL Server 2005, you will not be able to establish a mirroring session with a SQL Server 2008 instance.

To use this method, you would install a fresh copy of Windows Server 2008 on a new server, get it fully patched and configured, then install SQL Server 2008, and get it fully patched and configured (including transferring logins and SQL Agent jobs). Then you would go though the normal steps to prepare the mirror—full database backup, copy and restore with NORECOVERY, then log backups, copy and restore with NORECOVERY, until you were ready to create the endpoints and establish the mirror.

After the mirror was established and synchronized, you could run that way as long as needed until you were ready to fail over each database to the mirror. You would also need to ensure that all of your applications had the failover partner information added to their connection strings. Failing over from SQL Server 2005 to SQL Server 2008 usually takes less than a minute, depending on your workload. Be aware that this is a one-way trip. After you have failed over from SQL Server 2005 to SQL Server 2008, you cannot go back (and, in fact, your 2005 copy of the database will be corrupted).

After you failed over each database from SQL Server 2005 to 2008, you would remove the mirroring session for that database. You would change the compatibility level to SQL Server 2008 (100) because it will still be SQL Server 2005 (90)—you originally restored it from a SQL Server 2005 backup. This assumes that your application has been tested and is fully compatible with SQL Server 2008 (100) compatibility mode. I would also consider running sp_updatestats on the upgraded database(s) during a non-peak period. Then you would enable all of your transferred SQL Server Agent jobs on the new server. Finally, you are ready to take advantage of the great new features in SQL Server 2008, and you were able to upgrade with a small outage and no loss of data.

Using database mirroring to move data seamlessly

Another common problem that can be easily solved with database mirroring is moving large volumes of live SQL Server data from one location to another, with little downtime. This could be from one server to another server, from one storage device to another storage device, or from one geographic location to another.

Case study of moving data with database mirroring

A couple of years ago, I was faced with moving about 3 TB of data that was in several busy SQL Server 2005 OLTP databases from an older server host and SAN to a new server host and SAN about 50 yards away. Let’s examine a case study in which I employed database mirroring for exactly this purpose. The system ran 24/7/365, with a five nines availability requirement. These business constraints eliminated any possibility of using a conventional full backup and restore sequence, because I estimated that it would take about 18 hours for a full backup, file copy, and full restore to complete, which was unacceptable to the business.

Another possibility was using a database detach, file copy, and attach operation. We had 2Gbps host bus adapters (HBAs) in each server, and copying the detached database files would be much faster over fiber links than with Gigabit Ethernet. Even so, we would be looking at several hours to copy the database files from the old server to the new server, plus a significant risk of something going awry during the file copy. None of these conditions was acceptable. Once again, the superhero solution is database mirroring.

In this case, I was able to deploy a new server attached to the new SAN in the new location. We installed a fresh copy of the x64 version of Windows Server 2003 R2 and got it fully configured and updated, including provisioning all the required SAN drives. Then we installed the x64 version of SQL Server 2005 Enterprise Edition and got it fully configured and updated to the same SQL Server build as on the original instance. Then we started the mirror preparation process.

I ran a full database backup on the original server, and had our SAN engineer take a SAN snapshot of the backup drives, which he then presented to the mirror host machine. This allowed me to start the full restore with NORECOVERY process in a few minutes (because I did not have to wait hours to copy the backup files over the network). As the full restore was running, I periodically copied my transaction log backup files over the network from the old server to the new server, so that they would be ready to restore as soon as the full restore finished. This is important with a busy OLTP database, because you are racing to catch up as you are preparing the mirror. After the full restore finished, I started restoring the transaction log backups on the new server. Scripting multiple log restores out with a 1 percent progress update setting and some PRINT statements after each log restore gave me a much better idea of my progress and how far the timing of the mirror database was behind the principal database. After I was caught up to being less than an hour behind the principal database, I configured security, and established the mirror in high-performance mode. Because the new SAN had much more I/O capacity than the old SAN, it only took a few minutes to get each database synchronized.

We ran in high-performance mode for a few days while we were moving other hardware, and were making sure we were ready to fail over the mirrored databases. Finally, we switched to high-safety mode and failed over each database to the new server and SAN. After some application testing, we were ready to remove the mirroring session for each database, making the move permanent. Then we powered down the old server and SAN, and moved them to the new location, rebuilt the server with a fresh copy of the operating system and SQL Server, and reestablished all of our database mirrors.

Lessons learned from case study

Depending on your workload and I/O capacity, it may take some time for the principal and mirror databases to get fully synchronized after you initially establish a database mirror. If you have less I/O capacity on the mirror side, you will see the unrestored log size grow on the mirror side if you are watching in Database Mirroring Monitor. Eventually it should catch up, and allow the two databases to become fully synchronized. After the databases are synchronized, you are ready to fail over from the principal to the mirror.

In order to fail over seamlessly, you need to make sure that you have the correct failover partner information added to all of your application connection strings. When you are ready, you can switch to high-safety mode and fail over the database. After your expected 10–15-second outage, your applications will be running against the principal database on the new server and SAN, in the new location. Next, you can remove mirroring from the database, and you will have moved the data, with a brief outage. After you remove mirroring from the database, make sure to remove the failover partner information from all of your connection strings. Later, after you have repeated this sequence with all of your databases, you can power down the old server and SAN and move them if necessary. This general strategy can also be used to move data over longer geographic distances. As the geographic distance increases, you have to be careful about increasing network latency if you are running in high-safety mode, which will cause delays for the principal as it waits for the mirror to harden its transactions. This is much less of a problem with high-performance mode, and SQL Server 2008 also helps with log stream compression, which is helpful over high-latency WAN links.

Once again, using Windows Server 2008 in combination with SQL Server 2008 Enterprise Edition makes it much quicker and easier to prepare the mirror, because you get much better network file copy performance, you can use backup compression for your backups and restores, and you can run database mirroring in high-performance mode. In the end we were able to fail over from the old server and SAN to the new server and SAN with a single 15-second outage, which was a great success, made possible with database mirroring.

Summary

For some reason, few DBAs have tried out database mirroring. When I go to various SQL Server–related events, I often ask for a show of hands on how many people have tried or are using database mirroring with SQL Server, and usually the percentage is low, which always amazes me. Don’t be afraid to start using SQL Server database mirroring.

As you’ve seen, database mirroring is easy to set up and administer, it offers the fastest possible automatic failover (under the right conditions), and it can be used with commodity hardware for a reliable high availability solution. It can replace or complement failover clustering or log shipping. For example, databases within a clustered instance can be mirrored to either a standalone instance of SQL Server or another SQL Server failover cluster. This provides both hardware redundancy through the Windows cluster and storage redundancy through database mirroring. This would give you additional hardware redundancy at the host level because of the Windows cluster, and storage redundancy because of the database mirroring.

With this configuration, you could also take advantage of the much faster failovers available with database mirroring to reduce your downtime for normal maintenance. Using a database mirror in combination with a cluster makes it much easier to do maintenance on the entire cluster (such as applying a Windows or SQL Server Service pack or cumulative update), because you can failover all of your databases to the mirror, and then do whatever you need to do to the cluster, without being in a rush.

If you are going to combine failover clustering with synchronous database mirroring (with automatic failover), you will want to change the default mirroring partner timeout to a higher value, so that a mirroring fail over is not initiated during the time that you are in the middle of a cluster failover (from one node to another). You can change this value with the following command:

ALTER DATABASE mydatabase SET PARTNER TIMEOUT 90;

Another possibility is to combine failover clustering with asynchronous database mirroring, which will avoid the potential for dueling failovers, at the cost of the loss of another layer of automatic redundancy. This is still a useful configuration, because you have a second copy of the database available (although it may not be synchronized) in the event of a catastrophic cluster or storage failure.

You can also take advantage of database mirroring for the non-traditional uses described in this chapter. Database mirroring can dramatically reduce your downtime for rolling maintenance tasks. It also makes it much easier to migrate to SQL Server 2008 in a production situation, with little risk or downtime. Hopefully this chapter has inspired you to try out database mirroring and given you ammunition to convince your company to upgrade to both Windows Server 2008 and SQL Server 2008, not because they are new and shiny, but because of tangible benefits that they provide together.

About the author

Glenn Berry works as a Database Architect at NewsGator Technologies in Denver, Colorado. He is a SQL Server MVP, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests.

He is also an Adjunct Faculty member at University College, University of Denver, where he has been teaching since 2000.