Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.

Going through the Availability Groups options, we can notice that we have distinct backup options:

The available options can be confusing, if you never tried them before. So let’s take a look on each one…

Prefer Secondary

This option is very conceptual. Basically, you can run the backup command from any replica!

“Automated backups for the availability groups should occur on secondary replica (…)”

We can easily test this by running a backup command from the Primary replica:

The result:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 2.
100 percent processed.
Processed 25 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 2.
BACKUP DATABASE successfully processed 24345 pages in 39.265 seconds (4.843 MB/sec).

Yes, it worked! As it would work from any other replica, being a secondary or not! But, what is the reason behind this? As I said before, the keywords are “Automated” and “should”.

Translating this, we can understand that “Should” would mean “yes, this would be good to run in a secondary. If not, that’s ok…”.

What about the “Automated” keyword? This case is different. SQL Server assumes that when you run a BACKUP DATABASE command, like we did, you know what you are doing. Basically, you set the backup rules, so if you are doing the backup on the primary replica, you assume the risk. In other hand, an automated backup would be done by using the beloved SQL Server Maintenance Plan. Let’s analyse this:

I created a simple Maintenance Plan, with a backup task including all the databases in the instance. You can notice that there’s a warning in the bottom of the window.

“This backup type is not supported on a secondary replica and this task will fail if the task runs on a secondary replica.”

Why this? Because we are including databases that are part of an Availability Group in the plan. The problem here is simple: SQL Server does not support a regular backup to be made on Secondary replicas. In this case we are dealing with the primary one, but in case of a failover, the backup job is going to fail!

How can we solve this? We need to go to the tab “Options” and tick the “Copy-only backup” option:

For more information about this you can check the Microsoft documentation on Active Secondaries: Backup on Secondary Replicas

Going back to the “Automated” keyword, if we press the “View T-SQL” button, we will be able to explore the code generated to execute the backups. Based on my lab, I’ll get the following, for the AdventureWorks2016 database:

Code starts with the declaration and value attribution of a variable called “preferredReplica”:

By running the select, present in code, I will get the following :

This matches with the configuration, as we defined that this Availability Groups would backup from the Secondary replica and, in this case, we are in the Primary replica. So the following function returns 1 if you are in the preferred replica to perform backups, and 0 if not:

Continuing with the code analysis, we have now an IF controlling the execution of the actual BACKUP DATABASE command:

Here is the point where we prove that the “Automation” is just conceptual. Basically, the SQL Server Maintenance Plan, is AG aware, and it has the backup task ready to interpret the backup options of the Availability Group. Knowing this, what happed if we run a simple (without IFs) BACKUP DATABASE command in the primary? It’s going to work! So, don’t be “eluded” thinking that set the backup preferences is enough… There are more things to do!

One of my suggestions is use the heavily tested and community approved scripts from Ola Hallegren, which are Availability Groups aware, and very smart and flexible!

Secondary Only

Continuing with the analysis, we can now check the second option “Secondary Only”, which the description states that the backups MUST occur on a Secondary Replica. Let’s test it:

First, connect to the primary replica, I’ll try to execute a BACKUP DATABASE command:

The output:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 3.
100 percent processed.
Processed 27 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 3.
BACKUP DATABASE successfully processed 24347 pages in 12.018 seconds (15.827 MB/sec).

OMG! It worked AGAIN! Why?? I set the preferences to backup ONLY in a Secondary replica and it worked in a Primary!! What’s wrong here??

Again, this is just an illusion 🙂 Backup preferences means NOTHING if you don’t understand the reason and how to deal with this…

Check the part 2 to continue reading…

• Author
• Recent Posts

Murilo Miranda

Murilo Miranda is a Luso-Brazilian blogger and speaker. SQL Server MVP, living in the UK. Nowadays he's Database Consultant at Pythian, company based in Ottawa - Canada.

With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,

View all posts by Murilo Miranda

Latest posts by Murilo Miranda (see all)

• Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
• Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
• AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015