Notes for SQL Server Alwayson agent job creation, sqlalwayson

Notes for SQL Server Alwayson agent job creation, sqlalwayson

Preface

Always On availability group activity auxiliary features include support for backup operations On secondary copies. Backup operations may put a lot of pressure on I/O and CPU (using backup compression ). After transferring the backup load to a synchronized or synchronizing secondary copy, you can use the resources on the server instance that carries the primary copy of the first layer of work load, you can create any type of backup for the primary database. You can also create a log backup for the secondary database and copy only the complete backup. I won't talk much about it below. Let's take a look at the detailed introduction.

I. Concepts

1. Backup Types supported by secondary replicas

• Backup database: the secondary DATABASE only supports full BACKUP of databases, files, or file groups. Note that only copying backups does not affect the log chain or clear the differential bitmap.
• Secondary copies do not support differential backup.
• Backup log only supports regular log backup (log backup on secondary copies does not support the COPY_ONLY option ).
• To back up the secondary database, the secondary database must be in SYNCHRONIZED or SYNCHRONIZING state.

2. Configure the location for running the backup job

It is advantageous to back up secondary copies to reduce the backup workload of the primary production server. However, backing up secondary replicas significantly increases the complexity of the process for determining where backup jobs should be run. To solve this problem, configure the running location of the backup job as follows:

• Configure the availability group to specify the availability copy for which the backup is to be performed.
• A script backup job is created for each availability database on each server instance that carries an availability copy as a backup candidate.

3. Backup preferences

Secondary replica priority

The specified backup should occur on the secondary copy, except when the primary copy is a unique online copy. In this case, the backup should occur on the primary copy. This is the default option.

Secondary replica only

The specified backup should never be performed on the primary copy. If the primary copy is a unique online copy, the backup should not occur.

Primary copy

The specified backup should always occur on the primary copy. This option is useful if you need a backup feature that is not supported when you run the backup for the secondary copy, for example, creating a differential backup.

Any copy

Specifies that the role of the available copy is ignored when you select the copy to be backed up. Note that the backup job may evaluate other factors, such as the backup priority, operation status, and connection status of each available copy.

Note:If you plan to use Log shipper to prepare any secondary database for the availability group, set the automatic backup preference to Primary until all secondary databases are ready and added to the availability group. There is no mandatory automatic backup preference settings. The explanation of this preference depends on the logic (if any) in which you write the backup job script for the database in the given availability group ). The automatic backup preference setting does not affect impromptu backup.

Ii. Judgment statement

If you want to consider using the automatic backup preference for a given availability group, for each server instance that carries an availability copy with a backup priority greater than zero (> 0, you need to write a script for the backup job of the database in the availability group. To determine whether the current copy is the preferred backup copy, usesys.fn_hadr_backup_is_preferred_replica Function. If the database on the current instance is on the preferred copy, 1 is returned; otherwise, 0 is returned. By running a judgment script on each available copy of the query function, you can determine which copy should run the specified backup job.

If sys. fn_hadr_backup_is_preferred_replica (@ dbname) = 1 beginbackup database @ dbname to disk = <disk> end else print ('current copy is not the backup preferred replicase ')

Create the same backup statement on all possible backup copies. In case of failover, no script or job needs to be modified.

3. Create a proxy job

 

Note:

1. select "master" for the database here. If the current alwayson is configured with a read-only route and you select an availability database, the job created on the secondary copy will fail to be executed, the readonly connection attribute is required for connecting read-only routes to secondary replica availability databases. The same applies to other jobs that are not backed up.

2. Currently, my backup script is stored in the master database. If the script is executed in other databases, the database name must be included here.

3. if the if clause determines that the result returned by a database in a non-availability group is also 1, for instances, the non-availability database is also the first copy of the backup.

Determine whether it is a master copy command

If sys. fn_hadr_is_primary_replica (@ dbname) = 1 begin print ('1') end else print ('current copy is not the primary replicase ')

Note:The command for determining whether the master copy is the same as the command for determining whether the master copy is backed up is not the same. If the specified database is a non-availability Group database, it determines whether the result returned by the master copy is NULL, determine whether the primary copy of the backup is returned as 1.

Summary

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

2. the backup preference is only an option for determining the backup. No matter which copy you manually back up, the only restriction is that the full backup of the secondary copy only supports "copy" backup. Secondary copies only support backup, so secondary copies cannot be backed up differently. To perform differential backup, select the primary copy as the backup copy.

3. Log backups on the primary copy or secondary copy finally truncate the log chains on all copies.

Well, the above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.