Came across an issue where are AG failed over, but 4 of 47 databases decided the local secondary db replica was not preferred and set their preferred replica to DR.
AG backup preferences had been correctly set, and in the past this database AG had happily failed over multiple times without issue.
The AG housed 47 databases, with only 4 decided to go to DR??
In the end I have now idea why or how SQL decided that these 4 should be preferred at DR site, and the other 43 were on the local secondary.
In the end I resolved by manually forcing a failover back to the current local secondary replica (DB02). When this was the primary, I then failed back to DB02. I did this just to ensure there wasn’t an issue failing over in a certain direction.
To check that all databases were correctly set I created the following scripts that will list the value of sys.fn_hadr_backup_is_preferred_replica
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; DECLARE @dby VARCHAR(max); SET @dby=DB_NAME(); SELECT @dby;SELECT sys.fn_hadr_backup_is_preferred_replica (@dby);'
This made it easy to confirm all servers and database were correctly setup.
Still none the wiser how it can get so muddled.
