3节点AlwaysOn数据库备份策略

思路：

1. 优先在辅助副本上执行备份。
2. 多个辅助副本在线时，在备份优先级高的辅助副本上执行。
3. 辅助副本优先级相同时，在主机名较小的辅助副本上执行。
4. 只有所有辅助副本都故障时才在主副本执行备份。

正常的数据库备份(完整备份、差异备份)只能在主副本进行

辅助副本只支持仅复制备份的完整备份，不支持差异备份，仅复制备份不影响日志链，也不清除差异位图

--查询主机名
select @@SERVERNAME

--查询主副本主机名
select primary_replica from sys.dm_hadr_availability_group_states

--查询副本信息
SELECT 
CASE ags.primary_replica WHEN ar.replica_server_name THEN '主副本' ELSE '辅助副本' END 副本类型,
CASE WHEN backup_priority > 0 THEN '可备份' ELSE '不可用' END 是否可备份,
ar.replica_server_name 副本主机名,ar.backup_priority 备份优先级
FROM sys.availability_replicas ar INNER 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 ASC;

以hqms数据库为例创建备份任务存储过程

use hqms

CREATE PROCEDURE [dbo].[BACK]
AS

-- =============================================
--1. 优先在辅助副本上执行备份。
--2. 多个辅助副本在线时，在备份优先级高的辅助副本上执行。
--3. 辅助副本优先级相同时，在主机名较小的辅助副本上执行。
--4. 只有所有辅助副本都故障时才在主副本执行备份。
--SELECT 
--CASE ags.primary_replica WHEN ar.replica_server_name THEN '主副本' ELSE '辅助副本' END 副本类型,
--CASE WHEN backup_priority > 0 THEN '可备份' ELSE '不可用' END 是否可备份,
--ar.replica_server_name 副本主机名,ar.backup_priority 备份优先级
--FROM sys.availability_replicas ar INNER JOIN sys.dm_hadr_availability_group_states ags
--ON ags.group_id = ar.group_id
--inner join sys.dm_hadr_cluster_members cm on ar.replica_server_name=cm.member_name and cm.member_state=1
--ORDER BY CASE ags.primary_replica WHEN ar.replica_server_name THEN '1' ELSE '0' END,
--ar.backup_priority ASC
-- =============================================


BEGIN
--需要备份的数据库名
declare @dbname nvarchar(250)='hqms'

--备份路径
declare @path nvarchar(250)='D:\backup\'

--查询主机名
DECLARE @当前登录主机 VARCHAR(100) = (select @@SERVERNAME)

--查询执行备份服务的主机名：选取辅助副本中备份优先级较高的节点
DECLARE @执行备份服务的主机 VARCHAR(100) = (
  SELECT top 1 ar.replica_server_name 副本主机名
  FROM sys.availability_replicas ar 
  INNER JOIN sys.dm_hadr_availability_group_states ags ON ags.group_id = ar.group_id
  inner join sys.dm_hadr_cluster_members cm on ar.replica_server_name = cm.member_name and cm.member_state=1
  where ar.backup_priority > 0
  ORDER BY CASE ags.primary_replica WHEN ar.replica_server_name THEN '1' ELSE '0' END ASC,ar.backup_priority ,ar.replica_server_name)

IF ( @当前登录主机 = @执行备份服务的主机 )
BEGIN
declare @filename nvarchar(250)
set @filename=replace(replace(replace(REPLACE(convert(varchar(50),getdate(),121),'-','_'),' ','_'),':',''),'.','_')
set @filename=@path + @执行备份服务的主机 + '_' + @dbname + '_Backup_' + @filename + '.bak'

BACKUP DATABASE @dbname TO DISK =@filename WITH COMPRESSION,copy_only,noformat,noinit,skip,rewind,nounload, stats = 10
END

END

在所有节点启用SQL代理服务，创建定时备份作业

执行情况（优先级都是50的情况下）：

• SQLDB51为主副本时，备份任务在SQLDB52上执行，关闭SQLDB52副本，备份在SQLDB53上执行。
• SQLDB52为主副本时，备份任务在SQLDB51上执行，关闭SQLDB51副本，备份在SQLDB53上执行。
• SQLDB53为主副本时，备份任务在SQLDB51上执行，关闭SQLDB51副本，备份在SQLDB52上执行。