【SQLServer】理解SQL Server AlwaysOn AG的备份
2022-06-12 12:19 abce 阅读(1316) 评论(0) 编辑 收藏 举报AG提供了以下几种备份策略
下面来看看各项的解释
Prefer Secondary(首选辅助副本)
应在辅助副本上执行此可用性组的自动备份。如果没有可用的辅助副本，将在主副本上执行备份。
这个选项只是概念上的选项。基本上，用户可以从任何复制节点上执行备份命令。
我们可以在主副本上执行一个备份命令测试一下：
BACKUP DATABASE [AdventureWorks2016] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016.bak' WITH STATS = 10 GO
执行结果：
10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 2. 100 percent processed. Processed 25 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 2. BACKUP DATABASE successfully processed 24345 pages in 39.265 seconds (4.843 MB/sec).
是的，备份成功了！因为它适用于任何副本，无论是否是辅助副本！但是，这背后的原因是什么？正如我之前所说，这里的关键词是"automated"和"should"。
翻译一下，我们可以理解"should"的意思是"是的，这可以很好地运行在辅助副本上。如果不是辅助副本，也是没关系的"。
那么关键字"automated"呢？这个场景就有点不同了。SQLServer假定当你像我们一样运行BACKUP DATABASE命令时，你知道自己在做什么。简单的说，你设置了备份规则，因此如果你在主副本上进行备份，你将承担风险。另一方面，SQL Server维护计划会完成一个自动备份。让我们分析一下：
我创建了一个维护计划，备份任务包含了实例下所有数据库的备份。你可以看到，在窗口底部有个警告信息。
"This backup type is not supported on a secondary replica and this task will fail if the task runs on a secondary replica."
为什么会这样？因为我们的维护计划中包含了一些AG中的数据库。这里的问题很简单：SQL Server不支持在辅助节点上执行常规备份。在这种场景下，我们在主副本上执行，但是在发生failover的时候，备份任务就会失败！
如何解决这个问题呢？我们需要打开”options”界面，点击选中“Copy-only backup”功能
回到关键字”Automated”，如果我点击“View T-SQL”按钮，我们可以看生成的代码。在我这个实验中，会得到以下的代码：
DECLARE @preferredReplica INT
SET @preferredReplica = (SELECT
[master].sys.Fn_hadr_backup_is_preferred_replica('AdventureWorks2016'))
IF ( @preferredReplica = 1 )
BEGIN
BACKUP DATABASE [AdventureWorks2016] TO DISK =
N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_23_192837_3837451.bak'
WITH copy_only, noformat, noinit, NAME =
N'AdventureWorks2016_backup_2015_11_23_192837_3837451', skip, rewind,
nounload, stats = 10
END
代码以声明、值属性开始，变量名为“preferredReplica”
DECLARE @preferredReplica INT
SET @preferredReplica = (SELECT
[master].sys.Fn_hadr_backup_is_preferred_replica('AdventureWorks2016'))
通过执行代码中的select，结果如下：
这里和设置是匹配的，因为我们定义了AG备份的首选项是辅助副本节点，而我们是在主副本节点执行的。所以，如果你在辅助副本下执行备份，下面的查询会返回1，而不是0。
[master].sys.Fn_hadr_backup_is_preferred_replica('<DATABASE_NAME>')
继续分析代码，有个if条件判断
IF ( @preferredReplica = 1 )
BEGIN
<BACKUP CODE…>
END
这里就是为了证明“Automation”只是概念上的。通常AG对SQL Server维护计划是有意识的。它会解析备份任务的配置选项。
了解这些后，如果我们在primary节点运行不带if判断的backup database命令呢？也会正常工作。所以，别以为设置了备份首选项就没事了，还有很多事情要做呢！
Secondary Only(仅辅助副本)
第二个选项是仅辅助副本，表示必须在辅助副本上执行AG的所有备份。我们来测试一下：
首先，连接到主副本节点，我会尝试执行一个backup database命令：
BACKUP DATABASE [AdventureWorks2016] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016.bak' WITH STATS = 10 GO
输出结果
10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 3. 100 percent processed. Processed 27 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 3. BACKUP DATABASE successfully processed 24347 pages in 12.018 seconds (15.827 MB/sec).
天哪！居然又成功了。为什么呢？明明是设置成了仅在辅助副本执行，怎么在主副本又成功了？
同样，这只是概念上的。如果你不懂原理，备份首选项设置就对你没啥意义。
我们再生成一下代码看看
DECLARE @preferredReplica INT
SET @preferredReplica = (SELECT
[master].sys.Fn_hadr_backup_is_preferred_replica('AdventureWorks2016'))
IF ( @preferredReplica = 1 )
BEGIN
BACKUP DATABASE [AdventureWorks2016] TO DISK =
N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_23_200104_2781259.bak'
WITH copy_only, noformat, noinit, NAME =
N'AdventureWorks2016_backup_2015_11_23_200104_2781259', skip, rewind,
nounload, stats = 10
END
可以看到，这个和“首选辅助副本”生成的代码一样。
看完这些，那“首选辅助副本”和“仅辅助副本”有什么区别呢？
区别就是可用性！
基本上，对于“首选辅助副本”，如果所有的辅助副本都offline了，备份就会在primary节点上；而“仅辅助副本”选项，在所有辅助副本都offline了，备份也不会在primary节点上，即备份会失败。
让我们来测试一下。
在我们的实验中，AG中有两个节点，备份属性设置成“仅辅助副本”。使用上面生成脚本，我会尝试在primary执行备份，看看会发生什么：
我们可以执行成功，但是备份没有真的做！
现在将辅助副本offfline，测试备份：
以下是测试结果：
结果和上面一样。总结一下：即使辅助副本offline了，备份也不会执行。这样的设计的用以是什么？假设你有个性能非常敏感的数据库，为了避免额外的负载，你不能在primary上执行备份。这种设计就符合你的要求。
记住，我们使用的是SQL Server维护计划智能生成的代码，带有if条件判断语句来执行backup database命令。如果我们想直接执行呢？和上面的场景类似(只有primary是online的)：
Backup database命令会成功。这也证明了备份选项只是概念上的选择。
你需要知道如果你使用“首选辅助副本”做测试究竟会发生什么。让我们来验证一下。
第一个测试：
·备份选项是“首选辅助副本”
·primary和secondary节点都是online状态的
·使用SQLServer维护计划智能生成的代码
结果：
继续，相同结果。代码运行成功了，但是backup database命令被忽略了。
第二个测试：
·备份选项是“首选辅助副本”
·只有primary是online状态的
·使用SQL Server维护计划生成的智能代码
结果：
这次，命令运行成功了，备份也执行了。
基于上面的测试，我们可以得出如下的结论：
仅辅助副本：
·备份命令会在辅助副本上执行，没有异常
·即使primary是唯一online的，备份会被忽略
首选辅助副本：
·备份命令会在辅助副本上执行，除非所有的辅助副本都offline了。这时，就会在primary上成功执行
在“仅辅助副本”和“首选辅助副本”，如果你直接执行backup database命令，备份会正常执行。
Primary(主副本)
顾名思义,必须在当前主副本上执行此可用性组的所有自动备份。我会在primary上运行backup database命令。
BACKUP DATABASE [AdventureWorks2016] TO DISK =
N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_23_200104_2781259.bak'
WITH copy_only, noformat, noinit, NAME =
N'AdventureWorks2016_backup_2015_11_23_200104_2781259', skip, rewind,
nounload, stats = 10
注意，这里我没有使用if条件判断，备份运行成功了：
10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 24320 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Data’ on file 3. 100 percent processed. Processed 29 pages for database ‘AdventureWorks2016’, file ‘AdventureWorks2014_Log’ on file 3. BACKUP DATABASE successfully processed 24349 pages in 11.484 seconds (16.564 MB/sec).
在辅助副本上，执行相同的命令，也没有遇到问题：
现在，我们使用SQLServer维护计划生成的智能备份代码，首先在primary上执行：
DECLARE @preferredReplica INT
SET @preferredReplica = (SELECT
[master].sys.Fn_hadr_backup_is_preferred_replica('AdventureWorks2016'))
IF ( @preferredReplica = 1 )
BEGIN
BACKUP DATABASE [AdventureWorks2016] TO DISK =
N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_23_200104_2781259.bak'
WITH copy_only, noformat, noinit, NAME =
N'AdventureWorks2016_backup_2015_11_23_200104_2781259', skip, rewind,
nounload, stats = 10
END
结果如预想的一样，成功了。
相同的代码在辅助复制节点上执行：
命令运行成功了，但是备份没有真的执行。
我们测试了“主副本”选项，只能将备份运行在primary上。和上面两种选项类似，需要使用if条件判断来执行，通过调用函数sys.Fn_hadr_backup_is_preferred_replica()。
“主副本”选项里，primary不存在offline的场景。
Any Replica(任意副本)
选择这个选项的话，会更加复杂一点。但光是看这个选项的名字，看起来会很简单。毕竟，任意副本，表示所有的节点。如果我们停止思考，一切都变得无意义。
这里比较诡异的一点就是如何实现备份策略。基本上，这个选项非常灵活，主要是因为下面的grid：
这里，支持我们灵活地定义备份策略。在我这个实验中，只有两个节点，如果节点更多，优先级设置会很方便。这就是任意副本如何工作的，都是基于该grid中的设置。
这里我们将两个节点设置成相同的权重，会发生什么呢？来测试一下！
首先，生成SQLServer维护计划的备份命令。
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('AdventureWorks2016'))
IF (@preferredReplica = 1)
BEGIN
BACKUP DATABASE [AdventureWorks2016] TO DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016_backup_2015_11_24_022752_8151223.bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'AdventureWorks2016_backup_2015_11_24_022752_8151223', SKIP, REWIND, NOUNLOAD, STATS = 10
END
代码都差不多，调用函数sys.fn_hadr_backup_is_preferred_replica()。
下面分别在主和辅助节点上执行：
Server: W2016SRV06 Role: Primary Weight: 50 Result: Preferred backup replica Server: W2016SRV07 Role: Secondary Weight: 50 Result: NON Preferred backup replica
我比较好奇的是，如果优先级权重一样，选择W2016SRV06的标准是什么？所以，我再次做了测试。测试结果是相同的。可能是权重相同，所以选择了主节点—其实并非如此！
我做了一次故障转移
Server: W2016SRV06 Role: Secondary Weight: 50 Result: Preferred backup replica Server: W2016SRV07 Role: Primary Weight: 50 Result: NON Preferred backup replica
结果很奇怪，但是没有找到原因，需要进一步研究。通过研究了函数代码，我发现，select语句负责帮助找到首选副本节点，细节挺有意思的。
以下是代码：
SELECT Cast(ar.replica_id AS NVARCHAR(36)) AS replica_id,
Cast(@ag_resource_id AS NVARCHAR(36)) AS resource_id,
CASE ar.replica_server_name
WHEN @primary_server_name THEN 1 -- primary
ELSE 2 -- secondary
END AS role,
CASE ar.replica_server_name
WHEN @local_server_name THEN 1
ELSE 0
END AS is_local
FROM sys.availability_replicas ar
WHERE ar.group_id = @ag_id
AND ar.backup_priority > 0
AND
-- 0 is a flag that backups are never desired on this replica.
CONVERT (CHAR(1), (SELECT CASE ar2.replica_server_name
WHEN @primary_server_name THEN 1
-- primary
ELSE 2 -- secondary
END
FROM sys.availability_replicas ar2
WHERE ar2.replica_id = ar.replica_id)) LIKE
CASE
@ag_pref
WHEN 0 THEN '1' -- Primary preferred
WHEN 1 THEN '2' -- Secondary Only
WHEN 2 THEN '2' -- Secondary Preferred
WHEN 3 THEN '%' -- No Preference
END
ORDER BY ar.backup_priority DESC,
ar.replica_server_name ASC;
你可以看到，有一个order by语句，根据备份优先级和server名称排序。所以，定义的server名称影响了首选节点的选择。
备份优先级设置总结：
·首先根据优先级的高低进行排序，优先级高的作为首选节点
·相同优先级的，根据server名称进行排序；名称是根据字母顺序进行排序的
聪明的你，可以客户化开发来帮助选择备份节点。例如：
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
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;
https://www.sqlshack.com/understanding-backups-on-alwayson-availability-groups-part-1
https://www.sqlshack.com/understanding-backups-on-alwayson-availability-groups-part-2
