Skip to main content
Version: 6.12

SQL Server Clustering Recommendations

As part of a wider High Availability strategy, SQL Server must have redundancy capabilities. The Microsoft recommended approach to this is to use one of the SQL Server Always On options.

Initial Reading​

Windows Server Failover Cluster with SQL on Azure​

Patriot supports a full cloud based Azure Failover Cluster setup, which is set-up in a very similar manner to a traditional stand-alone WFC. However the shared storage requirement is replaced with Azure Share Disks (Zone-redundant storage type).

note

Azure SQL and Azure SQL Server are not currently supported by Patriot.

Always On Failover Cluster Instances​

Requires: SQL Server Standard / Enterprise Edition (2016+). In Standard Edition, only two nodes are allowed.

Always On Failover Cluster provides SQL Server instance-level failover. If the active SQL Server node fails, it is automatically started on another node. The database files themselves are stored on shared storage and automatically connected to the active node. A Storage Area Network (SAN) shared storage system with built-in redundancy is highly recommended.

As the entire SQL instance is failed over as a unit, this includes all databases, logins, and other instance-level items automatically.

Because the FCI by definition can only run on one node at a time it does not require separate licensing of SQL for each database instance, as they are the same instance and can't be running at the same time. This may make licensing the SQL server cheaper but this is likely offset by the hardware requirements of shared storage.

Patriot cluster

Always On Availability Groups​

Requires: SQL Server Standard / Enterprise Edition (2016+). In Standard Edition, only one replica is allowed, and only a single database can be added to an availability group.

The full capabilities of AoAG requires an SQL Enterprise license which can be expensive. SQL Server Standard 2016 has limited support for Availability groups. The restriction of having only a single database makes it unsuitable for customers using the archive database.

The replica server can be a local server with synchronous commit or a remote disaster recovery server with asynchronous commit.

No shared storage is required for a Basic Availability Group. Each server keeps a local copy of the database and applies the same transactions to it in real time. If this is synchronous commit then a fast connection of 10Gbs or higher is required to prevent slowing down the live database while it waits for transactions to be confirmed on the replica. If you are also clustering the Patriot services then some small amount of shared storage is ideal to ensure no data is lost during failover.

Additionally, instance level items such as logins will need to be manually synchronised to the replica(s)

Examples​

Basic Availability Group (SQL Standard)​

The cheapest and simplest high availability configuration that can be achieved is a 2 node set up with SQL standard servers with the Patriot Database in a Basic Availability Group. Only SQL Server Standard 2016 or greater is required for this setup, significantly reducing the total cost of this configuration.

Each node is a stand-alone server (no Windows Clustering). A single replica is configured, with its own copy of the data. In SQL Standard, the archive database is not supported. The Data Service connects to the AG Listener and is automatically routed to the active replica.

The Patriot Services should be clustered alongside this set up.

Patriot cluster

Enterprise Availability Group With 3+ Nodes​

Requires: SQL Server Enterprise Edition (2016+)

This is the most versatile and functional solution, however it is expensive as it requires SQL Enterprise licenses. Besides this no expensive equipment or complex set up is strictly required.

Compared to the Basic Availability Group, archiving is supported as both databases can be added directly to the same availability group.

An Advanced Availability Group can be configured on up to 16 nodes. Each of these nodes can be configured with different commit and readability modes which allows you to combine both HA and DR within the single availability group.

You can easily add or remove nodes from the availability group once it is configured. The machines need not be identical as long as they can be added to the cluster. No shared resources between servers are required but any servers that are in synchronous commit mode require a high speed and low latency connection to the principal server.

The below example shows a comprehensive HA/DR setup

  • The primary Data Service (and Task Service, not pictured) are in a Windows Failover Cluster across the two primary site nodes.
  • SQL Server using AoAG in synchronous mode provides HA at the primary site
  • A secondary standby copy of Patriot is configured at the DR site, which can be manually activated in a DR scenario
  • An additional asynchronous replica of the SQL database(s) is kept at the DR site

Patriot cluster

Multi Subnet Clustering​

Patriot software supports SQL Multi-Subnet clusters. If you are using a Multi-Subnet SQL cluster, Patriot should be configured to enable Multisubnet failover. This setting improves failover detection and recovery so it should always be enabled when using this server setup (Microsoft recommend using this setting for all cluster types even if using a single subnet). To enable this mode, edit the AppSettings.json file in the Patriot Data Service installation folder. Look for the Failover section, and locate the following setting:

{
  "DataService":{
    "SqlConnection": {
      {
        "Failover":{
          "MultiSubnetFailover" : false,
        }
      }
    }
  }
}

Change the value to True to enable the multisubnet support. If this setting is missing from your configuration file, add it below the other settings, or contact Patriot support for assistance.

The changes will take effect the next time that the Data Service is restarted.

note

Multi-Subnet clustering is only supported in the Enterprise edition of Patriot or if you have the Enterprise Database module registered to your license.

Failover Procedures​

A SQL cluster has automatic built-in error recovery. If one of the nodes should fail, another node will take over automatically. This will have the effect of making SQL unavailable momentarily. The data service is designed to handle this situation. You may get notified that a signal failed to log, but it will get logged soon after on a retry once SQL comes back online. No signal or data loss will occur in this situation, and no manual intervention is required.

Alternative Options​

  • Database Mirroring. Microsoft have indicated their intention to deprecate SQL Database Mirroring in future versions of SQL Server.
  • SQL Server Log Shipping. While still a common practise for transferring data to DR when not using AoAG, it can be used for limited HA as well. In this case, manual intervention is required to start the secondary server if the primary becomes unavailable. Some data loss is likely when using Log Shipping as the data is only copied at fixed intervals.