Sql – Mirroring vs Replication


How to decide whether to choose a Replication or Mirroring in SQL Server 2005 to provide data availabilty and performance at the same time.

To be more specific about my SQL server architecture, I have an active/active cluster of 2 nodes which are used for load balancing, and I have another server for replication, which will be used only for reporting, I want to make sure which technology best provides both availabilty and performance, Transactional replication or Database Mirroring?

Best Answer

It depends on the level (hot, warm, cold) of standby availability you require.

Various mechanisms in SQL Server provide database-level redundancy, such as backup/restore, log shipping, and database mirroring (in SQL Server 2005 and later). Database mirroring is the only mechanism that provides a real-time, exact copy of the protected database with the guarantee of zero data loss (when the mirror is synchronized).

Database mirroring operates in either synchronous or asynchronous mode. Under asynchronous operation, transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.

This MS Whitepaper, Providing High Availability using Database Mirroring, covers a range of scenarios.

You should probably read this TechNet article, Database Mirroring Best Practices and Performance Considerations.