Home > Backup > Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)

Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)

Its been a old story now , when we use to take database backup on the Primary Database as we don’t have another option to minimize the I/O and CPU of the system.

[FROM BOOKS ON LINE]

The AlwaysOn Availability Groups active secondary capabilities from SQL Server 2012 has feature to perform  backup operations on secondary replicas. Backup operations can put significant strain on I/O and CPU (with backup compression). Offloading backups to a synchronized or synchronizing secondary replica allows you to use the resources on server instance that hosts the primary replica for your tier-1 workloads.

Supported Backup Types on Secondary Replica

  1. BACKUP DATABASE supports only copy-only full backups of databases, files, or file groups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

  2. Differential backups are not supported on secondary replicas.

  3. BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

    A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

  4. To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

 

Performing backups on secondary replicas introduce significant complexity to the process of determining where backup jobs should run. To address this, configure where backup jobs run as follows:

  1. Configure the availability group to specify which availability replicas where you would prefer backups to be performed. For more information, see AUTOMATED_BACKUP_PREFERENCE and BACKUP_PRIORITY parameters in CREATE AVAILABILITY GROUP (Transact-SQL) or ALTER AVAILABILITY GROUP (Transact-SQL).

  2. Create scripted backup jobs for every availability database on every server instance that hosts an availability replica that is a candidate for performing backups. For more information, see the "Follow Up: After Configuring Backup on Secondary Replicas" section of Configure Backup on Availability Replicas (SQL Server).

image

 

To determine whether the current replica is the preferred backup replica

sys.fn_hadr_backup_is_preferred_replica ( 'dbname' )
Categories: Backup
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: