SQL Server on Linux: Mission-critical HADR with Always On Availability Groups

February 17, 2017

Product
SQL Server 2017 on Linux

This post was authored by Mihaela Blendea, Senior Program Manager, SQL Server

In keeping with our goal to enable the same High Availability and Disaster Recovery solutions on all platforms supported by SQL Server, today Microsoft is excited to announce the preview of Always On Availability Groups for Linux in SQL Server v.Next Community Technology Preview (CTP) 1.3. This technology adds to the HADR options available for SQL Server on Linux, having previously enabled shared disk failover cluster instance capabilities.

First released with SQL Server 2012 and enhanced in the 2014 and 2016 releases, Always On Availability Groups is SQL Server’s flagship solution for HADR. It provides High Availability for groups of databases on top of direct attached storage, supporting multiple active secondary replicas for integrated HA/DR, automatic failure detection, fast transparent failover, and read load balancing. This broad set of capabilities is enabling customers to meet the strictest availability SLA requirements for their mission- critical workloads.

Here is an overview of the scenarios that Always On Availability Groups are enabling for SQL Server v.Next:

Run mission-critical application using SQL Server running on Linux

Always On Availability Groups make it easy for your applications to meet rigorous business continuity requirements. This feature is now available on all Linux OS distributions SQL Server v.Next supports — Red Hat Enterprise Linux, Ubuntu and SUSE Linux Enterprise Server. Also, all capabilities that make Availability Groups a flexible, integrated and efficient HADR solution are available on Linux as well:

Multidatabase failover – an availability group supports a failover environment for a set of user databases, known as availability databases.
Fast failure detection and failover – as a resource in a highly available cluster, an availability group benefits from built-in cluster intelligence for immediate failover detection and failover action.
Transparent failover using availability group listener – enables client to use single connection string to primary or secondary databases that does not change in case of failover.
Multiple sync/async secondary replicas – an availability group supports up to eight secondary replicas. The availability mode determines whether the primary replica waits (synchronous replica) or not (asynchronous replica) to commit transactions on a database until a given secondary replica has written the transaction log records to disk.
Manual/automatic failover with no data loss – failover to a synchronized secondary replica can be triggered automatically by the cluster or on demand by the database administrator.
Active secondary replicas available for read/backup workloads – one or more secondary replicas can be configured to support read-only access to secondary databases and/or to permit backups on secondary databases.
Automatic seeding – SQL Server automatically creates the secondary replicas for every database in the availability group.
Read-only routing – SQL Server routes incoming connections to an availability group listener to a secondary replica that is configured to allow read-only workloads.
Database level health monitoring and failover trigger – enhanced database-level monitoring and diagnostics.
Disaster Recovery configurations – with Distributed Availability Groups or multisubnet availability group setup.

Here is an illustration of a HADR configuration that an enterprise building a mission-critical application using SQL Server running on Linux can use to achieve: application-level protection (two synchronized secondary replicas), compliance with business continuity regulations (DR replica on remote site) as well as enhance performance (offload reporting and backup workloads to active secondary replicas):

Fig. 1 Always On Availability Groups as an Integrated and Flexible HADR Solution on Linux

On Windows, Always On depends on Windows Server Failover Cluster (WSFC) for distributed metadata storage, failure detection and failover orchestration. On Linux, we are enabling Availability Groups to integrate natively with your choice of clustering technology. For example, in preview today SQL Server v.Next integrates with Pacemaker, a popular Linux clustering technology. Users can add a previously configured SQL Server Availability Group as a resource to a Pacemaker cluster and all the orchestration regarding monitoring, failure detection and failover is taken care of. To achieve this, customers will use the SQL Server Resource Agent for Pacemaker available with the mssql-server-ha package, that is installed alongside mssql-server.

Workload load balancing for increased scale and performance

Previously, users had to set up a cluster to load balance read workloads for their application using readable secondary replicas. Configuring and operating a cluster implied a lot of manageability overhead, if HA was not the goal.

Users can now create a group of replicated databases and leverage the fastest replication technology for SQL Server to offload secondary read-only workloads from the primary replica. If the goal is to conserve resources for mission-critical workloads running on the primary, users can now use read-only routing or directly connect to readable secondary replicas, without depending on integration with any clustering technology. These new capabilities are available for SQL Server running on both Windows and Linux platforms.

Fig. 2 Group of Read-Only Replicated Databases to Load Balance Read-Only Workloads

Note this is not a high-availability setup, as there is no “fabric” to monitor and coordinate failure detection and automatic failover. For users who need HADR capabilities, we recommend they use a cluster manager (WSFC on Windows or Pacemaker on Linux).

Seamless cross-platform migration

By setting up a cross-platform Distributed Availability Group, users can do a live migration of their SQL Server workloads from Windows to Linux or vice versa. We do not recommend running in this configuration in a steady state as there is no cluster manager for cross-platform orchestration, but it is the fastest solution for a cross-platform migration with minimum downtime.

Fig. 3 Cross-Platform Live Migration Using Distributed Availability Groups

Please visit our reference documentation on business continuity for SQL Server on Linux for more specifics on how integration with Pacemaker clustering is achieved in all supported OS flavors and end-to-end functional samples.

Today’s announcement marks the first preview of new Always On Availability Groups capabilities: Linux platform support for HADR as well as new scenarios like creating a cluster-independent group of replicated databases for offloading read-only traffic. Availability Groups are available on all platforms and OS versions that SQL Server v.Next is running on. In upcoming releases, we are going to enhance these capabilities by providing high-availability solutions for containerized environments as well as tooling support for an integrated experience. Stay tuned!