The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, March 18, 2024 Login
Public

SQL 2017 Always-on no cluster 5/17/2018 12:57:24 PM

Setting up always-on availability group with no cluster.

So far works good, though some of our backup and monitoring queries began failing, specifically the function, sys.fn_hadr_backup_is_preferred_replica

Msg 35222, Level 16, State 0, Line 1 Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

We ended up working around this by checking the sys.availability_groups which has a column for cluster type, this way we can check if the always-on group has a cluster or not.

-- This leads to error
select [master].sys.fn_hadr_backup_is_preferred_replica('ao_test')
go
select
CASE WHEN dbrs.is_primary_replica = 1 and ag.cluster_type = 1 then 1
when dbrs.is_primary_replica = 0 and ag.cluster_type = 1 then 0
WHEN ag.cluster_type is null then 0
else Convert(int,[master].sys.fn_hadr_backup_is_preferred_replica(sd.[name])) end
as preferred_replica_backup
From sys.databases sd
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON sd.group_database_id = dbrs.group_database_id and dbrs.is_local = 1
left outer join master.sys.availability_groups ag
on ag.group_id = dbrs.group_id where sd.[name] = 'ao_test'

 


Blog Home