Mirroring vs Replication

Question

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

Answer 1

I don't know SQL Server 2005, but for general SQL Usage i would always prefer Replication. You have to separate reads/writes in your application (for MySQL there is MySQL Proxy which can do this in a proxy way for you), but gain a scalable system. (reads to slave(s), writes to master)

Mirroring means master-master replication which leads to concurrency/transaction issues. Even in master-master scenarios you should NEVER send write queries to different servers. Depending on your project's size, the next steps would be adding more slaves, and then add another master+its slaves for redundancy.

master --- master
  |          |
slave       slave
  |          |
slave       slave
  |          |
slave       slave

Even then you would only send write queries to one master, but in case of a failing master you could automatically promote the second master to your new write-query-target.

Answer 2

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?

Answer 3

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.

Answer 4

It turns out that Database mirroing prevents data to be accessed directly, mirrored data are only accessable through a database snapshot, so reports from snapshot data will not be up to date so, I will use Database Transactional Replication to provide high availabilit and load balancing.