Start Here

Get in touch with a
TriCore Solutions specialist

Blog | May 13, 2015

Understanding Backups with SQL Server AlwaysOn High Availability Mirrors


INTRODUCTION:

With SQL Server’s AlwaysOn feature, Microsoft made great strides in the area of mirroring and high availability. Though there are many benefits to this new feature, for the purpose of this blog we will be focusing on AlwaysOn in relation to backup configuration (specific to the SQL Server 2012).

Understanding how the backups (and integrity checks) work with the primary and secondary replicas can go a long way in helping us to understand the best way to configure them.

We will not get into technical details on the scripting and full configuration options, however, will focus more on the high level concepts available to us and their utilization. For more technical details on the subject, BOL and MSDN are a great reference for requirements and language. If you do not have a good understanding of what AlwaysOn is – again, BOL and MSDN are a great reference on this subject.

 

The main points we will be focusing on in this discussion are:

  • Where can I take a backup?
  • What type of backups can I take?
  • How do I configure backup location preferences?
  • Where should I put my backups?

 

Where can I take a backup?

One of the great benefits of the AlwaysOn scenario is the ability to offload backup operations from the primary server to one of the secondary replicas. Simply stated, any replica (up to four) can be utilized for backup operations as long as it can communicate with the primary replica and it is in a SYNCHRONIZED or SYNCHRONIZING state. A few things should be taken into consideration before using a replica to perform backups instead of your primary server.

Backups taken on a replicas will not show up in the MSDB database backup tables of the primary server (or other replicas), as they don’t share an MSDB database. This needs to be taken into consideration when performing audits, researching backup history or utilizing scripts or logic that utilize these tables.

If you intend on using replicas for backups (utilizing preferences as well), the backup jobs will need to be configured on every instance/node in the AlwaysOn replication scenario where the preferences make it possible for a backup to take place. More of this will be discussed later.

What type of backup can I take?

This is a very important question when considering backup scenarios in an AlwaysOn group environment.

On the primary replica, backup types and solutions can be run just like any other scenario – including FULL backups, DIFFERENTIAL backups and TRANSACTION LOG backups. However, when it comes to taking backups on a secondary replica, there are some differences.

In relation specifically to databases participating in an availability group:

BACKUP DATABASE supports only COPY_ONLY operations for FULL backups on the secondary replicas. This type of FULL backup does not affect the log chain for transaction log backups, and does not clear the differential base. It should be noted that point in time recovery is still possible with a COPY_ONLY FULL backup and transaction log backups from the same time period.

Differential backups are not supported on secondary replicas.

BACKUP LOG supports regular log backups only. COPY_ONLY transaction log backups are not supported on secondary replicas. Additionally, the log backup chain is supported across all replicas regardless of where the log backup is taken (primary or secondary replicas) or the mode of the replication (asynchronous or synchronous).

It is important to understand what this means. For instance, a transaction log backup taken against a database participating in an AlwaysOn availability group will affect all replicated versions of that database.

Example:

I have an availability group which includes three instances and one database: [MyTestDatabase]

instance_jeremy 

The secondary instance, Instance3, is the preferred backup instance in this scenario.

When I take a transaction log backup against [MyTestDatabase] on Instance3, because they are all part of a single log chain, it will truncate the transaction log for [MyTestDatabase] on Instance3, Instance1 and Instance2.

 

How do I configure backup location preferences?

Configuration of backups and backup preferences can be performed easily through the GUI or scripted. Using the maintenance wizard to create your backup jobs will still give you the options and preferences (or the ability to turn off the preferences) required, but will not allow you to be as dynamic with your scenario driven backup operation utilizing the available logic.

Preferences add logic that can be used in your backup jobs in order to determine when and where your backups should take place. These preferences are not enforced and will not prevent someone from taking an ad hoc backup on any of the replicas or instances.

For instance, if your preference in your Availability Group Properties has been set to Secondary only than SQL Server should recognize the status of the replica in question, and if it is not the preferred replica, than the job would not run (or would complete successfully with no action taken).

How do you determine (or how does your job determine) if the current database is the preferred replica or not?

