{"id":862,"date":"2014-12-19T14:40:02","date_gmt":"2014-12-19T08:40:02","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=862"},"modified":"2015-07-09T16:33:44","modified_gmt":"2015-07-09T10:33:44","slug":"availability-options-for-sql-server","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/","title":{"rendered":"Availability options for SQL Server"},"content":{"rendered":"<h3><strong>Scenario<\/strong><\/h3>\n<p>You need to implement a high availability solution for one of your SQL Server databases, but you&#8217;re not sure of which option to implement to satisfy your company&#8217;s needs.\u00a0 Check out this tip to learn about the five native SQL Server High Availability options:<\/p>\n<p><!--more--><\/p>\n<p>Finding the right high availability option can be tricky.\u00a0 The decision really depends on these items:<\/p>\n<ul>\n<li>Needs<\/li>\n<li>Budget<\/li>\n<li>Scope<\/li>\n<li>SQL Server version<\/li>\n<li>Level of automation<\/li>\n<li>Team level support<\/li>\n<li>etc.<\/li>\n<\/ul>\n<p>At a high level, there are five main high availability options including a new feature released with SQL Server 2012:<\/p>\n<ul>\n<li>Replication<\/li>\n<li>Mirroring<\/li>\n<li>Log Shipping<\/li>\n<li>Clustering<\/li>\n<li>AlwaysON<\/li>\n<\/ul>\n<div align=\"center\">\n<hr align=\"center\" size=\"2\" width=\"100%\" \/>\n<\/div>\n<h3><strong>SQL Server Replication Overview<\/strong><\/h3>\n<p>At a high level, replication involves a publisher and subscriber, where the publisher is the primary server and the subscriber is the target server. Replication&#8217;s main purpose is to copy and distribute data from one database to another. There are four types of replication that we will outline:<\/p>\n<ul>\n<li>Snapshot replication<\/li>\n<li>Transactional replication<\/li>\n<li>Merge replication<\/li>\n<li>Peer to Peer replication<\/li>\n<\/ul>\n<p><strong>Snapshot:<\/strong> Snapshot replication occurs when a snapshot is taken of the entire database and that snapshot is copied over to the subscriber. This is best used for data that has minimal changes and is used as an initial data set in some circumstances to start subsequent replication processes.<\/p>\n<p><strong>Transactional:<\/strong> Transactional replication begins with a snapshot of the primary database that is applied to the subscriber. Once the snapshot is in place all transactions that occur on the publisher will be propagated to the subscriber. This option provides the lowest latency.<\/p>\n<p><strong>Merge:<\/strong> Merge replication begins with a snapshot of the primary database that is applied to the subscriber. Changes made at the publisher and subscriber are tracked while offline. Once the publisher and subscriber are back online simultaneously, the subscriber synchronizes with the publisher and vice versa. This option could be best for employees with laptops that leave the office and need to sync their data when they are back in the office.<\/p>\n<p><strong>Peer to Peer:<\/strong> Peer to Peer replication can help scale out an application.\u00a0 This is because as transactions occur they are executed on all of the nodes involved in replication in order to keep the data in sync in near real time.<\/p>\n<table style=\"width: 800px;\" border=\"1\" width=\"800\" cellpadding=\"0\" align=\"center\">\n<tbody>\n<tr>\n<td colspan=\"2\">\n<p align=\"center\"><strong>Pros and Cons for SQL Server Replication<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 400px;\">\n<p align=\"center\">Pros<\/p>\n<\/td>\n<td style=\"width: 400px;\">\n<p align=\"center\">Cons<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Can replicate to multiple servers<\/p>\n<\/td>\n<td>\n<p align=\"center\">Manual failover<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Can access all databases being replicated<\/p>\n<\/td>\n<td>\n<p align=\"center\">Snapshot can be time consuming if you have a VLDB<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Replication can occur in both directions<\/p>\n<\/td>\n<td>\n<p align=\"center\">Data can get out of sync and will need to re-sync<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div style=\"clear: both;\"><\/div>\n<p>&nbsp;<\/p>\n<h3><strong>SQL Server Database Mirroring Overview<\/strong><\/h3>\n<p>Database Mirroring involves a principal server that includes the principal database and a mirror server that includes the mirrored database. The mirror database is restored from the principal with no recovery leaving the database inaccessible to the end users. Once mirroring is enabled, all new transactions from the principal will be copied to the mirror. The use of a witness server is also an option when using the high safety with automatic failover option. The witness server will enable the mirror server to act as a hot standby server. Failover with this option usually only takes seconds to complete. If the principal server was to go down the mirror server would automatically become the principal.<\/p>\n<table style=\"width: 800px;\" border=\"1\" width=\"800\" cellpadding=\"0\" align=\"center\">\n<tbody>\n<tr>\n<td colspan=\"2\">\n<p align=\"center\"><strong>Pros and Cons for Mirroring<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 400px;\">\n<p align=\"center\">Pros<\/p>\n<\/td>\n<td style=\"width: 400px;\">\n<p align=\"center\">Cons<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Automatic failover (with witness server)<\/p>\n<\/td>\n<td>\n<p align=\"center\">Limited to two servers<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Fairly easy to setup<\/p>\n<\/td>\n<td>\n<p align=\"center\">Mirrored database is set to restore mode (Can&#8217;t access)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Fast failover<\/p>\n<\/td>\n<td>Replaced by AlwaysOn in SQL Server 2012<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div style=\"clear: both;\"><\/div>\n<p>&nbsp;<\/p>\n<h3><strong>SQL Server Log Shipping Overview<\/strong><\/h3>\n<p>Log shipping involves one primary server, one monitor server (optional), and can involve multiple secondary servers. The secondary database(s) is restored from the primary database with no recovery leaving the database inaccessible to end users. The process of log shipping begins with the primary server taking a transaction log backup and moving the transaction log to a backup share on the secondary server by using the SQL Server Agent and job schedules at a set time interval. The secondary server will then restore the transaction log using the SQL Server Agent and job schedules at a set time interval. While it&#8217;s nice that log shipping supports multiple secondary servers, it&#8217;s probably the least used for HA because before the failover can occur, the secondary database must be brought fully up to date by manually applying unrestored log backups.<\/p>\n<table style=\"width: 800px;\" border=\"1\" width=\"800\" cellpadding=\"0\" align=\"center\">\n<tbody>\n<tr>\n<td colspan=\"2\">\n<p align=\"center\"><strong>Pros and Cons for Log Shipping<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 400px;\">\n<p align=\"center\">Pros<\/p>\n<\/td>\n<td style=\"width: 400px;\">\n<p align=\"center\">Cons<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Can log ship to multiple servers<\/p>\n<\/td>\n<td>\n<p align=\"center\">Failover is only as good as your last log backup<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Secondary database will be read only for reporting<\/p>\n<\/td>\n<td>\n<p align=\"center\">Manual failover<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Does not require SQL Server Enterprise<\/p>\n<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div style=\"clear: both;\"><\/div>\n<p>&nbsp;<\/p>\n<h3><strong>SQL Server Clustering Overview<\/strong><\/h3>\n<p>Clustering involves at least two servers and is more of a server level high availability option compared to a database level option. Clustering will allow one physical server to take over the responsibilities of another physical server that has failed. This is crucial in environments that need close to 100% uptime. When a server&#8217;s resources fail, the other server will automatically pick up where the failed server left off causing little or no downtime. The two types of clustering we will discuss are Active\/Active and Active\/Passive.<br \/>\n<strong>Active\/Active: <\/strong>When running in Active\/Active mode, SQL Server is actually running on both servers.\u00a0 If one of the SQL Server&#8217;s fail then the other SQL Server will failover meaning that two instances will be running on one server which could potentially cause performance issues if not sized appropriately.<br \/>\n<strong>Active\/Passive:<\/strong> When running in Active\/Passive mode, SQL Server runs on one server while the other server waits in case of a failure. This is the most popular choice because it doesn&#8217;t affect performance; however, you will need a server just sitting there with nothing running on it which could be perceived as expensive.<\/p>\n<table style=\"width: 800px;\" border=\"1\" width=\"800\" cellpadding=\"0\" align=\"center\">\n<tbody>\n<tr>\n<td colspan=\"2\">\n<p align=\"center\"><strong>Pros and Cons for Clustering<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 400px;\">\n<p align=\"center\">Pros<\/p>\n<\/td>\n<td style=\"width: 400px;\">\n<p align=\"center\">Cons<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Can cluster multiple servers<\/p>\n<\/td>\n<td>\n<p align=\"center\">Complex setup<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Automatic failover<\/p>\n<\/td>\n<td>\n<p align=\"center\">Risk of purchasing hardware that never gets used<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p align=\"center\">Server level failover compared to DB level<\/p>\n<\/td>\n<td>\n<p align=\"center\">Not necessarily data protection<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><strong>SQL Server AlwaysON Overview<\/strong><\/h3>\n<p>AlwaysON is a new feature added with SQL Server 2012 and is an alternative to database mirroring. AlwaysON uses groups called Availability Groups, which are groups that contain selected databases that will fail over together if a failure should occur. Since AlwaysOn is a new feature there is not a lot of production environment usage yet. I have installed and configured this option on a few test servers, however, and think it&#8217;s by the far the coolest HA option to date.<\/p>\n<p><strong>Thanks for reading this article,<\/strong><\/p>\n<p><strong>Next steps :<\/strong><\/p>\n<ol>\n<li><strong>Share this with your colleagues because Sharingis Learning<\/strong><\/li>\n<li><strong>Comment below if you need any assistance<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: right;\"><em><strong>Powered by <a href=\"http:\/\/www.codereview.co\">CodeReview &#8211; Let&#8217;s make it Better!<\/a><\/strong><\/em><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-862\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-862\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-862\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-862\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-862-69d7276069dab' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=862&amp;origin=dotnetsql.info&amp;obj_id=76243027-862-69d7276069dab' data-name='like-post-frame-76243027-862-69d7276069dab'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Scenario You need to implement a high availability solution for one of your SQL Server databases, but you&#8217;re not sure of which option to implement to satisfy your company&#8217;s needs.\u00a0 Check out this tip to learn about the five native SQL Server High Availability options:<\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-862\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-862\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-862\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-862\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/availability-options-for-sql-server\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-862-69d727606a2bb' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=862&amp;origin=dotnetsql.info&amp;obj_id=76243027-862-69d727606a2bb' data-name='like-post-frame-76243027-862-69d727606a2bb'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[6,54,82,25],"tags":[280,97,40],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/862"}],"collection":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/comments?post=862"}],"version-history":[{"count":5,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/862\/revisions"}],"predecessor-version":[{"id":1004,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/862\/revisions\/1004"}],"wp:attachment":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/media?parent=862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/categories?post=862"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/tags?post=862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}