Steps to reproduce:

1. Zabbix Agent 2
2. Use zabbix-agent2-plugin-mssql.exe mssql plugin
3. Set MS SQL Availability Group, Prefered Backup Secodary AG Replica
4. Check for backup status MSSQL Template

Result:

Agent shows backup have not been executed, because it only checks backups on Primary Replica

Expected:
Agent should respect the  AG Backup preferences config, and check on the Replica, that was set as prefered

 

 

 

Details:
Problem: MS SQL 2022 Availability Group, when in AG I set that Secodary AG Replica is the Prefered Backup Replica (see pic bellow)

In this case MSSQL plugin wrongly states, that backup has not completed on Primary Replica on AG Databases.

After some research I checked the query, that Zabbix MSSQL plugin sends:
**

 
https://git.zabbix.com/projects/AP/repos/mssql/browse/plugin/queries/last.backup.get.sql
 
this query only checks, if the AG Replica is primary --> then it returns all databases,
if the AG Replica is secondary --> then it returns only local databases.
Iz ignores the AG Backup preferences config.

So it would make sense to rewrite the query "last.backup.get.sql":
(I checked; it works on SQL 2016 and later)

------------------------------------------------------------

• • • 1. Original hardcoded Query*
      ------------------------------------------------------------
      **
      Code:
      SELECT bs.database_name as dbname,
      [type],
      DATEDIFF(SECOND, bs.backup_finish_date, getdate()) as time_since_last_backup,
      (DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)) as duration,
      db.recovery_model as db_recovery_model
      FROM msdb.dbo.backupset as bs
      LEFT JOIN sys.databases as db ON bs.database_name = db.name
      WHERE bs.database_name not in (
      SELECT AGDatabases.database_name AS Databasename
      FROM sys.dm_hadr_availability_group_states States
      INNER JOIN master.sys.availability_groups Groups
      ON States.group_id = Groups.group_id
      INNER JOIN sys.availability_databases_cluster AGDatabases
      ON Groups.group_id = AGDatabases.group_id
      WHERE primary_replica != @@Servername OR primary_replica is NULL
      ) and db.name is not NULL
      GROUP BY bs.database_name,
      backup_finish_date,
      [type],
      backup_start_date,
      db.recovery_model
      HAVING backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
      AND bs.type = [type]
      )
      ORDER BY bs.database_name
      ------------------------------------------------------------
    • 2. Corrected Query*
      ------------------------------------------------------------
      **
      Code:
      WITH AGPreferredDatabases AS (
      SELECT name
      FROM sys.databases
      WHERE sys.fn_hadr_backup_is_preferred_replica(name) = 1
      )
      SELECT bs.database_name as dbname,
      [type],
      DATEDIFF(SECOND, bs.backup_finish_date, getdate()) as time_since_last_backup,
      (DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)) as duration,
      db.recovery_model as db_recovery_model
      FROM msdb.dbo.backupset as bs
      LEFT JOIN sys.databases as db ON bs.database_name = db.name
      INNER JOIN AGPreferredDatabases agdb ON db.name = agdb.name
      WHERE db.name is not NULL
      GROUP BY bs.database_name,
      backup_finish_date,
      [type],
      backup_start_date,
      db.recovery_model
      HAVING backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
      AND bs.type = [type]
      )
      ORDER BY bs.database_name