Mirror, mirror, on the wall, who's the fairest database of all? I don't know about fairest, but database mirroring, a new feature in SQL Server 2005, certainly makes SQL Server more attractive in terms of high availability and standby database needs. Database mirroring is transaction-log based and sends a continuous steam of log records from a primary server to a mirror server to provide high availability, fault tolerance, and standby solutions. Previous SQL Server Standard Edition releases lack valuable high-availability solutions. SQL Server 2005 Standard Edition includes basic database mirroring to provide additional data protection from certain types of system failures. However, Microsoft chose to save some more advanced database mirroring features for the Enterprise Edition to raise the value.
Database mirroring runs in one of three modes: High Availability, High Protection, or High Performance. The High Performance Mode works only when the Transaction Safety setting is off and is available only in SQL Server 2005's Enterprise and Developer Editions. It might sound counterintuitive to have a high-end feature--saved for the Enterprise Edition--associated with "unsafe" transactions. High Performance mode requires these "unsafe" transactions to ensure that mirroring won't adversely affect performance in high-volume environments, especially when the primary and mirror servers are separated over a WAN. A Full Safety setting requires that transactions are committed on both the primary and mirror servers before the primary server will consider the transaction fully committed. Think of this as synchronous communication. However, turning the High Performance Safety mode off lets the primary server consider a block of transactions as fully committed before receiving notification from the mirror that its transactions are also committed; think of this as asynchronous. Eliminating the need to receive positive confirmation from the mirror before committing a block of transactions removes a tremendous serialization bottleneck that might otherwise occur. Both the High Availability and High Protection modes require synchronous, or safe, transactions.
I've shared a few key database-mirroring concepts with you here, but I also want to encourage you to read Ron Talmage's Microsoft white paper "Database Mirroring in SQL Server 2005" (http://lists.sqlmag.com/t?ctl=F1C8:7B3DB), which provides a wonderful introduction to this new feature. I think the most valuable parts of the 39-page white paper are the in-depth analyses of real-world implementation models that help you understand the pros and cons of using database mirroring in certain scenarios.
One of my favorite sections of the white paper compares database mirroring with other SQL Server high-availability and standy technologies. SQL Server 2005 supports four distinct high-availability options--database mirroring, clustering, log shipping, and transactional replication--that you can also use for standby purposes. Of course the white paper doesn't include the growing number of high-availability add-on solutions from third-party vendors. And Talmage doesn't discuss how you can use database mirroring with the other three high-availability technologies that come in the SQL Server box. Choices are many, which means you must be more diligent when picking the most appropriate combination of technologies to ensure you roll out the best solution for your needs. I hope you enjoy the white paper; I know you'll appreciate the extra flexibility that database mirroring provides.
