Data_engine cannot connect to SQL Server database when configured to connect to an Always-ON, High Availability, availability group listener.

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

When the Data engine is restarted it sometimes fails and in the data_engine log it reports that it has obtained a read only connection to the database.

The "Test Connection" button is greyed out in data_engine.

The data_engine is configured to connect to the SQL Availability Group listener.

Cause

• The availability group is configured to allow all connection attempts to the secondary replica.
• This is not the default setting for an availability group. the default is to not allow connections to the secondary replica. 
• In this configuration SQL is allowing connections to the secondary replica even if they are read-write connections.

 

Environment

Release: CNMSPP99000-8.4-Unified Infrastructure Mgmt-Server Pack-- On Prem
Component:

Resolution

Have the availability group configuration changed to not allow connections to the secondary replica 

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/about-client-connection-access-to-availability-replicas-sql-server#HowConnectionAccessAffectsConnectivity 

 to configure this:

1. Open SQL management studio and expand AlwaysOn High Availability - Availability Groups. 
2. Right click the desired group and select properties.
3. In the Availability Replicas section change the value for "Readable Secondary" to NO for all replicas.

This will prevent any accidental connections to the secondary by the data_engine.

Additional Information

SQL Server Always on High Availability Groups are documented by Microsoft^® here.