SQL Server AlwaysOn Create Agent job considerations

Tag: A color mil read-only route else procedure to determine the title run

Introduction

Always on Availability group activity accessibility includes support for performing backup operations on secondary replicas. Backup operations can put a lot of pressure on I/O and CPU (using Backup compression). After you transfer the backup load to a secondary replica that is synchronized or synchronizing, you can use the resources on the server instance that hosts the primary replica of the first tier of the workload, and you can create any type of backup of the primary database. You can also create log backups of secondary databases and copy only full backups.

First, the concept

1. Supported backup types on secondary replicas

• Backup database: Only copy-only full backups of databases, files, or filegroups are supported in secondary sub-replicas. Note that a copy-only backup does not affect the log chain, nor does it clear the differential bitmap.
• A secondary replica does not support differential backups.
• Backup log supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).
• To back up a secondary database, the secondary replica must be able to communicate with the primary replica, and the state must be SYNCHRONIZED or SYNCHRONIZING.

2. Configure where to run the backup job

It is beneficial to perform backups on secondary replicas to alleviate the backup workload of the primary production server. However, performing a backup of a secondary replica can significantly increase the complexity of the process used to determine where the backup job should run. To resolve this issue, configure the location where the backup job runs as follows:

• Configure the availability group to specify the availability replicas to perform backups on.
• Script-scripted backup jobs are created for each availability database on each server instance that hosts an availability replica that performs backup candidates.

3. Backup Preferences

Priority Secondary Replicas
Specifies that the backup should occur on the secondary replica, except when the primary replica is the only copy that is online. In that case, the backup should occur on the primary replica. This is the default option.

Secondary replicas only
Specifies that the backup should never be executed on the primary replica. If the primary replica is a unique online copy, the backup should not occur.

Primary replica
Specifies that the backup should always occur on the primary replica. This option is useful if you need backup features that are not supported when you run a backup on a secondary replica, such as creating a differential backup.

Any copy
Specifies that you want the backup job to ignore the role of the availability replica when you select a copy of the backup to perform. Note that the backup job may evaluate other factors, such as the backup priority of each availability replica and its operational status and connected status.

Note: If you plan to use log shipping to prepare any secondary databases for an availability group, set the automatic backup preference to primary until all the secondary databases are ready and joined to the availability group. There is no mandatory automatic backup preference setting. The interpretation of this preference depends on the logic (if any) that you compose a backup job script for a database in a given availability group. The automatic backup preference setting has no effect on ad hoc backups.

Second, the judgment statement

To consider using automatic backup preferences for a given availability group, for each server instance that hosts an availability replica with a backup priority greater than 0 (>0), you need to script the backup jobs for the databases in that availability group. To determine whether the current replica is the preferred backup copy, use the Sys.fn_hadr_backup_is_preferred_replica function in the backup script. Returns 1 otherwise returns 0 if the database on the current instance is on the preferred replica. By running a judgment script on each availability replica that queries This function, you can determine which copy should run the given backup job.

If sys.fn_hadr_backup_is_preferred_replica (@dbname)=1BEGIN  BACKUPDATABASE@DBNAMEtodisk=<disk>  ENDELSEPRINT(' current copy is not backup preferred copy ')

Create the same backup statement on all copies of the backup that may be performed, without modifying any scripts or jobs when failover occurs

Iii. Creating Agent Jobs

Attention:

1. The database to select "Master", if the current AlwaysOn is configured with read-only Routing and you choose an availability database, then the job created on the secondary replica fails because the read-only routing connection secondary replica availability database requires readonly connection properties. The same is true for other jobs that create non-backups.

2. The current my backup script is placed in the master database, and if you are executing scripts under other databases, it is necessary to bring the database name.

3. If the if is determined that the database specified here is a non-availability group, the result returned is also 1, because the database that is non-availability for the instance is also the first copy of the backup.

Determine if the command is the primary replica

If sys.fn_hadr_is_primary_replica (@dbname=1  BEGIN       Print('1')ENDELSE    PRINT( ' The current replica is not the primary replica ')

Note: Determine whether the primary replica command and determine whether the backup primary replica command is different, if the specified database is a non-availability group database to determine whether the primary replica returned the result is null, and whether the backup primary replica returned is 1.

Summary

1. The RESTORE statement is not allowed on the primary or secondary database of the availability group.

2. The backup preference is just a decision option for a backup, regardless of which copy you manually backup on, the only limitation is that a full backup of the secondary replica supports only "copy" backups. Because secondary replicas only support copy-only backups, secondary replicas cannot make differential backups. To make a differential backup the first backup copy should select the primary replica.

3. Either backing up the log on the primary replica or backing up the log on the secondary replica will eventually truncate the log chain on all replicas.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

SQL Server AlwaysOn Create Agent job considerations