[SQL SERVER]SQL2016-Alwayson AG了解備份喜好設定

備份作業可能會是一項耗用I/O和CPU(啟用備份壓縮)的繁重作業,這項作業可能會影響主要伺服器效能,

現在透過AlwaysOn AG將備份作業卸載至同步或同步中的次要複本,讓主要伺服器專用於生產應用程式。

設定AG的備份喜好選項設定讓我有一點困惑,測試完後才知道我對文字描述理解真的不是那麼高..

了解可用性群組的備份喜好設定

慣用次要(Perfer Secondary):

--the Primary replica:
backup database ricolab1 to disk='\\win2k12r2a\sqlbackup$\ricolab1.bak'
with compression,init,stats=10
主要複本執行成功

--the secondary replica:
backup database ricolab1 to disk='\\win2k12r2a\sqlbackup$\ricolab1.bak'
with compression,init,stats=10

可以看到在次要複本執行備份會出現無支援的錯誤。

 

必須加上copy_only備份命令才能在複本正常執行

Note:backup log 無須加上copy_only,支援一般命令。

declare @preferredReplica int 
select @preferredReplica = ( select
[master].sys.Fn_hadr_backup_is_preferred_replica('ricolab1') )
--print @preferredReplica
if @preferredReplica=1
begin
backup database ricolab1 to disk='\\win2k12r2a\sqlbackup$\ricolab1.bak'
with compression,copy_only,nounload,stats=10
end

設定所有次要複本離線進行測試

所有次要複本離線後,在主要複本執行備份依然可以成功,

該選項是說如果所有次要複本不可用,那麼備份應該要在主要複本執行,否則將在次要複本執行。

 

僅次要(Secondary Only):

設定所有次要複本離線進行測試

declare @preferredReplica int 
select @preferredReplica = ( select
[master].sys.Fn_hadr_backup_is_preferred_replica('ricolab1') )
print @preferredReplica
if @preferredReplica=1  --判斷目前的複本是否為慣用的備份複本,1=執行備份
begin
backup database ricolab1 to disk='\\win2k12r2a\sqlbackup$\ricolab1.bak'
with compression,copy_only,nounload,stats=10
end

該選項是說如果所有次要複本不可用,備份將永遠不會在主要複本執行。

 

Primary:

指定備份一定要在主要複本上進行。由於次要複本不支援差異備份,

這時該選項就很實用(不建議AG中使用差異備份)。

 

次要副本無法執行任何備份命令

 

任何複本(Any Replica):

指定當您選擇要執行備份的複本時,您希望備份作業忽略可用性複本的角色。

依照優先權設定,執行備份命令順序為WIN2KR2D、WIN2KR2B、WIN2KR2A,

優先選擇WIN2KR2D複本執行,其他複本依序排隊,除非前一個複本不可使用,後面複本才能遞補。

如果遇到優先權相同,將依照複本伺服器名稱字母排序優先權,你可以透過下面TSQL了解執行備份命令順序。

select CASE ags.primary_replica 
         WHEN ar.replica_server_name THEN 'PRIMARY' 
         ELSE 'SECONDARY' 
       END replica_type, 
       ar.replica_server_name, 
       ar.backup_priority, 
       CASE 
         WHEN backup_priority > 0 THEN 'INCLUDED' 
         ELSE 'EXCLUDED' 
       END used_for_backup 
from   sys.availability_replicas ar 
       join sys.dm_hadr_availability_group_states ags 
               ON ags.group_id = ar.group_id 
order by ar.backup_priority desc, ar.replica_server_name; 

 

參考

Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

Configure Backup on Availability Replicas (SQL Server)

sys.fn_hadr_backup_is_preferred_replica (TRANSACT-SQL)

How to Configure AlwaysOn AG Backups with Ola Hallengren’s Scripts

Copy-Only Backups (SQL Server)

SQL Server AlwaysOn Availability Groups Cheat Sheet

Understanding backups on AlwaysOn Availability Groups – Part 1

Understanding backups on AlwaysOn Availability Groups – Part 2

sys.availability_replicas (Transact-SQL)

sys.dm_hadr_availability_group_states (Transact-SQL)