Sql-server – Configure AlwaysOn backups

availability-groupssql server

I'm new to AlwaysOn Availability groups and have set it up in my DEV environment. I currently have it set to do the backups on my primary because we are only using the secondary for read-only reporting purposes. The backups are running but how do I control how often? Currently it does a FULL every night, a DIFF every night at midnight and log backups every hour. I don't see in the AVG setup where to specify how often…I currently do a FULL at 10pm, DIFF every 4 hours and LOG every 15 minutes. I also currently use Ola's backup scripts and I'm confused about how to continue to use his scripts when I setup AVG in my production. I've read that there is a parameter AVAILABILITY_GROUP_DATABASES for Ola's scripts. Do I use this to be able to control all the backups with Ola's scripts? And then the AVG will not back up the databases…instead they will be controlled by Ola's scripts?

I've tried testing this and no matter what I do, the AVG always does the backups and Ola's scripts are ignored.

Best Answer

I am running Availability Groups and using Ola's backup solution, and here is how I have it set up:

  1. First, Availability Groups do not do their own backups, something has to be scheduled to run a backup. So make sure there is not a Maintenance Plan already doing backups on the SQL Server
  2. Make sure Ola's scripts are installed and up to date on all of your Availability Group servers. :)
  3. Create a SQL Agent job for your FULL backups that calls Ola's dbo.DatabaseBackup procedure; schedule this to run however often you need.
  4. Repeat step 3 for your DIFF and LOG backups.
  5. Copy the backup jobs you just created to all of your secondary Availability Group servers.

Here's what you need to know about Ola's scripts:

  • They are what I like to refer to as "Availability Group Aware", meaning that you can run the same backup jobs on both your Primary and Secondary AG servers, and they will act accordingly. (ex: If you run a FULL backup on a Secondary, Ola's script will recognize it is on a secondary and exit gracefully without doing anything)
  • If you specify ALL_AVAILABILITY_GROUPS as your backup database, it will backup only the databases in ALL of the availability groups on that server. If you specify USER_DATABASES, it will back up ALL user databases on the server (including the ones in the AG).
Related Question