olly - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Dissect the SQL Server on Linux high availability features

SQL Server 2017 on Linux gives IT shops greater flexibility, but there are some limitations and changes to the way high availability and failover are performed on Linux vs. Windows.

Companies planning to run SQL Server on Linux must understand which features won't be available for Linux, as well as how data protection and high availability capabilities differ on Linux vs. Windows.

The initial release of SQL Server on Linux will consist of the relational database engine with support for online transaction processing, database applications and basic data warehousing. It will not include other subsystems, such as Analysis Services, R Services and Reporting Services. It is supported on Red Hat Enterprise Linux 7.3, SUSE Enterprise Linux Server 12 SP2, Ubuntu 16.04 LTS and Docker Engine 1.8 or higher on Windows, macOS or Linux. 

One of the biggest questions about SQL Server on Linux is what high availability options it will support. Microsoft provides several different levels of availability and disaster recovery options for SQL Server on Linux.

Basic SQL Server availability features

For basic levels of availability, SQL Server on Linux supports basic backup and restore, virtual machine (VM) failover, and log shipping. These are available for all SQL Server editions, including the Express, Standard and Enterprise editions.

Backup and restore are the most fundamental SQL Server data and disaster recovery capabilities. SQL Server on Linux supports most basic SQL Server backup and restore methods, including full database backup, differential database backup and transaction log backup.

Backups and restores for SQL Server on Linux are initiated using either Transact-SQL (T-SQL) or SQL Server Management Studio running on a remote Windows system. Using database backup and restore is also a great way to migrate your databases to SQL Server on Linux. You can back up your SQL Server databases on Windows, and then restore them to SQL Server on Linux or vice versa -- the backup format is identical. The backup and restore option is compatible with databases running on SQL Server 2014 and higher.

You can also implement a simple VM failover option using Hyper-V Replica or VMware HA. These both provide resilience from host, guest and OS failures. They can also provide protection for planned downtime, such as software upgrades and patching, by enabling you to manually fail over before patching the host instance. Because both of these technologies replicate changes, periodically unplanned downtime will result in some data loss.

Log shipping on SQL Server is a high availability and disaster recovery feature where a database from a primary server is replicated to one or more secondary servers. It essentially works the same in Linux as it does in Windows. A backup of the primary database is restored to a secondary server. Then, the primary server periodically creates transaction log backups, and forwards them to the secondary servers, where they are restored.

SQL Server 2017 on Linux uses the Common Internet File System and Samba to set up a network share where the transaction log backups are stored. The SQL Server Agent on Linux is used to periodically run a stored procedure that forwards the log backups to the secondary servers. Similar to Windows, recovering a database that is protected with log shipping is a manual operation.

Failover clustering on Linux

Like Windows, Linux failover clusters consist of two or more servers that are connected to shared storage. The servers are called cluster nodes. Conceptually, high availability clustering for SQL Server on Linux works like Windows. Similar to the Windows Server version of Failover Clustering instances, SQL Server clustering on Linux supports instance-level protection, automatic and manual failover, transparent failover for applications and clients, and recovery times of seconds to minutes.

However, since Windows Server Failover Clustering is not supported on Linux, SQL Server 2017 on Linux uses the native Linux Pacemaker high availability technology. All of the cluster nodes require an active connection to the storage resources. When Pacemaker detects a failure, the clustering components move the SQL instance to another node. You can see a high-level overview of failover clustering on Linux using Pacemaker in Figure 1.

Linux with Pacemaker failover
Figure 1 -- Failover clustering on Linux with Pacemaker

AlwaysOn Availability Groups

Like the Windows version, the maximum levels of availability for SQL Server on Linux are provided by AlwaysOn Availability Groups . AlwaysOn Availability Groups in Linux support:

  • protection for up to eight secondary replicas;
  • protection for multiple databases;
  • up to three synchronous replicas;
  • mixing synchronous and asynchronous secondary replicas;
  • manual or automatic failover;
  • readable secondaries;
  • read-only routing; and
  • automatic seeding.

There are two configuration types for availability groups on Linux. You can create a high availability configuration that uses Pacemaker to provide business continuity, or you can create a read scale-out availability group without a cluster manager. The read scale-out only provides read-only replicas for performance scale-out -- it does not provide high availability. The high availability configuration can also include read scale-out replicas.

The availability group configurations that provide high availability and data protection require three synchronous replicas. When there is no Windows Server failover cluster, the availability group configuration is stored in the master database on participating SQL Server instances, which need at least three synchronous replicas to provide high availability and data protection.

An availability group with two synchronous replicas can provide data protection, but this configuration cannot provide automatic high availability. If the primary replica has an outage, the availability group will automatically fail over.

However, applications cannot automatically connect to the availability group until the primary replica is recovered. You can have a mixed availability group that contains both Windows and Linux replicas, but Microsoft only recommends this for data migration.

SQL Server 2017 on Linux provides the enterprise-level data protection and availability capabilities that businesses require for their relational database applications. However, while SQL Server on Linux does support failover clustering, availability groups and log shipping, it does not support database mirroring. Database mirroring is still supported in the Windows version of SQL Server 2017, but it is a depreciated feature that Microsoft plans to remove in the future.

Next Steps

SQL Server on Linux creeps closer

Microsoft embraces open source

Microsoft evolves with Linux

Dig Deeper on Microsoft SQL Server Tools and Utilities