主页 / dba / 问题 / 204963
Accepted
SQL_Hacker
Asked: 2018-04-26 12:19:10 +0800 CST

SSIS 中基于可用性组状态的条件连接

  • 772

我正在开发一个 SSIS 包，它将从生产备份中刷新开发数据库。该软件包已经存在多年，但我们正在开发实例上部署可用性组，因此必须更改任务以同时使用主副本和辅助副本。背景：所有实例都是SQL Server 2014 SP2。一个生产实例 (PD1)、两个开发实例 (DV3 和 DV7) 和一个实用程序实例 (DV1)，其中 SSIS 包部署到 SSISDB 并运行 SQL 代理作业。在 SSIS 包中，在获取完整备份并将其放在生产环境的网络共享上之后，下一组任务（在单独的 SSIS 包中）需要确定哪个节点托管主副本。我正在按照此处列出的步骤操作去完成这项工作，如果它是硬编码的，一切都会很好（当然，如果/当发生故障转移时，这将不起作用）。我能够考虑解决这个问题的唯一方法是，在运行时评估哪个实例是主要实例，然后继续执行任务（顺便说一下，这些都是“执行 SQL 任务”对象——需要它们自己的连接定义). 我看过一些关于如何在 ConnectionStrings 和变量中使用表达式的示例，但无法弄清楚如何在 SQL Server 中设置从以下查询返回的值以返回正确的实例以在包中设置连接字符串。

    select cs.replica_server_name
from sys.dm_hadr_availability_replica_states rs
join sys.dm_hadr_availability_replica_cluster_states cs
    on rs.replica_id = cs.replica_id
join sys.dm_hadr_name_id_map n
    on rs.group_id = n.ag_id
where rs.role = 2
and n.ag_name = 'DVAG001'

理想情况下，将有两个这样的连接管理器，一个用于主要连接管理器，一个用于辅助连接管理器，然后我可以在“执行 SQL 任务”对象中使用它们。然后一切都会完美地工作。很可能还有其他方法可以做到这一点，但我在 SSIS 或 TSQL 之外的开发方面不是很有经验。

更新：以上过程每天都在进行……这就是我使用 SSIS 包和 SQL 代理的原因。因此，整个过程必须自动化并具有所有必要的值——尤其是副本状态（主要和次要）的确定——在包内，这样就不需要人工干预。

sql-server ssis

2 个回答

  1. Best Answer

    假设您有一个只有一个辅助副本的可用性组（您可以轻松地扩展此示例以获得更多副本）

    1.连接

    您将需要 3 个连接：

    • AG 侦听器（运行将返回哪个服务器持有主服务器和辅助服务器的查询）
    • 主要（与主要服务器一起参数化）
    • 辅助（与辅助服务器一起参数化）

    2.变量

    创建 2 个数据类型为 String 的变量

    • 服务器名称主要
    • ServernameSecondary

    在此处输入图像描述

    3.查询

    要使用执行 SQL 任务将查询结果集映射到变量，您的查询只需要返回一行。您会将每一列映射到您想要的变量。知道这一点后，我已将您的查询更改为以下内容：

    WITH CTE_AGStatus as (
    select cs.replica_server_name, rs.role
    from sys.dm_hadr_availability_replica_states rs
    join sys.dm_hadr_availability_replica_cluster_states cs
        on rs.replica_id = cs.replica_id
    join sys.dm_hadr_name_id_map n
        on rs.group_id = n.ag_id
    where n.ag_name = 'DVAG001'
)

select  ServernamePrimary = MAX(case when role = 1 then replica_server_name end)
,       ServernameSecondary = MAX(case when role = 2 then replica_server_name end)
from CTE_AGStatus

    （随意更改它，只要记住只返回一行）

    4.将结果集映射到变量

    在执行 SQL 任务编辑器上，粘贴上面的查询，设置 OLEDB 连接“AG listener”并将结果集更改为“单行” 执行 SQL 任务编辑器 - 常规选项卡

    然后转到“结果集”选项卡并将列映射到变量 结果集映射到变量

    5.更改连接字符串

    单击“主要”连接管理器并转到“属性”选项卡，然后单击“表达式”，选择属性“ServerName”并在表达式上添加变量。

    在此处输入图像描述

    对“辅助”连接管理器重复该过程

    6.检查过程

    要验证，您可以添加其他步骤并使用断点来检查变量的运行方式。接下来的步骤由您决定。现在您有一个连接指向您的 AG 的每台服务器。 在此处输入图像描述

    • 8

  2. 作为替代策略，我可能会考虑使用 T-SQL 执行（不需要参数）来简化事情而不需要 SSIS 参数。SSIS，尤其是遗留包，看起来总是很容易出错和有问题，如果不需要的话我不想碰它们：

    A首先，考虑在生产服务器本身而不是开发服务器上运行“主要 AG 检查”。

    B.然后，将您的 SSIS 包导入到生产环境中（如果它们尚未存在）——以便在那里运行。无论如何，人们可能会考虑从更多的生产过程中进行开发恢复。

    C.最后，通过运行 T-SQL 代理作业尽可能地避免更改遗留 SSIS 包，该作业使用以下代码从每个“潜在”主 AG 调用 SSIS 包执行（数据库还原）——或类似代码—— - 如清单 A 所示。

    清单 A： 仅当 SQL Server 是主要服务器时才执行 SSIS 包（数据库还原）——通过 SQL 代理作业。

    --If current server is the primary AG then run the legacy package

if (select a.role_desc FROM sys.dm_hadr_availability_replica_states AS a JOIN sys.availability_replicas AS b  ON b.replica_id = a.replica_id WHERE b.replica_server_name like @@Servername) like 'Primary' 
begin
            Declare @execution_id bigint  
            EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'AlwaysOn', @project_name=N'MyTestPackage', @use32bitruntime=False, @reference_id=Null  
                exec ssisdb.catalog.start_execution @execution_id 
end

    笔记：

    如果我没理解错的话，开发服务器的名称将始终相同。只是生产服务器名称有可能发生变化。鉴于此，我相信清单 A 中显示的代码将使您能够以更少的时间和精力完成所需的工作。您甚至可以在 SQL 备份结束时搭载此执行，以更直接地刷新开发环境。

    • 0

相关问题