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
Requires Free Membership to View
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.
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.
Summary
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation