As a DBA sometimes we came across the situation when we have to choose a best High Availability and Disaster recovery solutions.

Each has its own advantages and limitations.

To provide an accurate choice which technology to use, DBAs must have through understanding of business needs for high availability, How fast  should fail-over happen,how many replicas  required and if reads can happen on the secondary. Each SQL Server High availability technology has  short comings on a different dimensions.

Here is a quick table for reference.

Feature  Fail over clustering Database Mirroring  Log Shipping Transactional Replication
Storage redundancy ?  No Yes Yes Yes
 No need for special hardware? No Yes Yes Yes
Role change automatically Yes Yes No No
No need for additional server ? None Witness Monitor* Distributor*
Reports read from secondary ? NA No Read only Yes
Scope of availability Instance level Complete Database Complete Database Database Objects
 Multiple secondary client NA No Yes Yes
Auto redirection after failover Yes No: Need custom solution No: Need custom solution No: Need custom solution
Recovery Model Any Full Full Any

Leave a Reply

Your email address will not be published. Required fields are marked *