Advertisements
Home > AlwaysOn Availability Groups, SQL 2012, SQL 2014, SQL 2016 > Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

Changing @@SERVERNAME causes SQL Server Backups to Fail for AlwaysOn Availability Group

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.

image

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.

Advertisements
  1. Chris Wood
    January 14, 2016 at 8:22 am

    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.

    Chris

  2. January 14, 2016 at 8:58 am

    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 :).

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: