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|