High Availability Options for SQL Server Linux

Database high availability (HA) and disaster recovery (DR) are critical for any organization. There is essential work to be done to ensure that online services and applications always have access to data in order to operate.

SQL Server Linux provides multiple options to implement high availability and disaster recovery. However, it might be tricky to find the right high-availability solution, as the solution might depend on SQL Server version, level of automation of high availability, database access, or backup requirements. 

This post will cover all of the available high availability options for SQL Server Linux. 

SQL Server Log Shipping Overview

SQL Server Log Shipping works by automatically sending the primary database log backups to one or more secondary server instances. It applies these log backups on the secondary databases to keep them up to date with the primary database. This process is similar to the MySQL binlog or the PostgreSQL Write Ahead Log (WAL). Additionally, you can deploy a monitor server to record the backup, restore history, and raise alerts if the secondary copy is behind the primary database with a predefined threshold.

SQL Server Log Shipping Benefits

• It works on database transaction-log backups, providing a simple disaster recovery mechanism.

• The log shipping process allows customization for the following:

  • The time at which the primary instance sends backup to the secondary database

  • The restoration frequency

• You can execute read-only queries from the secondary log-shipping database. The read requests work during the internal between restore jobs.

• Log shipping is supported in Standard and Enterprise editions.

SQL Server Log Shipping Limitations

• Log shipping only supports manual failover. Therefore, if your primary instance goes down, you need to take manual actions for applications to start using the secondary database.

• The secondary copy always remains behind the primary database. The secondary database contains data up to when the last log backup was restored.

SQL Server Replication

SQL Server replication is used to distribute filtered data among multiple target servers. It can synchronize data between databases while maintaining data consistency and integrity.

The replication process involves a publisher (source database) and a subscriber (target database). SQL Server replication works in a variety of database synchronization approaches, such as:

• One-to-one

• One-to-Many

• Bi-directional

SQL Server supports the following replication types.

• Snapshot Replication: Snapshot replication takes a snapshot of the entire database and applies it to the subscriber database. It is helpful for initial database synchronization or cases where your source database does not change frequently.

• Transactional Replication: Transactional replication replicates continuous changes from the primary database (publisher) to the secondary database (subscriber) with minimal latency. It is one-way replication from publisher to subscriber databases.

• Merge Replication: Merge replication can replicate data from a publisher to a subscriber database and vice versa. It is bidirectional replication typically used in the server to the client environment for synchronizing data across database servers when they cannot be connected continuously.

• Peer-to-Peer Replication: Peer-to-Peer replication can replicate data to multiple subscribers distributed across the globe. Data can be changed in any database server and replicated to servers participating in Peer-to-Peer replication.

Benefits of SQL Server Replication

• You can replicate data to multiple database servers through one-way or bi-directional synchronization.

• Replication allows you to filter data and replicate only required data to subscribers.

• You can access and run workloads on the replicated database.

• The replicated database is available regardless of the publisher (source) database.

• Replication is available in both Standard and Enterprise SQL Server editions.

SQL Server Replication Drawbacks

• It does not support automatic failover.

• The initial data snapshot can be time and resource-consuming for large databases.

• All tables should have a primary key defined to use.

SQL Server Failover Clustering

A SQL Server failover cluster provides a high availability solution at the server level. If the primary server crashes or goes down for any reason, the secondary server can take over responsibility, and SQL Services and databases remain online. It uses a virtual name for application connection. Therefore, the application does not need to reconfigure connection strings in order to work after the secondary server takes over.

There are two types of failover clustering:

• Active-Passive Cluster: Active-Passive cluster configuration uses the secondary server in case of any failover. It is a popular choice since it does not affect performance. However, your passive server remains idle until resources are failed over to it.

• Active-Active Cluster: The Active-Active cluster runs different SQL Services (instances) on both servers. Each server has its own SQL Service and storage.  If any of the servers fail, the other server takes over and runs both SQL instances.

Advantages of SQL Server Failover Clustering

• Failover clustering safeguards SQL Server instances from server or hardware failures.

• It supports both Automatic and Manual failover.

• It can cluster multiple servers for high resiliency.

Disadvantages of SQL Server Failover Clustering

• Failover clustering requires a complex setup and specializations on Windows. For the Linux SQL Server, you need to use a pacemaker cluster.

• The passive server remains idle in an active-passive configuration.

• It requires shared storage between cluster nodes.

SQL Server Always On Availability Groups

The SQL Server Always On availability groups feature is a combination of failover clustering and database mirroring. It defines the availability group (AG) that can failover with both automatic and manual failover. You can configure two or more servers in the failover cluster with individual SQL Services running on each server. Both servers are configured with similar configurations at the hardware or disk level. The secondary databases can be used to offload the primary database load for reading requests and database backups.

SQL Server Always On supports two modes.

• Synchronous Data Commit: In synchronous mode, the transaction must commit on the secondary database followed by the primary database. It ensures that you always have a synchronized database copy without any difference between the primary and secondary databases. It is suitable for the critical database where you cannot afford any data loss.

• Asynchronous Data Commit: In asynchronous commit mode, the primary database does not wait for acknowledgment from the secondary. It is suitable for optimized performance and disaster recovery solutions.

Benefits of SQL Server Always On Availability Groups

• SQL Server Always On supports both high availability and disaster recovery.

• It supports database synchronization and failover with Zero Data Loss Protection.

• Both Automatic and Manual failovers are supported.

• It does not require shared storage similar to the windows failover cluster.

• It supports Automatic Page Repair from the secondary replica in case of any issues with the primary.

• You can offload read-only workloads to the secondary replica. Therefore, it is suitable for enhanced performance.

Disadvantages of SQL Server Always On Availability Groups

• The system databases (Master, Model, and MSDB) cannot be added to the availability groups.

• You cannot take a full database backup (except COPY_ONLY) on the secondary availability group replica.

Wrapping Up

SQL Server provides multiple options for high availability and disaster recovery. Choosing which high availability solution is the best fit for you comes down to understanding your specific requirements and comparing those to the pros and cons of each option. While some options don’t have automatic failover capabilities, ClusterControl supports the deployment of SQL Server Always On clusters on Linux, with automatic failover. 

If you’re looking for more resources on how to install and configure SQL Server on Linux, check out this step-by-step guide. You can also easily deploy SQL Server on Linux directly from the ClusterControl GUI, and you’re free to give ClusterControl a try for 30-days, no credit card required.

For more updates and best practices for managing your open-source-based databases, be sure to follow us on Twitter and LinkedIn and subscribe to our newsletter.