Compare features in different High Availability options in SQL Server.
Since Sql Server 2005, High Availability (HA) has become very easy to implement in a Sql Server environment. Also, starting Sql 2005, there are 4 methods to implement HA.
- Failover Clustering
- Database Mirroring
- Replication
- Log Shipping
Though, Failover clustering still is the most popular option, my guess is the other options might make it a more level field going forward.
In the following matrix, I compare some features of each HA option in one table. This gives a good comparison picture of the features available in each option.
| Property \ Feature | FailoverClustering | DB Mirroring | Replication | Log Shipping |
| Server Level | Yes | No | No | No |
| Database Level | No | Yes | Yes | Yes |
| Automatic Failover | Yes | Yes (Only HA Option) | No | No |
| Automatic Failure Detection | Yes | Yes (Only in HA option) | No | No |
| Hot, Warm or Cold standby | Hot | Hot and Warm Options | Warm | Warm |
| Server Level Objects Failover | Yes (Automatic) | Manual | Manual | Manual |
| Is data on Failover available for queries while the Primary server is running? | No | Yes (use Database Snapshots) | Yes | Yes (Standby Mode) |
| Are changes allowed at Failover location? | No | No | Yes (Merge and TRN Replication with updating subscribers) | No |
| Is Main and Failover server in a 1-1 relationship? | Yes | Yes | Not Required | Not Required |
| Is 1-Many relationship possible between the Main and Failover servers? | No | No | Yes | Yes |
| Is Many-1 relationship possible between Main and Failover server? | No | No | Yes | No |
| Is Automatic Application redirection possible after failover? | Yes | Yes | No | No |
| Recovery model required for the participating databases? | Any | FULL | ANY | FULL & BULK |
| Can you filter what data is sent to the Failover server? | No | No | Yes | No |
| Where does the logic HA reside? | OS + Sql | Sql | Replication Agents (they run outside Sql Server | Sql Agents (Run outside Sql Server) |
| If you want a reports server, what is the best solution? | Not Possible | Works, but needs constant refresh of the Snapshot database for latest data. | Best Option. | Works, but users connections need to be kicked out, when new log is applied. |
| Best solution if the Main and Failover server are separated by long distance? | Not good. | Works, but not recommended. | Works, but with high latency | Best Option. (Log compression reduces latency significantly. |