Mirroring, Replication and Always On Clusters

Mirroring versus Replication

SQL Server provides database-level redundancy through:

1. backup/restore
2. log shipping
3. database mirroring (in SQL Server 2005 and later)
4. Always on Clustering (SQL 2012 onwards)

Mirroring means master-master replication which leads to concurrency/transaction issues. Database mirroring may be the only mechanism for a real-time copy of the database with zero RPO (zero Recovery Point Objective = zero data loss); however, it is error-prone and costly.

The reason replication is preferable is because in every DB, you need to distinguish between reads and writes.  Reads are directed to slave(s), whereas writes always go to master.Even in master-master scenarios, sending WRITE queries to different servers is dangerous and error-prone.

WFSC as part of SQL Always-On Clustering

As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

DR versus Failover

Failover of Web and App Tiers

Windows offers a WSFC – Windows Server Failover Cluster which can be used to provide true failover (think multiple nodes). App and Web Servers – Possible to failover automatically provided the multiple app/web servers are part of a WFSC.

DR of Web and App Tier

DR can also be achieved through WFSC –  however, in practice, it might be simpler to simply have a copy of the app running on the Failover Node. This way, the entire application can ‘recover’ – one simply points users to the copy of the app running in the redundant data center.

Failover of Data Tier

Using Always On Clustering, failover of a database itself is simplified. All the database objects will automatically failover to the Secondary Always-On Node (could be in your backup datacenter). However, users, roles and even some StoredProcs, will not carry over – and will need to be manually recreated.

DR of data tier

For true DR, live replication may be needed. This is possible with and without Always On.

Summary

For data recovery, in theory, mirroring works best. However, in practice, replication usually wins over mirroring. This is because a typical app has both – READs and WRITEs.

When architecting a full n-Tier app DR scenario, one needs to deal with each  tier separately – as the technologies involved may differ. For Web and App Servers, WFSC or even manually recovering are both possible( Again, In theory WFSC wins, in practice, manual recovery is preferred).