Skip to content

SQL Server Always On#

Always On is a feature that provides a low-cost alternative to a Storage Area Network (SAN) as each Database Server uses its own local storage as shown in the figure below. The Always On feature is supported in the SQL Server Enterprise and Standard Editions (the Standard Edition does not support Availability Groups and is limited to two nodes Failover Cluster Instances). More information about SQL Server Always On can be found in the following URLs:

SQL Server Always On

SQL Server Always On Failover Cluster Instances#

The Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level - a failover cluster instance (FCI). A FCI is a single instance of SQL Server that is installed across different Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

SQL Server Always On Availability Groups#

The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On Availability Groups maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

SQL Server Always On in Multi-Subnet Setups#

Critical Manufacturing MES v11.2 supports SQL Server Always On across multiple subnets provided the following requirements are met:

With these settings, the listener registers only the active IP, enabling reliable client connections and cross-subnet failover without MultiSubnetFailover.

Info

Support for MultiSubnetFailover is planned for a future MES release.

Microsoft Active Directory Alternatives#

Using other DNS servers or different architectures may also work, assuming they follow the previous requirements.

Warning

Keep in mind that any alternative configurations are your responsibility and need to be managed and maintained independently.

Among the alternatives are:

  • Using other DNS services may work as long as they implement the RFC 2136 ⧉, allowing dynamic DNS updates. However, these may require custom configuration to ensure that the WSFC updates their DNS entries when performing failovers.
  • Other cloud-native services - such as AWS Route 53 or Azure Private DNS - may work but are more complex to set up and typically require custom automation via provider APIs because they do not implement RFC 2136.
  • Alternatively, you can leverage a Load Balancer that will only route traffic to the active replicas, assuming that it has healthchecking capabilities. The Availability Group Listener hostname must be resolved through any DNS Server to the Load Balancer's IP address. For more information, see the how to reduce failover times on AWS ⧉.