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.
Requires: SQL Server Standard / Enterprise Edition (2016+). Note that in Standard, 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.
Requires: SQL Server Standard / Enterprise Edition (2016+). Note that in Standard, 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)
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.
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
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 PatriotService.exe.config file in the Patriot Data Service installation folder. Look for the <PatriotService.Settings1> section, and locate the following setting:
<setting name="SQLMultiSubnet" serializeAs="String">
<value>False</value>
</setting>
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.
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.