Introduction

Always On Availability Groups (AOAG) is a replication and high-availability technology for Microsoft SQL server.

"Always On" is an umbrella term for the availability features in SQL Server and covers both Availability Groups and Failover Cluster Instances. "Always On" is not the name of the AG feature.

Availability Groups were introduced in SQL Server 2012. There was some concern that Microsoft was deprecating failover clustering. As far as our research can find, only Database Mirroring was deprecated in SQL Server 2012. Failover Clustoring remains a supported configuration.

Microsoft: Business continuity and database recovery - SQL Server

MS SQL Tips: What is SQL Server Always On

Request Drivers

Some modern backup utilities can operate outside the VM, and will backup the raw storage used by the Database server. These technologies are not compatible with the failover cluster design, or are risky and require lenghty recovery processes which aren't conducive to OT business continuity.

IT/OT convergence means that some customers want to standardise their MS-SQL deployments, and have already landed on AOAG as their preferred solution.

Product History

The Enterprise SCADA Historian was designed to run against a Failover Cluster, comprised of two nodes sharing a common storage appliance. This used to be fibre channel connected SANs, and lately has been iSCSI connected NAS devices, which were cheaper. The fundamental design hasn't changed: two nodes in a cluster sharing a single RAID storage, with only one node being HOT at any moment. In OASyS DNA, a shared IP floats between the nodes to provide a single logical endpoint to which clients connect.

Database backups are done at the application level: SQL queries were run and output to files that were archived onto external devices or shipped to another network location.

There are reasons for this design choice that were to do with the storage and replication technology available at the time. Also, the design was aimed to keep the SQL server as simple as possible, including its maintenance. For example, the transaction logs are truncated on checkpoint by default in the Enterprise SCADA Historian setup scripts, in order to prioritise stability over speed as a full transaction log can wedge the SQL server, which can cause disruptions to the control room. This was a nod to the fact that SCADA administrators were not DBAs, and needed something simple and reliable to "just work", while they focused on other tasks.

Product & Implementation Impacts

AOAG requires the Enterprise grade MS-SQL server, and requires transaction logs to not be truncated on checkpoint. This will impact the hardware selection and sizing, as there are now two copies of the databases, and database transactions will grow. It will also impose monitoring and maintaince responsibilities to the customers' DBA overseeing the OT technology. There may also be impacts to network design, as the two nodes in the AG will now be replicating the databases over the wire.

As IT/OT convergence continues, the variability in the user-base is growing. There are still the smaller customers that do not have a dedicated DBA, and must manage the entire system and infrastructure with a small OT team. While others are large enough that they may have dedicated DBA teams for OT systems.

Similar to our strategy pivot to not limit the choices our customers have when selecting cybersecurity solutions, and also the iniative to be a "better Active Directory citizen", so too should we support our customers' implementing and supporting the SQL server architecture that their IT/OT teams deem to be best practice for them.

Roadmap

Currently, only SQL Failover Clustering is tested by R&D as part of the release hardening. Some preliminary research indicates that there may be product code changes needed to fully support AOAG in terms of Performance, Stability, Reliability, Failure Modes, Recovery and interoperability with products such as Datapump, System Monitor, Data Access Layer (DAL).

The architecture group has done preliminary research into AOAG, and our Practice has performed some smoke tests. At this time we are not able to endorse AOAG with any version of Enterprise SCADA up to version 2024.

Customer input is being requested via this survey: MS Office Forms: Survey on AOAG in the customer base

Requirements

As these requirements are refined they will be added here. So far:

• Must support AOAG in the Enterprise SCADA Historian for every database installed by the Enterprise SCADA platform product.

  • Measurement Advisor is out of scope at this time

  • At this time it is assumed that all databases will be in a single AOAG.

• Optimize design for, in order of importance

  • read/write query performance for applications as they are written today (i.e. no changes to applications if possible)

  • recovery time reduction

• Availability group limited to a single site.

  • Datapump continues to be the solution between sites

  • Must therefore continue to work with datapump

• Support for legacy Failover Clusters Instances will continue

Out of Scope

No change to any products to work against the secondary replica.

Support for third party backup solutions

No upgradability from FCI to AOAG, other than the ability to support both at the same time to assist in the migration. No docs on how to perform the migration.

DBA documentation for how to create, manage, and backup the AOAG - product will be told where it is so that it can install its databases.

Questions

- Speed versus data integrity?
- Who controls primary AG?
- AOAG failover as seperate from Enterprise SCADA Historical failover?
- Does the product ship today with a database backup solution?