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 Disaster Recovery 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 Disaster Recovery (DR).

Note: HA is not DR. 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

Related Articles :

http://www.practicalsqldba.com/2012/03/understanding-concept-of-sql-server.html

http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

http://www.mssqltips.com/sqlservertip/1541/getting-started-with-sql-server-clustering

http://clusteringformeremortals.com/2012/04/05/sql-server-2012-standard-edition-availability-options/

http://www.sql-server-performance.com/2002/clustering-intro/

http://www.techrepublic.com/article/explore-the-benefits-and-drawbacks-of-clustering-sql-server-2000/6130580

http://blogs.technet.com/b/meamcs/archive/2013/02/15/sql-2012-failover-cluster-build-step-by-step-part-1-installing-sql-2012.aspx

http://sql.starwindsoftware.com/sql-server-clustering-technology

http://www.sqlserverblogforum.com/2012/09/create-and-setup-a-sql-clustering-on-your-desktop-or-laptop/

http://www.midnightdba.com/Jen/2013/01/clustering-concepts-for-the-common-man/

http://enterpriseitnotes.wordpress.com/2013/04/19/clustering-and-mirroring/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: