alwayson高可用组_了解AlwaysOn可用性组上的备份-第2部分

alwayson高可用组

This article is a continuation of a guide where we are checking all the available backup preferences for a database contained in an Availability Group.

本文是指南的续篇，在该指南中，我们正在检查可用性组中包含的数据库的所有可用备份首选项。

Following the analysis, we are studying the behaviour of the option “Secondary Only”. After testing a manual backup, while this option is set, the BACKUP DATABASE command ran with success, and the backup was made even being performed from the Primary replica, which is totally against the description of the option, which states that the backup MUST be made in the Secondary replica ONLY!

经过分析，我们正在研究“仅中学”选项的行为。 在测试了手动备份之后，设置了此选项后，BACKUP DATABASE命令成功运行，甚至从主副本执行了备份，这完全与该选项的说明相反，该说明指出必须仅在辅助副本中制成！

Let’s try to understand the reason behind this…

让我们尝试了解其背后的原因…

Checking the maintenance plan, created before (see part 1), I generated the backup code from the Backup Database task. This is the result:

检查之前创建的维护计划（请参阅第1部分 ），我从备份数据库任务中生成了备份代码。 结果如下：

 
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 
 

As you can see, this is exactly the same of the option “Prefer Secondary”. Knowing this, what is the real difference between “Prefer Secondary” and the “Secondary Only” option? The difference is in the availability.

如您所见，这与“首选二级”选项完全相同。 知道这一点，“首选中学”和“仅中学”选项之间的真正区别是什么？ 区别在于可用性。

Basically, on the “Prefer Secondary” option, if all the secondary replicas become offline, the backup will in the primary. In other hands, on the “Secondary Only” option, on the same situation, the backup would never run in the Primary replica. This would simply fail. So let’s test it.

基本上，在“首选辅助副本”选项上，如果所有辅助副本都脱机，则备份将位于主副本中。 换句话说，在“仅次要”选项上，在相同情况下，备份将永远不会在主副本中运行。 这只会失败。 因此，让我们对其进行测试。

In my lab, I have an Availability Group with two replicas, configured with the backup option “Secondary Only”. Using the generated code (above) I’ll try to execute the backup from the Primary replica, let’s see what is going to happen:

在我的实验室中，我有一个具有两个副本的可用性组，并配置了备份选项“仅次要”。 使用上面生成的代码，我将尝试从主副本执行备份，让我们看看会发生什么：

Well, the execution completed successfully… But the backup was not made (which makes sense…)!

好了，执行成功完成了……但是没有进行备份（这很有意义……）！

Now I’m going to take the secondary instance offline and test the backup:

现在，我要使辅助实例脱机并测试备份：

This was the result:

结果是：

The result was exactly the same! In summary: Even with the primary offline, the backup won’t run. What’s the utility for this? Pretend that you have a very performance sensible database, and you just can’t run the backups from the primary replica, in order to avoid an extra load. This is the way to go.

结果是完全一样的！ 总结：即使主脱机，备份也不会运行。 这有什么用？ 假设您有一个非常适合性能的数据库，并且只是为了避免额外的负载而无法从主副本运行备份。 这是要走的路。

Keep in mind that we are using the “smartly generated” code from the SQL Server Maintenance Plan. With the IF clause controlling the access to the BACKUP DATABASE command. What if we try to execute the backup directly, in the same conditions as before (only the primary is online):

请记住，我们正在使用SQL Server维护计划中的“智能生成”代码。 通过IF子句控制对BACKUP DATABASE命令的访问。 如果我们尝试在与以前相同的条件下直接执行备份（仅主数据库处于联机状态），该怎么办：

The BACKUP DATABASE command will succeed! One more prove that the “Backup Options” are nothing more than conceptual choices.

BACKUP DATABASE命令将成功！ 另一事实证明“备份选项”仅是概念上的选择。

You should be wondering what happens if you do the same test using the option “Prefer Secondary”. Let’s check it out!

您应该想知道如果使用选项“首选二级”进行相同的测试会发生什么。 让我们来看看！

First test:

第一次测试：

• Backup option “Prefer Secondary”
  备份选项“首选二级”
• Both Primary and Secondary replicas are online
  主副本和辅助副本都处于联机状态
• Using the “smart” code, generated by SQL Server Mantenance Plan.
  使用由SQL Server维护计划生成的“智能”代码。

Result:

结果：

Here you go… Same thing again! The code run successfully, but the BACKUP DATABASE command was ignored!

在这里，你又来了…… 代码成功运行，但是BACKUP DATABASE命令被忽略！

Second test:

第二次测试：

• Backup option “Prefer Secondary”
  备份选项“首选二级”
• Only the Primary replica is online
  只有主副本在线
• Using the “smart” code, generated by SQL Server Maintenance Plan.
  使用SQL Server维护计划生成的“智能”代码。

Result:

结果：

Now we are talking!! The command run with success and the backup was executed.

现在我们在说话！ 该命令成功运行，并且备份已执行。

Based on the tests that we’ve just made, we can conclude the following:

根据我们刚刚进行的测试，我们可以得出以下结论：

Secondary Only (using the condition):

仅次要（使用条件）：

• The backup command will run always in the secondary, with no exceptions!
  backup命令将始终在辅助目录中运行，没有例外！
• Even if the Primary is the only online replica, the backup will be ignored.
  即使主数据库是唯一的联机副本，备份也将被忽略。

Prefer Secondary (using the condition):

首选中学（使用条件）：

• The backup command will run always in the secondary, unless all the secondary replicas are offline. I this case, it will success in the Primary.
  除非所有辅助副本都脱机，否则backup命令将始终在辅助副本中运行。 在这种情况下，它将在Primary中成功。

In both cases, if you issue the BACKUP DATABASE command directly, the backup will be started normally.

在这两种情况下，如果直接发出BACKUP DATABASE命令，则备份将正常启动。

Now that both of the “Secondary backup” options were demystified, it is time to check the other two remaining options…

现在，“次要备份”两个选项均已被揭开神秘面纱，现在该检查其余两个选项了……

主 ( Primary )

As it says, all the backups will be forced to run on the Primary replica. So let’s test it….

就像说的那样，所有备份将被强制在主副本上运行。 因此，让我们对其进行测试...。

From the primary, I’m going to run the following BACKUP DATABASE command:

在主数据库中，我将运行以下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 
 

Notice that I’m not using the IF condition. The backup ran with success:

请注意，我没有使用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).

已处理10％。
已处理20％。
30％已处理。
40％已处理。
50％已处理。
60％已处理。
70％已处理。
80％已处理。
90％已处理。
已处理数据库'AdventureWorks2016'的24320页，文件3中的文件'AdventureWorks2014_Data'。
100％已处理。
已处理29页的数据库'AdventureWorks2016'，文件'AdventureWorks2014_Log'的文件3。
BACKUP DATABASE在11.484秒（16.564 MB /秒）中成功处理了24349页。

By running the same command, but from the secondary, it also runs without issues:

通过运行相同的命令，但是从第二个命令运行，它也不会出现问题：

Now, let’s use the code generated by te SQL Server Maintenance Plan’s Backup task, first on the primary:

现在，让我们首先使用主要由SQL Server维护计划的备份任务生成的代码：

 
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 
 

The output confirms the expected success.

输出确认了预期的成功。

Now, the same code on the secondary:

现在，在辅助服务器上使用相同的代码：

The command ran with success, but the backup didn’t run, as expected.

该命令成功运行，但是备份没有按预期运行。

We already tested the behaviour of the Primary option, where we can set the backup to run only on the primary replica. As the other two options, this will be controlled, only if you use the IF clause, by calling the sys.Fn_hadr_backup_is_preferred_replica() function.

我们已经测试了Primary选项的行为，可以在其中将备份设置为仅在主副本上运行。 作为其他两个选项，只有在使用IF子句的情况下，才可以通过调用sys.Fn_hadr_backup_is_preferred_replica（）函数来控制它。

In this option, we don’t have a scenario where the Primary is offline, as we will always need a Primary replica to be running in order to keep the Availability Group working properly, differently from the secondary replicas.

在此选项中，我们没有主服务器处于脱机状态的情况，因为我们将始终需要运行主服务器副本以保持可用性组正常工作，这与辅助副本不同。

任何副本 ( Any Replica )

We’ve reached the last available option, the “Any Replica” one. As the other options, the implementation of this one is not just dene it and click on the “Ok” button. In this case, this is a little bit more complex…

我们已经达到了最后一个可用选项，即“任何副本”。 作为其他选项，不仅仅可以执行此操作，还可以单击“确定”按钮。 在这种情况下，这有点复杂...

Looking for the name of the option, it might look like a very simple thing. After all, “any replica” mean, all the replicas! But if we stop to think, this would be useless!

寻找选项的名称，可能看起来很简单。 毕竟，“任何副本”是指所有副本！ 但是，如果我们停止思考，这将毫无用处！

The trick here is again on how you will implement the backup strategy. Basically, this option is very flexible, all because of the grid that we have just after the option:

这里的技巧还是关于如何实施备份策略。 基本上，此选项非常灵活，这全都归功于该选项之后的网格：

This grid will allow us to be very flexible on how to define the backup strategy. In the case of my lab, I have only two replicas, but with more replicas, the prioritization could be very handy. Basically this is how the “Any Replica” option would work, based on the information of this grid.

该网格将使我们在定义备份策略方面非常灵活。 就我的实验室而言，我只有两个副本，但是具有更多副本，优先级排序可能非常方便。 基本上，这是基于此网格信息的“任何副本”选项的工作方式。

One of the columns, the “Exclude Replica”, allow us to exclude replicas to be a backup target, if needed. The other column, ”Backup Priority” allow the referred prioritization between nodes, being 1 the lowest priority and 100 the highest one.

如有必要，其中一列“排除副本”使我们可以将副本排除为备份目标。 另一列“备份优先级”允许在节点之间引用优先级，即最低优先级为1，最高优先级为100。

In the screenshot case, we have both replicas with the same weight, what is going to happen? Let’s test!

在屏幕快照的情况下，我们两个副本的权重相同，将会发生什么？ 让我们测试！

First of all, I’m going to generate the backup command from the SQL Server maintenance plan, as I did in the other options.

首先，我将像在其他选项中所做的那样，从SQL Server维护计划中生成备份命令。

Here is the generated backup code:

这是生成的备份代码：

 
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
 

Basically, the code is the same, calling the same function sys.fn_hadr_backup_is_preferred_replica(). So let’s check the behavior of this function, by executing it on both the Primary and the Secondary replicas.

基本上，代码是相同的，调用相同的函数sys.fn_hadr_backup_is_preferred_replica（）。 因此，让我们通过在主副本和辅助副本上执行此功能来检查此功能的行为。

The result was the following:

结果如下：

Server: W2016SRV06
Role: Primary
Weight: 50
Result: Preferred backup replica

Server: W2016SRV07
Role: Secondary
Weight: 50
Result: NON Preferred backup replica

伺服器： W2016SRV06
角色：主要
重量：50
结果：首选备份副本

伺服器： W2016SRV07
角色：中学
重量：50
结果：NON首选备份副本

I started to wonder, what was the criteria to choose the server W2016SRV06 to be the preferred backup replica, if the weight was the same for bot replicas, so I tested again the function execution and the result was the same!

我开始怀疑，如果服务器副本的权重相同，那么选择服务器W2016SRV06作为首选备份副本的标准是什么，因此我再次测试了功能执行和结果是否相同！

Ok, maybe this is happening, because as the weight is the same for all the replicas, the Primary replica has priority by design – NOT!

好的，这可能正在发生，因为所有副本的权重都相同，因此主副本在设计上具有优先级–不是！

I performed a failover and the result was:

我执行了故障转移，结果是：

Server: W2016SRV06
Role: Secondary
Weight: 50
Result: Preferred backup replica

Server: W2016SRV07
Role: Primary
Weight: 50
Result: NON Preferred backup replica

伺服器： W2016SRV06
角色：中学
重量：50
结果：首选备份副本

伺服器： W2016SRV07
角色：主要
重量：50
结果：NON首选备份副本

After that weird result and some research, without find a reason, I decided to investigate further, so I found a possible reason for this. Analyzing the code of the function I found the SELECT statement responsible to help the return of the preferred replica, and there’s something interesting there… Here is code:

经过那个奇怪的结果和一些研究之后，我没有找到原因，所以决定进一步调查，因此我找到了可能的原因。 通过分析该函数的代码，我发现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; 
 

If you notice, there’s an ORDER BY clause, ordering the list of replicas by the “Backup priority” AND by the “Server Name”. Here is the tricky… The defined database server name will influence in the chosen preferred backup replica.

如果您注意到，则有一个ORDER BY子句，按“备份优先级”和“服务器名称”对副本列表进行排序。 这很棘手……定义的数据库服务器名称将影响所选的首选备份副本。

Summarizing the behavior of the “Backup Priority” setting:

总结“备份优先级”设置的行为：

• • Those replicas will only be used if no other is available.
    这些副本仅在没有其他副本可用时使用。
• The replicas with the higher priority value will be on the top of the backup replica choice.
  具有较高优先级值的副本将位于备份副本选项的顶部。
• In the case of have two or more replicas with the same priority value (weight) SQL Server will give priority based on the alphabetical order.
  如果有两个或多个副本具有相同的优先级值（权重），则SQL Server将根据字母顺序给出优先级。

As we are all very smart people, we can customize the things, of course! If you think that your environment would follow another way to prioritize the backups, you can create a customized function in order to override the default SQL Server one. The following SELECT statement helps the start of this:

因为我们都是非常聪明的人，所以我们可以自定义事物！ 如果您认为您的环境将采用另一种方法来对备份进行优先级排序，则可以创建一个自定义函数以覆盖默认SQL Server。 下面的SELECT语句可以帮助您开始：

 
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; 
 

That statement is following the replica server name way to order, in case of the same priority, but your imagination can guide you! You can customize that query to pick the less loaded server, for example… This is not very easy, but feasible with some tricks 🙂

在相同优先级的情况下，该语句遵循副本服务器名称的排序方式，但是您的想象力可以指导您！ 您可以自定义该查询以选择负载较小的服务器，例如……这不是很容易，但可以通过一些技巧来实现。

I hope you liked this article and if you have questions, suggestions or something weird, I’ll be very happy in receive your contact!

希望您喜欢这篇文章，如果您有任何疑问，建议或奇怪的事情，我们将非常高兴收到您的联系！

See you soon!

再见！

翻译自: https://www.sqlshack.com/understanding-backups-on-alwayson-availability-groups-part-2/

alwayson高可用组