About the SQL Server AlwaysOn Requirements

The Plant Applications modules support the Microsoft SQL Server AlwaysOn feature that provides a high-availability and disaster-recovery solution for SQL servers. The feature acts as an enterprise-level alternative to database mirroring and maximizes the availability of a set of user databases for an enterprise. For more information, refer to the URL https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/ms190202(v%3dsql.110).

Note:

When the SQL Server is in an AlwaysOn environment and to replicate CDC Jobs on the SQL server nodes, run below sql command manually on secondary node.


Use <<DataBaseName>>
GO
EXEC sys.sp_cdc_add_job @job_type = 'capture';
EXEC sys.sp_cdc_add_job @job_type = 'cleanup';

See Run the Script Manually to Support High-Availability Data Replication (HADR) on a Secondary Node.

About Replicating Logins and Jobs for the SQL Server Nodes

For the high-availability solutions to work, you must replicate all the user logins (including the default logins ComXClient, ProficyConnect, and ProficyDBO) and all SQL Server Agent Jobs of the primary replica of the Plant Applications database (SOADB) to all the secondary replicas of the SQL Server instance (nodes used in a cluster environment). In addition, you must create replicated user logins with security identifiers (SID) that match those of the primary replica of the Plant Applications database. For more information, refer to the URL https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/logins-and-jobs-for-availability-group-databases?view=sql-server-2017.

About the Tested High-Availability Solutions

The following table describes the high-availability solutions tested for Plant Applications modules.


High-Availability Solution	Cluster Type	SQL Server Node 1	SQL Server Node 2	SQL Server Node 3
Failover Cluster Instances	Single Subnet	Network A	Network A	Not applicable
Failover Cluster Instances	Multi-Subnet	Network A	Network B	Not applicable
Availability Groups	Single Subnet	Network A	Network A	Not applicable
Failover Cluster Instances + Availability Groups	Single Subnet	Network A	Network A	Network A
Failover Cluster Instances + Availability Groups	Multi-Subnet	Network A	Network B	Network B
Failover Cluster Instances + Availability Groups	Multi-Subnet	Network A	Network B	Network A

Note:

The performance of a high-availability solution in a multi-subnet cluster environment depends on the complexity of the network.