SQL Server High-Availability Solutions

SQL Server High-Availability Solutions
SQL Server High-Availability Solutions

This topic presents a variety of SQL Server high-availability solutions used to help improve the availability of both servers and databases. High-availability solutions disguise the effects of hardware or software failure, further upholding the availability of applications. That way the alleged downtime for users is minimised.

Summary of SQL Server High-Availability Solutions

SQL Server provides numerous options to produce high availability for a server or database. High-Availability options include the following:

  • Always On Failover Cluster Instances

A part of SQL Server Always On option, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality providing high availability locally through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a lone occurrence of SQL Server installed across Windows Server Failover Clustering (WSFC) nodes and, quite possibly, across several subnets. An FCI shows up on the network as an occurrence of SQL Server running on a single computer, but the FCI delivers failover from one WSFC node to another, that’s if the current node becomes unavailable.

  • Always On Availability Groups

Always On Availability Groups is described as an enterprise-level high-availability and disaster recovery solution that was introduced in SQL Server 2012 (11.x) which permits you to maximise availability for more than one user database. Always On availability groups that need SQL Server instances reside alongside Windows Server Failover Clustering (WSFC) nodes.

Please Note! An FCI may leverage Always On availability groups to provide remote disaster recovery at the database level.

  • Database Mirroring

Database mirroring is used to enhance database availability by supporting rapid failover. In addition, Database mirroring can be used to uphold a single standby database, or “mirror database”, for a matching production database that is referred to as the “principal database”.

Please Note! This feature is currently in maintenance mode and could possibly be removed from a future updated version of Microsoft SQL Server. If focusing on new development work, avoid using this feature and arrange modification for the applications that are currently running this feature. We recommend using Always On availability groups instead.

  • Log shipping
    Similar to Always On availability groups and database mirroring, log shipping also runs at the database level. Log shipping can be used to maintain more than one warm standby databases (referred to as “secondary databases”) for a single production database which is referred to as the “primary database”.

Recommended Solutions for Using SQL Server to Protect Data

What we recommend to provide data protection for your SQL Server environment:

  • Data protection through a third-party shared disk solution (a SAN) – Always On Failover Cluster Instances.
  • Data protection through SQL Server – Always On Availability Groups.

If running on a version of SQL Server that does not support Always On availability groups, then we recommend using log shipping.

Contact us today