SQL Server High Availability Solutions

The estimated reading time for this post is 5 minutes

It is quiet tough to decide what kind of high availability solution is available on the table and which one can fit all your production server requirements in terms of licenses, hardware cost, maintenance and professional services. In this blog post, I would like to explain every single SQL Server high availability solution that is currently feasible with the related pros and cos.

Log Shipping

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  2. Copy the transaction log file to the secondary server instance.
  3. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

log shipping

Database Mirroring

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Database mirroring is a simple strategy that offers the following benefits:

  1. Increases data protection.
  2. Increases availability of a database.
  3. Improves the availability of the production database during upgrades.

database mirroring

Failover Cluster

SQL Server failover clustering provides high-availability support for an entire SQL Server instance.  SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster.

A SQL Server failover cluster, also known as a failover cluster instance, consists of the following:

One or more Windows Server failover cluster nodes

A cluster resource group dedicated for the SQL Server failover cluster which contains the following:

  • Network name to access the SQL Server failover cluster
    • IP addresses
    • Shared disks used for the SQL Server database and log storage
    • Resource DLLs that control the SQL Server failover behavior
  • Check-pointed registry keys that are automatically kept in sync across the failover cluster nodes

A SQL Server failover cluster appears on the network as a single SQL Server instance on a single computer. Internally, only one of the nodes owns the cluster resource group at a time, serving all the client requests for that failover cluster instance. In case of a failure (hardware failures, operating system failures, application or service failures), or a planned upgrade, the group ownership is moved to another node in the failover cluster. This process is called failover. By leveraging the Windows Server failover cluster functionality, SQL Server failover cluster provides high availability through redundancy at the instance level.

fc san

 

fc smb

AlwaysOn Availability Group

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

 

An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

AlwaysOn Availability Groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows:

  • Supports up to nine availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group. Each availability group supports one primary replica and up to eight secondary replicas.
  • Supports an availability group listener for each availability group. An availability group listener is a server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica. The listener provides fast application failover after an availability group fails over.
  • Supports a flexible failover policy for greater control over availability-group failover.
  • Supports automatic page repair for protection against page corruption.
  • Supports encryption and compression, which provide a secure, high performing transport.

AlwaysOn AG

Conclusion

You may refer to the following table as your reference for SQL Server High Availability solutions pros and cons.

pros and cons

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

1 Comment on "SQL Server High Availability Solutions"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
IT support Dallas
Guest

One of these technologies is that I’d like to be able to use for myself. It is definitely a cut above the rest until my provider and I can not wait to have it. I need your insight. Thanks

IT support Dallas

wpDiscuz