SQL Server – Configure Backup on Availability Replicas

For a recent project, I needed to create a backup maintenance task for databases in a SQL Server Always On Availability group. In three separate AO environments.

Now there are several options available:

  • Use custom backup jobs
  • Use Ola Hallengren’s backup scripts
  • Use SQL Maintenance tasks

After investigating each of these options, I decided to use the SQL Maintenance tasks. Primary reason, the client won’t have to update Ola’s scripts as revisions take place, and I wanted to keep the solution simple and easily repeatable.

Using the SQL Maintenance Wizard, for each node in the SQL Availability Group, I created three (3) separate tasks.

The keys to making this work are:

  • Using a UNC location to backup the user databases and transaction logs files for each environment
  • Scheduling each SQL Server Agent Job to run at the same time
    • In the Always On environment, only one of the jobs will actually run

Here are the build notes:

SQL Server Backup

Important maintenance plan creation notes:

· Use default Availability Group backup priority options

· Use compression

· Create a folder for each database

· User Database(s) Transaction log file back up to *environment share*

· Create a folder for each database

1. On each node configure the following three maintenance plan backup tasks.

a. BackupSystemDBs

· System DB backups created locally (on each server)

· Schedule: Run daily @ 5:00PM

○ Retention 1 week

image

SQL Maintenance Task

b. BackupDB

· User Database(s) back up to *environment share*

· Use Copy-only database Option

· Note: For more information on copy-only database option

· Schedule: Run daily @ 6:00PM

○ Retention 1 week

image

c. BackupDBTrans

· User Database(s) Transaction log file back up to *environment share*

· Schedule: Run Hourly from 7:00PM to 5:30PM

○ Retention 48 hours

image

Reference: Configure Backup on Availability Replicas (SQL Server)

From <https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-2017>

Advertisements
This entry was posted in High Availability, SCCM, SQL Server. Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.