I created a PBM condition and policy in SSMS. The policy runs a T-SQL script using the ExecuteSql function against the Server facet to determine whether all databases that are expected to be a member of an availability group are in fact members of the group. The expected values are stored in a table. SQL below; it executes fine.
When I evaluate the policy (right-click | Evaluate) it shows success. The actual and expected values are zero. So far, so good.
The policy is configured to be in the Availability Replica warnings category so it can be evaluated by the High Availabiltiy dashboard (or the Test-SqlAvailabilityReplica cmdlet). When I run the High Availabilty dashboard it reports a policy execution running the policy but gives no further detail. See 2nd screen capture in image.
When I run the Test-SqlAvailabilityReplica cmdlet in Powershell it reports a value of False for the policy. All other policies report True (3rd screen capture). When I run the cmdlet with the -Verbose parameter I don't see the SQL for my policy being executed but I do see the SQL for the other policies. It appears that is returns False without being evaluated. I'm running as a user with sysadmin.
I'm stumped. Why is it returning False when it should be True? Any insight appreciated.
Condition:
ExecuteSql('numeric', 'SELECT
count(*)
FROM myDB.dbo.AvailabilityGroups ag
INNER JOIN myDB.dbo.AvailabilityGroupDatabases agd
ON ag.AvailabilityGroupId = agd.AvailabilityGroupId
LEFT JOIN
(
SELECT name,
database_name
FROM master.sys.availability_groups ag
INNER JOIN master.sys.availability_databases_cluster adc
ON ag.group_id = adc.group_id
) sag
ON ag.AvailabilityGroupName = sag.name
AND agd.DatabaseName = sag.database_name
WHERE sag.name IS NULL
AND sag.database_name IS NULL')
