CODEWITHSUNDEEP
sql-server-2012high-availability
JVC
JVC

Reputation: 570

fn_hadr_backup_is_preferred_replica always returning zero

I'm setting up a SQL Server 2012 "Always On" availability group and I am having issues with the backup maintenance plan.

I've tracked the problem down to fn_hadr_backup_is_preferred_replica always returning zero

I created a test database and ran the function, it returned 1. I then added the database to the availability group and the function returned zero. I've tried every option in Backup Preferences and in all cases on both primary and secondary instances, it returns zero. It never returns 1.

Upvotes: 2

Views: 2067

Answers (2)

Lord Darth Vader
Lord Darth Vader

Reputation: 2005

@@Servername of null can happen if there is no entry in sys.servers for server_id 0. check sys.servers with this:

select * from sys.servers where server_id=0

If this returns 0 rows you can add the local server with

sp_addserver '', local

you will need to restart the server for this to take effect.

You may also need to remove your server being > row 0 EXEC sp_dropserver 'old_name';

Upvotes: 0

JVC
JVC

Reputation: 570

We discovered the problem. It relates to changing the server name. The network engineer who built the cluster changed this value after the build. We started from scratch and it now works.

Upvotes: 2

Related Questions