Data collection errors related to secondary database availability.

[windsorkelly]

Component

SQL collection scripts

Performance Monitor Version

2.0.0

SQL Server Version

Microsoft SQL Server 2016 (SP3-GDR) (KB5068401) - 13.0.6475.1 (X64) Oct 4 2025 05:49:46 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Windows Version

Windows Server 2016 Standard

Describe the Bug

Getting data collection errors due to my secondary database not allowing read only intent.

This is how I updated database_configuration_collector to eliminate the errors.

alter database_configuration_collector - Copy.txt

Thank you for the great product.

Steps to Reproduce

Install product via GUI. Errors when data collection runs at the end of the process.

Expected Behavior

I would expect that data collection would ignore secondary databases the don't allow read only intent.

Actual Behavior

Data collection reports errors attempting to access database that are not readable.

Error Messages / Log Output

From the installation log:  
[ERROR]   database_configuration_collector: Database: DaDatabase - The target database, 'DaDatabase', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access...

Screenshots

No response

Additional Context

No response

[erikdarlingdata]

@windsorkelly I don't have an availability group to test this against. Would you be willing to work on it, since you obviously do?

[windsorkelly]

Yes, I will see if I can figure it out.

[erikdarlingdata]

Fix is merged to dev in PR #345. We added a filter to skip AG secondary databases with READ_ONLY connections in all 4 cross-database collectors (both server-side install scripts and Lite):

• install/39_collect_database_configuration.sql — scoped config cursor
• install/09_collect_query_store.sql — Query Store discovery cursor
• Lite/RemoteCollectorService.ServerConfig.cs — scoped config database list
• Lite/RemoteCollectorService.QueryStore.cs — Query Store discovery cursor

Databases on primaries, non-AG databases, and secondaries configured with ALL connections are unaffected.

This will be in tonight's nightly build: https://github.com/erikdarlingdata/PerformanceMonitor/releases/tag/nightly

If you'd like to test sooner, you can also replace the two stored procedures directly from the dev branch:

• 09_collect_query_store.sql
• 39_collect_database_configuration.sql

Just run the ALTER PROCEDURE statements against your PerformanceMonitor database. Let us know if it resolves the errors on your environment!

[josejulioduarte]

Thank you for your consideration, and I apologize for my oversight in not mentioning this, as I realized it only happens in some cases, and since it was within the explanation, I didn't include it.

[josejulioduarte]

In script 09_collect_query_store.sql give a error, because the fields (collect_query and collect_plan) aren't present in the table config.collection_schedule
Msg 207, Level 16, State 1, Procedure query_store_collector, Line 180 [Batch Start Line 32]
Invalid column name 'collect_query'.
Msg 207, Level 16, State 1, Procedure query_store_collector, Line 181 [Batch Start Line 32]
Invalid column name 'collect_plan'.
Query Store collector created successfully
Collects comprehensive runtime statistics from all Query Store enabled databases

[erikdarlingdata]

@josejulioduarte The dev branch currently has multiple fixes on it for various issues. You're getting that error because another change added those columns, and things on the dev branch aren't in a state to have it picked up by doing an install with the upgrade path. If you go into the upgrade folder, you'll see 2.0.0 > 2.1.0 with the file 01_collection_schedule_optional_text in it. That will fix the errors.

[josejulioduarte]

Okay, thank you, I'll be waiting.

[windsorkelly]

Still getting the error when running [collect].[database_configuration_collector] in SSMS: Error collecting scoped configs from DaDatabase: The target database, 'DaDatabase', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access… The cursor definition starting around line 159 is querying all databases regardless of which replica it runs on. *Replace this:* DECLARE database_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT database_id = d.database_id, database_name = d.name FROM sys.databases AS d WHERE d.database_id > 4 AND d.name != DB_NAME() AND d.state_desc = N'ONLINE' AND d.database_id < 32761 /*exclude contained AG system databases*/ AND d.database_id NOT IN ( SELECT d2.database_id FROM sys.databases AS d2 JOIN sys.availability_replicas AS r ON d2.replica_id = r.replica_id WHERE NOT EXISTS ( SELECT 1/0 FROM sys.dm_hadr_availability_group_states AS s WHERE s.primary_replica = r.replica_server_name ) AND r.secondary_role_allow_connections_desc = N'READ_ONLY' AND r.replica_server_name = @@ServerName ) ORDER BY d.name OPTION (RECOMPILE); *With this:* DECLARE database_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT database_id = d.database_id, database_name = d.name FROM sys.databases AS d LEFT JOIN sys.dm_hadr_database_replica_states AS drs ON d.database_id = drs.database_id AND drs.is_local = 1 WHERE d.database_id > 4 /* skip system databases */ AND d.name != DB_NAME() /* skip this database */ AND d.state_desc = N'ONLINE' /* online only */ AND d.database_id < 32761 /* exclude contained AG system databases */ AND ( drs.database_id IS NULL /* not in any AG */ OR drs.is_primary_replica = 1 /* primary on this replica */ OR d.is_read_only = 1 /* readable secondary on this replica */ ) ORDER BY d.name OPTION (RECOMPILE); I know you’re a Claude fan, so I should disclose that ChatGPT wrote this for me. :)

…

On Fri, Feb 27, 2026 at 12:14 PM josejulioduarte ***@***.***> wrote: *josejulioduarte* left a comment (erikdarlingdata/PerformanceMonitor#325) <#325 (comment)> Okay, thank you, I'll be waiting. — Reply to this email directly, view it on GitHub <#325 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/B676VQBCS5LFGM4A7HQELSL4OCCRLAVCNFSM6AAAAACWA6AGL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTSNZUGM2DKNRRGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Kelly Windsor ***@***.***