One of customers changed the value returned from @@SERVERNAME. SQL Server works no problem, however an unexpected behavior appeared. Changing the value for @@SERVERNAME, caused the backups to fail.
Looking at the maintenance jobs, found all jobs completed successfully and without issues. However, upon looking at the database’s statics it states no backups completed.
Because the database in question is part of AlwaysOn Availability Group (AG); SQL Server executes sys.fn_hadr_backup_is_preferred_replica to determine if the backup should take place on the current node. However, it returns value of 0 for all databases, if the preferred replica is set. Because, the script makes a check that is running on the server that is preferred. It does this by comparing the value to @@SERVERNAME to value of replica_server_name in sys.availability_replicas. Because value will never match, it skips the database on both primary and secondary replica.
I have created a Microsoft Connect article (link); asking this little bit of information to be added to Books Online article (link). There was a request submitted by Ola Hallengren (Blog | Twitter), which was closed as Won’t Fix (link). Please vote!
This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.
If you understand Availability Groups I wouldn’t think that this was a good idea in the first place. What version of SQL was involved? I expect you tried this on 2012/2014 and even 2016 knowing you.
Heh, we were playing with SQL 2012. Not many things break with @@ServerName values changing, but I had done quite a bit of research before we went forward with change. I missed Microsoft Connect (do’h). Haven’t tried it on SQL 2016, because my customer is just getting to SQL 2014. And I kinda destroyed my SQL2016 lab before holidays, so have to rebuild it :).