SQL Server 2005 Database Mirroring primer

@18437 Since SQL Server 2000, you have been able to create a standby server using replication, log shipping and backup and restore -- but now Microsoft has introduced a built-in tool that enables automated failover. Database Mirroring is a new feature in SQL Server 2005 that allows you to mirror database contents from one SQL Server to another SQL Server. It also enables you to failover to the mirrored database in case of a failure.

Edgewood Solutions' Greg Robidoux answers some common questions about Database Mirroring in SQL Server 2005, and explains why you should start testing this built-in functionality now to see if it will ultimately benefit your environment.

What is database mirroring?
Database mirroring is a process that moves database transactions from one SQL Server database to another SQL Server database on a different instance of SQL Server. This mirrored copy is a standby copy and can not be accessed directly; it is used only for a failover situation.

Being new to SQL Server 2005, this feature only works with this version of the software. In a way, it is a mixture of replication and log shipping: All of your transactions are moved (log shipping) on a transaction level (replication) to a mirrored copy of your database, minus some of the issues you would have to face when implementing log shipping or replication.

Replication involves a lot of moving parts, and it is difficult to keep replication running smoothly if your database schema often changes. With log shipping, many processes need to be implemented, such as backup creation, backup copying and backup restore, in order to keep things in sync. If one process breaks, the entire process breaks. How does database mirroring work?
The minimum requirements for Database Mirroring to work include two different instances of SQL Server. The primary server is called the "principal" and the secondary server is called the "mirror." The principal is your live database, and the mirror is your standby copy of the database. As transactions are written to your principal database they are also sent and written to your mirrored database.

Database Mirroring offers three modes of implementation. The choice you select depends on how you want to handle failover processing.
Greg Robidoux
FounderEdgewood Solutions
In addition to the principal and the mirror, you can introduce another optional component called the "witness." The witness is a third instance of SQL Server 2005 that acts as an intermediary between the principal and the mirror to determine when to failover. This option is only used when you want to do an automatic failover. It creates the ability to have a 2-to-1 vote that says one of my components is not available and therefore I am going to failover. A witness server is only needed when you want to implement automated failover. What are the modes of implementation?
Database Mirroring offers three modes of implementation. The choice you select depends on how you want to handle failover processing.

  • High Availability: This operating mode option allows you to synchronize transaction writes on both servers and enables automated failover. For this option to work, you must also have a witness server.
  • High Protection: This option allows you to synchronize transaction writes on both servers, but failover is manual. Since automated failover is not part of this option, a witness server is not used.
  • High Performance: This option does not care if the writes are synchronized on both servers, therefore offering some performance gains. When using this option, you are just assuming that everything is going to complete successfully on the mirror. This option only allows manual failover, and a witness server is not used.
  • Keep in mind that automatic failover to a secondary copy of your data is the real benefit of Database Mirroring. Therefore, most implementation will probably use the High Availability mode. The other options still offer a built-in process for failing over, but chances are you'll be most interested in having an automated failover in case an issue does arise. Which versions does Database Mirroring work with?
    Database Mirroring is only available in the Standard, Developer and Enterprise editions of SQL Server 2005. These are the required versions for both the principal and mirror instances of SQL Server. The witness server can run on any version of SQL Server. In addition, there are some other features only available in the Developer and Enterprise editions of SQL Server, but the base functionality exists in the Standard edition.


    At the current time, Database Mirroring is turned off in SQL Server 2005, but all of the functionality is still present. Database Mirroring can be turned on by using a trace flag -- but Microsoft does not support it at this time, so don't run this in your production environments. Database Mirroring should be fully implemented and supported later this year.

    To turn on the features and begin your testing, take a look at this previous Expert Knowledge response. Microsoft is looking for additional feedback, so see if it is a good candidate for your SQL Server environment.

    To find out more about Database Mirroring, take a look at SQL Server 2005 Books Online or take a look at this Microsoft white paper that explains Database Mirroring in much greater detail.

    About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.

    More information from SearchSQLServer.com

  • Expert response: Database Mirroring in SQL Server 2005
  • Learning Guide: SQL Server 2005
  • Topic: Get more best practices for SQL Server backup and recovery

  • Dig Deeper on Microsoft SQL Server 2005

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.