SQL Server monitoring - monitoring of instance stops on secondary replicas

JesperM

Hi,

I'm evaluating Xormon NG for SQL Server monitoring and yesterday I found an issue when monitoring secondary replicas in Always On Availability Groups.

In standard edition of SQL Server the database on the secondary replica is not available for queries.

This is also the default setting in enterprise edition but there it is possible to have the databases available for read-only queries.

There seems to be some sort of unhandled exception when Xormon tries to retrieve information from the secondary replica which makes it stop monitoring the instance all together.

Here are some entries from the error log:

2024-08-14 08:20:00: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The target database, 'test-db', 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. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Serv (SQL-42000) at /home/xormon/xormon-ng/server-nest/microservices/sqlserver/sqlserver-service.pl line 360.

2024-08-14 08:15:00: Can't use string ("err") as a HASH ref while "strict refs" in use at /home/xormon/xormon-ng/server-nest/microservices/sqlserver/sqlserver-service.pl line 187.

Even if Xormon is not able to get information about the databases when they are configured like this the other monitoring of the instance should still work?

What are your thoughts about this?

Thanks

damerva

Hello Jesper,

when connecting we specify that intent is read only, but unfortunately that seems to not be enough

With this patch the script will not crash and will pool data as expected (changes from yesterday are included and will be included in versions going forward)

https://download.xormon.com/patch/1.5-100-g9e21e/sqlserver-service.pl.gz

Gunzip it and copy to this path in your Xormon application directory (755, xormon owner):

server-nest/microservices/sqlserver/

-rwxrwxr-x 1 xormon xormon 34610 Aug 14 11:54 sqlserver-service.pl

If your web browser gunzips it automatically then just rename it: mv sqlserver-service.pl.gz sqlserver-service.pl

2. restart the application after as user xormon, ( Xormon application directory -> ./xormon.sh restart )

3.Let us know if it works for you as expected

JesperM

Thanks, I will test and get back to you with the results.

JesperM

Thanks, after installing the patch it works fine.

Information about non AG-databases is collected as well as system wide information on the secondary replica.