High Availability & Disaster Recovery Databases for Microsoft SQL Server (Part 2)

In my previous blog post, I introduced the basics and requirements of a Highly Available (HA) database in Microsoft SQL Server. HA databases are categorized staunchly by their ability to perform disaster recovery in a speedy, efficient manner. A standard SQL Server feature, AlwaysOn, improves fault tolerance and disaster recovery efforts across databases. In this blog post, I focus more on disaster recovery solutions by examining the AlwaysOn features.

Microsoft SQL Server as a product has evolved and matured over the course of its existence. Prior to Microsoft SQL Server 2012, the traditional method for setting up High Availability databases was to install SQL Server in a cluster or on a Virtual machine (VM) using a virtualization technology, then set up mirroring and/or log shipping for disaster recovery purposes. With SQL Server 2012, Microsoft introduced a new feature called “AlwaysOn,” which provided new capabilities to configure and maintain a Highly Available SQL Server environment while keeping the cost and complexity of the implementation low.

Solutions that are built utilizing the SQL Server AlwaysOn feature provide fault tolerance and disaster recovery across the following physical layers of infrastructure and application components:

  • Infrastructure Level: Windows Server Failover Clustering (WSFC) provides features that monitor the health of resources within the cluster and with failover coordination.
  • SQL Server Instance Level: AlwaysOn Fail Over Cluster Instance (FCI) is a SQL Server instance installed across multiple nodes in a WSFC. These multiple nodes are configured to be identical and provide instance level fault tolerance by enabling SQL Server to run on any of them at a given point in time. AlwaysOn FCI provides instance level fault tolerance.
  • Database level: AlwaysOn Availability Groups (AG) is a set of databases that fail over together. These databases can fail over between primary and secondary replicas which are hosted a SQL Server FCI instance or a non FCI instance. FCI is not mandatory to setup Availability Groups. AlwaysOn AG provide database level fault tolerance.
  • Client Connectivity: Traditionally, client applications connect to a SQL Server using the instance name. However, AlwaysOn introduces Virtual Network Name (VNN) feature which is bound to the availability group listener. VNN creates a layer of abstraction between the client and the physical hardware that SQL Server runs on there by making it transparent to client applications where a failover happens.

The following diagram is a representation of how a SQL Server AlwaysOn solution can be deployed:

High Availability & Disaster Recovery Databases

Courtesy of Microsoft

A closer look at the architecture in the diagram above presents the following information:

  • 5 instances of SQL Server exist in this architecture
  • These instances exist in two different subnets (could be two different data centers)
  • All of the 5 instances are part of the same WSFC configuration
  • 2 of the 5 instances are part of an SQL Server FCI, whereas the other 3 are standalone instances
  • The 2 FCI instances share the same storage, but the other 3 instanced have their own separate storage
  • All of the 5 instances are part of an Availability group, with one node acting as a primary and the rest as secondary replicas

AlwaysOn availability groups provide a rich set of options that can be used to configure your SQL Server environment to perform optimally and be highly available simultaneously:

  • Multiple Replicas: the number of supported replicas is based on the version of SQL server. Each of the replicas can serve as a potential failover target. SQL Server 2016 supports up to 9 availability replicas.
  • Availability Modes:

    • Asynchronous commit: best suited for disaster recovery
    • Synchronous commit: best suited for high availability
  • Failover Type: supports several forms for failover, including automatic failover, planned manual failover, and forced manual failover. These various failover modes provide you the flexibility needed to configure the SQL Server environment based on your business need.
  • Improved IT efficiency: Availability replicas can be configured to perform the following active secondary capabilities:

    • Backups can be configured to run on the replica.
    • Ready Only operations such as data loads to reporting / BI platforms can be performed from the replica.

Offloading backup and read-only operations to secondary replicas improve performance on the primary replica by reducing the need to have high capacity hardware to run the primary replica or instance of the SQL server.

By taking advantage of the SQL Server’s capabilities for High Availability and Disaster Recovery databases, architect solutions achieve an availability rating of 99.999%.

There’s more to explore at Smartbridge.com!

Sign up to be notified when we publish articles, news, videos and more!

By signing up for emails from Smartbridge.com, you agree to our terms and privacy policy.