Alwayson -- backup preference

A new feature of alwayson group is to allow backup in secondary replica and remove the backup load from primary replica.

The backup preference option is provided for managing backups in the alwayson availability group environment. It should be emphasized that backup preferences does not work forcibly. After setting, backup can still be performed on any replica.

The following example illustrates how to use backup preference.

Environment

===

Secondary node: sql1_w2k8r21.

Secondary node: sql1_w2k8r22.

Primary node: sql1_w2k8r23.

Availability group: aggrouptest

Replica Database: dbtest1

Open avwayson High Availability-> availability groups-> right-click the required availability replicas-> properties.

The following window is displayed. Click Backup preference on the left.

Backup preference options are as follows:

Prefer secondary

Secondary replica with a higher priority will be set to prefer replica. If all secondary replica have the same priority, the system will randomly select one as prefer replica.

If the secondary replica with a higher priority is unavailable, the second highest priority will be prefer replica, and so on.

If primary is the only available replica, primary becomes prefer replica.

Secondary only

Primary replica will never be prefer replica, and the rest will be the same as prefer secondary

Primary

Only primary replica becomes prefer replica.

Any replica

Replica with a higher priority

 

If exclude replica is selected, the replica will not become prefer replica.

 

So how can we apply backup preference to our backup plan? The following is an example.

 

Create a full backup job in all replica jobs, which runs once a day. The script is as follows:

Declare @ n int

Select @ n = SYS. fn_hadr_backup_is_preferred_replica ('dbtest1 ')

If @ n = 1

Begin

Declare @ timestamp sysname

Set @ timestamp = Replace (replace (convert (char (16), getdate (), 120 ),':','_'),'','_') + '. bak'

Declare @ dbname sysname

Set @ dbname = 'dbtest'

Declare @ path sysname

Set @ Path = '\ Folder \' -- for example C: \ backup \

Declare @ backup nvarchar (200)

Set @ backup = 'backup database' + quotename (@ dbname, ']') + 'to disk = '+ ''' + @ path + @ dbname + @ timestamp + ''' with copy_only'

Exec (@ backup)

 

 

End

 

Create a log backup job in all replica jobs every 15 minutes. The script is as follows:

Declare @ n int

Select @ n = SYS. fn_hadr_backup_is_preferred_replica ('dbtest1 ')

If @ n = 1

Begin

 

Declare @ timestamp sysname

Set @ timestamp = Replace (replace (convert (char (16), getdate (), 120 ),':','_'),'','_') + '. bak'

Declare @ dbname sysname

Set @ dbname = 'dbtest'

Declare @ path sysname

Set @ Path = '\ Folder \' -- for example C: \ backup \

Declare @ backup nvarchar (200)

Set @ backup = 'backup log' + quotename (@ dbname, ']') + 'to disk = '+ ''' + @ path + @ dbname +' _ log' + + @ timestamp + ''''

Exec (@ backup)

 

 

End

 

This backup job uses the SYS. fn_hadr_backup_is_preferred_replica function. This function determines whether the current node is prefer replica based on the backup preference setting. If yes, the backup is performed. Otherwise, the job exits.

This function returns 0 and 1, 0 indicates no, and 1 indicates yes. This ensures that only one replica can be used for backup at the same time.

 

If you use the maintenance plan to create a plan, backup preference is automatically used by default.

 

Finally, there are two bug fixes for backup preference.

Fix: 100 percent of CPU resources are used after you enable the log shipping feature for some databases in SQL Server 2012 http://support.microsoft.com/kb/2887115

 

Fix: the system function SYS. fn_hadr_backup_is_preferred_replica does not work correctly after you have cu7 for SQL Server 2012 SP1 installed http://support.microsoft.com/kb/2918791