Always On Availability Groups

An Always On Availability Group is set of separate SQL Server instances residing on different nodes in a cluster, which host replicas of the same databases. They ensure that the replicated databases are available at all times, even if the primary SQL Server instance fails.

One instance is designated as the primary, and all others are secondary instances. Read requests can be fulfilled by any of the instances, but all Update requests are fulfilled by the primary instance on its database only. Changes made to the primary instance are then propagated to all the secondary instances.

If the primary instance fails, a secondary instance assumes the primary role, taking over all Update requests on its database, and propagating new updates to the other secondary instances. When the failed instance has recovered it assumes the secondary role and synchronizes its databases with the others.

This takeover of responsibilities can be done automatically or manually, depending on the setup of the SQL Server instances in the Availability Group.

Uniface can work with an Availability Group just as it can with a regular MS SQL database, but you need to keep the following information in mind.

When Uniface's MSS connector connects to an Availability Group it must connect to an Availability Group Listener, not to a specific instance.

Therefore, when configuring a DSN with the ODBC Data Source Administrator utility for a connection to an Availability Group Listener, select the Multi-subnet failover checkbox. This ensures that the Listener makes the connection to the primary SQL Server instance, without you having to know which one that is.

If your application only requires Read access to the database, you can have the Listener make a connection to a secondary instance that is configured to allow read-only access, instead of to the primary instance. This improves overall database performance because Read requests are handled by a different server on its own database replica.

To use this feature, set the Application intent value in the DSN configuration to READONLY. By default, it is set to READWRITE and will thus connect to the primary SQL Server instance.

Updates can be propagated to the secondary nodes in synchronous or asynchronous mode.

When synchronous mode is used, a commit takes slightly longer, because the commit has to wait until the update has propagated to all secondary instances. This ensures that there is no possibility of data loss and all the secondary nodes are always up to date.

When asynchronous mode is used, the commit returns as soon as the updates have been made in the primary database. The secondary instances always lag behind the primary one, as do applications that have READONLY set as their application intent. If the primary instance fails, this can result in data loss in the secondary instances that have not yet received the updates.

The Listener does not automatically reconnect to another database instance if the one that is connected to fails. The failure is not transparent to Uniface and database requests will subsequently fail with a connection broken error.

To handle this situation, you can close the path and reopen it in ProcScript. The Listener will then initiate a new connection to another instance. For Read-Write access, this will only work if one of the secondary instances has automatically taken over the primary role.