Query Plan & Top SQL collections fail in SQL Server availability groups (4289941)

In SQL Server, when a database is part of an availability group and the instance has databases that are secondary servers and are currently accessible for connections and when the application intent is set to read only then live features such as Generate Query Plan (from the plan cache) and Top SQL collections will fail because the database is an AlwaysOn Secondary with the Readable Secondary feature set for READ-INTENT ONLY

If an application uses ApplicationIntent=ReadWrite and the secondary replica location is configured for read-only access, the connection will fail.

In SQL Server Management Studio, check the properties of the Availability Group

READ INTENT would look like this.

When retrieving an execution plan and trying to generate a new plan, an error similar to the following may appear.

"No execution plans found"

"Click here to Generate the estimated plan" (in blue)

ERROR [http-0.0.0.0-8080-4] STDERR - Caused by: com.quest.qsi.fason.framework.connections.common.exceptions.JDBCServerException: Failed to run TOP1000_DM_EXEC_QUERY_STATS_ENTRIES collection. Failed to execute query on instance [MYHOST-MYINSTANCE], user account [.]. Reason : [[DataDirect][SQLServer JDBC Driver][SQLServer]The target database ('MYTESTDB') 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.

An Agent Status Property (ASP) field was added as part of SSFOG-188 to define the ApplicationIntent manually.

By default, the value will be "NONE" to support the currently running SQL Server agents. For special cases where the replica location is configured for read-only access, set "ReadOnly" for this ASP.

Change the agent property "DB Connection ApplicationIntent used to enable read-only routing" to "READONLY":

• Go to the Administration | Agents | Agent Status dashboard
• Select the problematic DB Agent 
• Click on Edit Properties and then on Modify the private properties for this agent.
• Find the property DB Connection ApplicationIntent used to enable read-only routing
• Change it from NONE to READONLY  (this value is case insensitive)
• Save the change and deactivate/activate the agent.

There is also the "READWRITE" option which is the default value for databases that have this parameter supported. (legacy databases does not know this property)

IMPORTANT: The SQL Server agent must be restarted after making this change.

 

An Enhancement Request has also been created SSFOG-1029 to have a drop down field with the available values for this property in the SQL Server agent installer, the Test Connection functionality in the Agent Administration panel, and the CLI (Command Line Installer).

Currently the values are NONE (default), READONLY and READWRITE. This is planned for a future release.