If your jobs are going to be scripted, you can use the function sys.fn_hadr_backup_is_preferred_replica in order to determine the status. Or, if you are using the Maintenance Plan Wizard to create your jobs, it will automatically call this function.

 

Example:

            If sys.fn_hadr_backup_is_preferred_replica ( @dbname ) <> 1

            BEGIN

            -- If this is not the preferred replica, script job to end without taking a backup

            END

            -- If this is the preferred replica, perform the backup

 

This function will provide an answer of [0] if it is run on a server which is not the preferred replica, and an answer of [1] if it is the preferred replica.

Notice in the screenshot below (showing Availability Group Properties), that in addition to Secondary only being checked, which indicates that I only want backups to take place on secondary replicas, the Backup Priority setting has also been configured so that between the two secondary replica instances (Instance2 and Instance3) Instance2 has a higher priority setting. This means that if both of the replicas are available at the time – the backup should only run on the Instance2 replica. Should the Instance2 replica become unavailable, the Instance3 replica would become the target instance performing backups against its replica.

 testgroup_jeremy

As mentioned previously, these backup jobs will need to be configured on all of the instances participating in the Availability Group if they ever have the intention of (depending on the preference setting) running backups. They do not need to be configured on an instance, even if it is participating in the Availability Group, if there is no intention of ever running backups on those replicas.

The option exists to exclude the replica/instance altogether. As the column header suggests – this would remove the specified instance replica from ever being a target for automated backups. Again – it should be noted that this does not prevent someone from running ad hoc backups on the replica.

An important point to keep in mind, the Availability Group backup preference settings apply to all backups that are configured to use preference settings against the specified group. You can’t segregate different preferences for your full backups from preferences for your transaction log backups in the same group. As noted earlier, you can ignore preferences (or not script them in) for that job if your desire is to have transaction log backups take place on a different server regardless of its replica status.

One last note for this topic, while the example above, and most comments, point to configuring the group properties/preferences using SQL Server Management Studio (in management studio - expand the AlwaysOn High availability node, and the Availability Groups node, right-click on the desired Availability Group and choose properties), as with most features these changes can also be scripted using T-SQL or even PowerShell.

 

At the T-SQL prompt (example):

ALTER AVAILABILITY GROUP [@MyGroup1] MODIFY REPLICA ON <@MyInstance1> WITH (BACKUP PRIORITY = 60)

ALTER AVAILABILITY GROUP [@MyGroup2] MODIFY REPLICA ON <@MyInstance1> WITH (BACKUP PRIORITY = 50)

 

At the PowerShell prompt (example):

Set-SqlAvailabilityReplica -BackupPriority 60

-Path SQLSERVER: \Data\AvailabiltiyGroups\AvailabiltiyReplicas\<@MyGroup1>

Set-SqlAvailabilityReplica -BackupPriority 50

-Path SQLSERVER: \Data\AvailabiltiyGroups\AvailabiltiyReplicas\<@MyGroup2>

 

Depending on your individual comfort level or preference, any of the three configuration tools are equally handy to get you where you need to go.

 

Where should I place my backups?

Planning a repository for your backups when using Availability Groups takes some serious consideration. If you are taking backups on separate replica instances - for example – taking your full backups on Instance2 and your transaction log backups on Instance3, pairing them together when you need to do a restore can be tricky. As (discussed earlier) the backup tables in the MSDB database only stores the information locally. Additionally because a COPY_ONLY FULL backup does not affect the log chain in a sequence of transaction log backups, SQL Server will not automatically recognize the pairing between the full and log backups in the case that you are restoring a replica database using Management Studio.

Consider configuring all your backup jobs and servers involved in the AlwaysOn Groups to use a shared repository so that they are all in one place. Using a well thought out naming convention, and a date/time stamp which will enable you to decipher the proper restore order, will assist in making a recovery much easier, less stressful and less time consuming.

 

Conclusion

Taking the time to properly strategize your backups in advance can greatly increase both its ease, usefulness and help to optimize many of the other features available with SQL Server AlwaysOn High Availability mirrors.