SQL Server High Availability – A Right Decision

 

Is your proposed HA solution is still pending for approval??
Don’t Worry
We have noticed that it’s not easy for a DBA to determine which HA option to choose when planning business continuity.
Also we should not get confuse or intermingle with any other Backup Solution.
This post is to help people understand High Availability options present in Microsoft SQL Server that any organization can take into consideration before finalizing their strategy.
Implementing High Availability (HA) is becoming easy day by day in recent versions of SQL Server.
Since SQL Server 2005, there are 4 methods to implement HA.

• Failover Clustering
• Database Mirroring
• Replication
• Log Shipping

Microsoft Failover Clustering (MFC) runs at the operating system level and requires two or more host servers with shared storage
Feature Comparison in different High Availability.
Though, Failover clustering is the most popular option. We can compare some features of each HA option in below table. This gives a good comparison of the features available in each option.

Property \ Feature	Failover Clustering	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.

We need to understand the difference between High Availability (HA) and Backup solution.
Note: HA is not Backup. HA is not your Business Continuity Plan (BCP). HA is not your backup.
Please read the below related article thoroughly.
http://www.houseofbrick.com/network-architecture/138-Microsoft-SQL-Server-High-Availability-Options