Fix wrong backup check for SQL Always on High availability Groups

[fabiozanella]

General information

I resume the @marcohald's Bug Fix with some small improvements.

I looked at the GitHub history and saw that This commit was introduced to watch only the primary replica. But usually, with Always on, the backup are performed on secondary replica (see Microsoft resource). Additionally, when you perform a failover to the second node, you lose track of previously performed backups.

Bug reports

Operating system name and version

SQL Always on High availability Groups on SQL Server 2019 Windows Server 2019

Detailed steps to reproduce the bug

1. Create a Backup of a High Available Database on Server1
2. Move the Database to Server2
3. You get a backup Error because no actual backup is found

Proposed changes

What is the expected behavior?

The Agent should return the Backup status, even if it is not the primary Server

What is the observed behavior?

The Agent does not return the Backup if it is not the Primary Replica

If it's not obvious from the above: In what way does your patch change the current behavior?

The Primary Server check is removed

List of changes

• Remove the filter to extract backups only from the primary replica.
• Simplify backup query.

I have read the CLA Document and I hereby sign the CLA or my organization already has a signed CLA.