Tags


Latest Posts


Latest Comments


Authors

Stairway to SQL Server High Availability (HA) solutions – level 1: Overview of all SQL Server’s HA offerings

louis-ozougwu.jpg

Posted by |

This blog is the first in a series of blogs where I hope to unmask various SQL Server High Availability solutions. It presents an overview of SQL Server High Availability solutions on offer. Further blogs in the series will attempt to dive deeper into each of the options mentioned in this blog. The last couple of blogs in the series will compare each of the High Availability options to highlight their differences and reveal how they fit into organisation’s Service Level Agreements. Let’s start!

High Availability solution, according Paul S Randal, is one that makes data more readily available in the event of a disaster. It allows business continuity by masking software and hardware failures from the application users.  It does this by ensuring that core application and services supporting an organisation can continue to function with minimal disruption in the event of any failure.

Some of the High Availability options provided by SQL Server include:

Log Shipping: Log shipping provides high availability by creating redundancies on a per-user-database level. System databases are not covered. An initial restore of a full database backup to a warm standby system is initially required, followed by periodic transaction logs updates from the primary to the standby system. If the primary system becomes unavailable, user action is required to bring the standby database online. Automatic failover is not possible.

Database Mirroring: Database mirroring is similar to Log shipping as it also involves an initial full database restore, works on a per-user-database level and does not cover system databases. But unlike log shipping, transaction logs are applied from the primary system to the standby system as soon as they are created not at pre-set intervals. Also, Database mirroring supports several forms of failover: automatic, planned manual failover and forced manual failure. Database Mirroring’s automatic failover has less than three seconds6 recovery time.

SQL Server Clustering (Failover Cluster Instance): Unlike Log shipping and Database mirroring, Clustering provides protection at the server / Instance level.  On failure, the operating system and services work together to provide automatic failover, in less than 30 seconds6, to any available participating server. This failover is transparent to the client and application connecting to the SQL Server and does not require any user intervention. SQL Server failover Cluster Instances requires Windows Operating Server Clustering.

SQL Server AlwaysOn Availability Groups: Introduced with the release of SQL Server 2012, AlwaysOn availability group is a robust High Availability solution that is built as a successor to Database Mirroring and offers enhanced improvements to the capabilities possible in Database Mirroring. Unlike Database Mirroring which offers protection on a per-database-basis, AlwaysOn Availability Group offers improved protection to a set of user databases called availability databases. It does this by maximizing their availability across a group of participating servers called availability replicas. Up to 5 availability replicas are supported. Each participating replica must reside on a different node of a Windows Server Failover Cluster (WSFC). On failover the set of user databases in an availability group move as a group to one of the participating replicas. Failover can be automatic in synchronous commit mode configuration or manual in an asynchronous commit mode configuration. SQL Server AlwaysOn availability group can be stretched to a multi-site solution, and can also be leveraged to provide cover at the server / instance level in a solution called AlwaysOn failover Cluster Instance – see following blogs in this series for more details.

SQL Server Replication: SQL Server Replication can serve as a High Availability solution but it is mainly used in the industry as a scale-out technology as it targets database objects rather entire database – see Paul’s post in defence replication as a HA solution. In SQL Server Replication, a snapshot of initial data files (from publisher server) meant for publication is taken (by a distributor server) to the target site (called subscriber server / device); this is followed by regular updates within acceptable intervals in a way that allows site autonomy and brings data closer to geographically distributed users. Types of replication include snapshot replication, transactional replication and merge replication. Peer-to-peer replication and Oracle Publishing replication are variations of the three main types of Replication mentioned earlier. Adam Jorgensen et al, discusses implementation of Peer-to-Peer replication and all the other SQL Server HA solutions mentioned above.

References

  1. http://technet.microsoft.com/en-us/library/ms189134.aspx
  2. http://technet.microsoft.com/en-us/library/ms189852.aspx
  3. http://msdn.microsoft.com/en-us/library/ms190202%28v=sql.105%29.aspx
  4. http://www.sqlskills.com/blogs/paul/in-defense-of-transactional-replication-as-an-ha-technology/
  5. http://www.sqlskills.com/blogs/erin/sql-server-hadr-features/
  6. http://www.vmware.com/files/pdf/solutions/SQL_Server_Availability_Guide_FINAL.pdf
  7. Professional Microsoft SQL Server 2012 Administration by Adam Jorgensen, Steve Wort, Ross LoForte and Brian Knight (24 August 2012)

 

Comments

 

Post a comment

Comment submitted! Comments needs approval before being displayed.