How many times you have seen heavy CPU utilization,heavy disk I/O happening when ever a backup job is running on a production SQL server.The CPU utilization hits top values if backup compression is enabled for the instance with the main intention of reducing backup sizes.

Would you love to offload these backup jobs from the main production server and avoid these high CPU/IO hits ?

This is exactly what SQL 2012 is capable of doing it for you.The databases part of availability groups can be backed up according to a set preference.

Some of the important points to mention here is –

1.The backup on the primary replica still works if you need to go with that option.

2.Only copy only full backup is allowed for secondaries.

3. Log backups can also be done for multiple secondaries and they all will form a single chain.

4. Differential backups are not supported for secondaries and you would require running differential for primary replica.

After setting up the Availability groups you have an option to set the backup preference.

Lets say we have a database Adventureworks2012 sitting on 2 replica’s WIN2008NodeA\AlwaysONSrv1(Primary) and WIN2008NodeB\AlwaysONSrv2(Secondary) and you would like to offload backups to happen only on secondary,then you can set that up.Ref to the below screens to get a clear understanding on the options which you have

If you are using T-SQL jobs to schedule your backups,then there is a new function which you would need to incorporate if you are planning to implement backups for secondary replicas.

The function is sys.fn_hadr_backup_is_preferred_replica and it will return a value of 1 if the replica is preferred for backups.

For syntax details you can ref http://msdn.microsoft.com/en-us/library/hh213235.aspx

A very simple code which you can incorporate in your TSQL code for backing up databases can be –

If sys.fn_hadr_backup_is_preferred_replica( @dbname ) <> 1 
BEGIN
-- If this is not the preferred replica, exit (without error).
END
-- If this is the preferred replica, continue to do the backup.

Incase you are using a maintenance plan schedule to backup the databases,then SQL 2012 backup task is smart enough to do this check.

Conclusion

Incase you are dealing with issues like backup running for a longer duration and causing good amount of performance issues within your mission critical servers,then you can think about moving to Availability Groups and make use of backups on secondary replica.The need for choosing this option should be evaluated very well.

Thanks for reading.