Error Message Using SQL Server Always On Group for Source when a Replica is set to Read Only

[Dana_Baldwin]

[SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 42000 NativeError: 978 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The target database ('XYZ') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. [1022502] (ar_odbc_conn.c:563)

Environment

• Qlik Replicate 7.0 (November 2020)

Resolution

1. Make sure you meet all requirements for always on in the user guide: Working with Microsoft SQL Server AlwaysOn Availability Groups
2. If you do not meet all requirements, try below:
   
   The default behavior of Replicate is to make a connection with all the replicas.
   
   This behavior can be controlled by an internal parameter "AlwaysOnSharedSynchedBackupIsEnabled".
   
   Edit your source endpoint, advanced tab, internal parameter add "AlwaysOnSharedSynchedBackupIsEnabled" and check the box.
   
   This will allow the task to connect as if it were a standalone server.
   
   
3. Or This error is received when one of the secondary cluster nodes is set to read-intent_only. Setting it to "Yes" should solve the issue.