Database Mirroring

In one of my previous posts I talked about Transaction Log Shipping and how it can be used to create a copy of the database on a secondary server and periodically apply the transaction logs. Mirroring is a similar process except a few differences.

Whereas in Log Shipping, transaction logs are periodically backed up and physically copied to the secondary server and applied to the database in recovery mode. In mirroring, SQL Server directly reads the data from the transaction logs and copies them from the principal server to the mirrored server.

 Mirroring can be configured to operate synchronously or asynchronously. In synchronous configuration, primary or principal server sends the data to the mirrored server and waits until the mirrored server saves the data on the hard-drive before committing the transaction. In this scenario, basically buffered data is sent to the mirrored server and principal waits for a signal from the mirrored instance. Once the signal is received from the mirror, only then the transaction is committed on the principal. As you can imagine the performance of your database depends on several factors such as network bandwidth, disk write speed etc.

 In asynchronous mode, the principal server doesn't wait for the mirrored server before committing the transaction. It sends the buffered data to the mirrored instance, while simultaneously committing the transaction.

Mirroring supports only one mirrored instance. This is yet another difference between Mirroring and Transaction Log Shipping, which supports multiple instances of the secondary server.

Mirroring supports automatic failover. In case of a failure on primary server, SQL Server automatically brings the mirrored instance online, usually within a few seconds.

While Log Shipping has been available since SQL 2000, mirroring was introduced only in SQL Server 2005.

Another key advantage of mirroring is built-in support in .NET Framework which doesn't require special routing or switching code to handle the fail-over switch from one server to another. It requires ADO.NET 2.0 and higher.

SQL Server 2008 introduced another key feature in mirroring, i.e. ability to automatically repair corrupt pages. For example, if a page is corrupted on the principal server, SQL Server will replace it with a page from the mirror and vice versa.

We can go in more technical details in further posts if needed, but in a nutshell this is what mirroring is all about.

As always, your comments are welcome!