SQL Always On support

Issue

Is it possible to use SQL Server Always with Kofax TotalAgility databases

The TotalAgility DB consists of 3 parts when processing/completing jobs:

1. Main
2. Archive
3. Audit

These databases are by default in the same TotalAgility DB, but can be separated depending on customer requirements as assist with DB maintenance and also scalability.

Solution

If any of these DBs are separated, it is possible for single transactions to be occur over crossed databases and thus is not supported by SQL AlwaysOn.
SQL AlwaysOn doesn’t support cross-database transactions in the same SQL database instance.

In SQL Server 2016 and before, cross-database transactions within the same SQL Server instance are not supported for availability groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same availability group. Cross-database transactions are also not supported for database mirroring.

This is a SQL Server Always On limitation, and as mentioned in the KTA Best Practice guide if the customer wants to use SQL AlwaysOn to have Live, Audit and Archive objects in the same database.

We support:

• AlwaysOn FCI
• AlwaysOn AGs*

We do NOT support (before v7.8):

• *AlwaysOn AGs with Transactional\Merge Replication (where you are replicating the Reporting database and accessing as a HOT copy).

Level of Complexity 

High

Applies to  

References

Please find the following information from Microsoft:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/transactions-always-on-availability- and-database-mirroring