sys.fn_hadr_backup_is_preferred_replica causes error 41070 in SQL Server 2017 CU15
After installing CU15 on a SQL 2017 2-Node Always On Availability Group Cluster (Windows Server Failover Clustering Mode), sys.fnhadrbackupispreferred_replica throws the following exception when run against a database that is in an availability group:
Msg 41070 - Configuration data for the availability group with Windows Server Failover Clustering (WSFC) resource ID 'GUID' is not found in the WSFC data store. The availability group may have been dropped, or a previous CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation has failed. Please use DROP AVAILABILITY GROUP command to clean up previously failed operations before retrying the current operation.
Uninstalling CU15 (reverting back to CU14) allows sys.fnhadrbackupispreferred_replica to execute without throwing the above exception.
There's an on-demand hotfix update package for SQL Server 2017 CU15 available now that can be installed on top of CU15 to resolve this issue: https://support.microsoft.com/en-us/help/4506633
Phil Morrow commented
This is a known issue if you review the CU15 release notes. Microsoft recommends not installing this CU if this is an issue for your environment. It's stated it will be fixed in the next CU.
Same issue here with Ola's solution.
same issue here +
Garry Pshonik commented
After additional troubleshooting, this is isolated to Always On configurations where the backup preference is set to Prefer Secondary or Secondary. The function works as expected when set to Primary or None.
The SQL Server backup solution we use (Ola Hallengren's) is broken due to this error, resulting in no backups being created on servers with CU15 installed. I can additionally confirm that when a node is retained on CU14, the `fn_hadr_backup_is_preferred_replica` call works as expected there, while it fails on all the other nodes running CU15.
same issue here