配置 Azure-SSIS 集成运行时以实现业务连续性和灾难恢复 (BCDR)Configure Azure-SSIS integration runtime for business continuity and disaster recovery (BCDR)

适用于: Azure 数据工厂 Azure Synapse Analytics

Azure 数据工厂 (ADF) 中的 Azure SQL 数据库/托管实例和 SQL Server Integration Services (SSIS) 可以组合为用于 SQL Server 迁移的所有平台即服务 (PaaS) 推荐解决方案。Azure SQL Database/Managed Instance and SQL Server Integration Services (SSIS) in Azure Data Factory (ADF) can be combined as the recommended all-Platform as a Service (PaaS) solution for SQL Server migration. 你可以将 SSIS 项目部署到由 Azure SQL 数据库/托管实例托管的 SSIS 目录数据库 (SSISDB),并在 ADF 的 Azure SSIS 集成运行时 (IR) 上运行 SSIS 包。You can deploy your SSIS projects into SSIS catalog database (SSISDB) hosted by Azure SQL Database/Managed Instance and run your SSIS packages on Azure SSIS integration runtime (IR) in ADF.

为实现业务连续性和灾难恢复 (BCDR),可以使用异地复制/故障转移组来配置 Azure SQL 数据库/托管实例,其中,具有读写访问权限(主要角色)的主要 Azure 区域中的 SSISDB 会持续复制到具有只读访问权限(辅助角色)的次要区域。For business continuity and disaster recovery (BCDR), Azure SQL Database/Managed Instance can be configured with a geo-replication/failover group, where SSISDB in a primary Azure region with read-write access (primary role) will be continuously replicated to a secondary region with read-only access (secondary role). 当主要区域发生灾难时,将触发故障转移,主要和辅助 SSISDB 将在其中交换角色。When a disaster occurs in the primary region, a failover will be triggered, where the primary and secondary SSISDBs will swap roles.

对于 BCDR,还可以配置与 Azure SQL 数据库/托管实例故障转移组同步运行的双重备用 Azure SSIS IR 对。For BCDR, you can also configure a dual standby Azure SSIS IR pair that works in sync with Azure SQL Database/Managed Instance failover group. 这允许你有一对在任何给定时间运行的 Azure-SSIS IR,只有一个可以访问主要 SSISDB 来提取和执行包,并写入包执行日志(主要角色),而另一个则只能对部署在其他位置的包执行相同操作,例如,在 Azure 文件存储中(辅助角色)。This allows you to have a pair of running Azure-SSIS IRs that at any given time, only one can access the primary SSISDB to fetch and execute packages, as well as write package execution logs (primary role), while the other can only do the same for packages deployed somewhere else, for example in Azure Files (secondary role). 发生 SSISDB 故障转移时,主要和辅助 Azure-SSIS IR 还将交换角色,如果两者都在运行,那么停机时间近乎为零。When SSISDB failover occurs, the primary and secondary Azure-SSIS IRs will also swap roles and if both are running, there'll be a near-zero downtime.

本文介绍如何在 BCDR 的 Azure SQL 数据库/托管实例故障转移组中配置 Azure-SSIS IR。This article describes how to configure Azure-SSIS IR with Azure SQL Database/Managed Instance failover group for BCDR.

使用 Azure SQL 数据库故障转移组配置双重备用 Azure-SSIS IR 对Configure a dual standby Azure-SSIS IR pair with Azure SQL Database failover group

若要配置与 Azure SQL 数据库故障转移组同步运行的双重备用 Azure-SSIS IR 对,请完成以下步骤。To configure a dual standby Azure-SSIS IR pair that works in sync with Azure SQL Database failover group, complete the following steps.

  1. 使用 Azure 门户/ADF UI,你可以创建一个带有主要 Azure SQL 数据库服务器的新 Azure-SSIS IR,以在主要区域中托管 SSISDB。Using Azure portal/ADF UI, you can create a new Azure-SSIS IR with your primary Azure SQL Database server to host SSISDB in the primary region. 如果现有 Azure-SSIS IR 已附加到 Azure SQL 数据库服务器托管的 SSIDB,并且仍在运行,则需要先将其停止才能重新配置它。If you have an existing Azure-SSIS IR that's already attached to SSIDB hosted by your primary Azure SQL Database server and it's still running, you need to stop it first to reconfigure it. 这将是主要 Azure-SSIS IR。This will be your primary Azure-SSIS IR.

    在“集成运行时设置”窗格的“部署设置”页上选择使用 SSISDB时,请选中“结合使用双重备用 Azure-SSIS Integration Runtime 对和 SSISDB 故障转移”复选框。When selecting to use SSISDB on the Deployment settings page of Integration runtime setup pane, select also the Use dual standby Azure-SSIS Integration Runtime pair with SSISDB failover check box. 对于“双重备用对名称”,请输入一个名称以标识主要和辅助 Azure-SSIS IR 对。For Dual standby pair name, enter a name to identify your pair of primary and secondary Azure-SSIS IRs. 完成主要 Azure-SSIS IR 的创建后,它将启动并附加到主要 SSISDB,该 SSISDB 将以你的身份创建,具有读写访问权限。When you complete the creation of your primary Azure-SSIS IR, it will be started and attached to a primary SSISDB that will be created on your behalf with read-write access. 如果刚重新配置了它,则需要重新启动。If you've just reconfigured it, you need to restart it.

  2. 使用 Azure 门户,可以检查是否已在主 Azure SQL 数据库服务器的“概述”页上创建了主要 SSISDB。Using Azure portal, you can check whether the primary SSISDB has been created on the Overview page of your primary Azure SQL Database server. 创建后,可以在“故障转移组”页上为主要和辅助 Azure SQL 数据库服务器创建故障转移组,并向其添加 SSISDBOnce it's created, you can create a failover group for your primary and secondary Azure SQL Database servers and add SSISDB to it on the Failover groups page. 创建故障转移组后,可以检查是否已在辅助 Azure SQL 数据库服务器的“概述”页上将主要 SSISDB 复制到具有只读访问权限的辅助 SSISDB。Once your failover group is created, you can check whether the primary SSISDB has been replicated to a secondary one with read-only access on the Overview page of your secondary Azure SQL Database server.

  3. 使用 Azure 门户/ADF UI,你可以通过辅助 Azure SQL 数据库服务器创建另一个 Azure-SSIS IR 来托管次要区域中的 SSISDB。Using Azure portal/ADF UI, you can create another Azure-SSIS IR with your secondary Azure SQL Database server to host SSISDB in the secondary region. 这将是你的辅助 Azure-SSIS IR。This will be your secondary Azure-SSIS IR. 对于完整的 BCDR,请确保还会在次要区域中创建它所依赖的所有资源,例如,用于存储自定义安装程序脚本/文件的 Azure 存储、用于业务流程/计划包执行的 ADF 等。For complete BCDR, make sure that all resources it depends on are also created in the secondary region, for example Azure Storage for storing custom setup script/files, ADF for orchestration/scheduling package executions, etc.

    在“集成运行时设置”窗格的“部署设置”页上选择使用 SSISDB时,请选中“结合使用双重备用 Azure-SSIS Integration Runtime 对和 SSISDB 故障转移”复选框。When selecting to use SSISDB on the Deployment settings page of Integration runtime setup pane, select also the Use dual standby Azure-SSIS Integration Runtime pair with SSISDB failover check box. 对于“双重备用对名称”,请输入相同名称以标识主要和辅助 Azure-SSIS IR 对。For Dual standby pair name, enter the same name to identify your pair of primary and secondary Azure-SSIS IRs. 完成辅助 Azure-SSIS IR 的创建后,它将启动并附加到辅助 SSISDB。When you complete the creation of your secondary Azure-SSIS IR, it will be started and attached to the secondary SSISDB.

  4. 如果希望在发生 SSISDB 故障转移时停机时间近乎零,请将两个 Azure SSIS IR 保持运行状态。If you want to have a near-zero downtime when SSISDB failover occurs, keep both of your Azure-SSIS IRs running. 只有主要 Azure-SSIS IR 可以访问主要 SSISDB 来提取和执行包,还可以写入包执行日志,而辅助 Azure-SSIS IR 只能对部署在其他位置的包(例如在 Azure 文件存储中)执行相同操作。Only your primary Azure-SSIS IR can access the primary SSISDB to fetch and execute packages, as well as write package execution logs, while your secondary Azure-SSIS IR can only do the same for packages deployed somewhere else, for example in Azure Files.

    如果要最大程度地降低运行成本,可以在创建辅助 Azure-SSIS IR 后将其停止。If you want to minimize your running cost, you can stop your secondary Azure-SSIS IR after it's created. 发生 SSISDB 故障转移时,主要和辅助 Azure-SSIS IR 将交换角色。When SSISDB failover occurs, your primary and secondary Azure-SSIS IRs will swap roles. 如果主要 Azure-SSIS IR 停止,则需要重新启动它。If your primary Azure-SSIS IR is stopped, you need to restart it. 根据它是否注入到虚拟网络和使用的注入方法,运行时间在 5 分钟或 20 - 30 分钟左右。Depending on whether it's injected into a virtual network and the injection method used, it will take within 5 minutes or around 20 - 30 minutes for it to run.

  5. 如果使用用于业务流程/计划包执行的 ADF,请确保所有带有执行 SSIS 包活动和关联触发器的相关 ADF 管道都复制到辅助 ADF(触发器最初处于禁用状态)。If you use ADF for orchestration/scheduling package executions, make sure that all relevant ADF pipelines with Execute SSIS Package activities and associated triggers are copied to your secondary ADF with the triggers initially disabled. 发生 SSISDB 故障转移时,需要启用它们。When SSISDB failover occurs, you need to enable them.

  6. 你可以测试 Azure SQL 数据库故障转移组,并在 ADF 门户中查看 Azure-SSIS IR 监视页,无论主要和辅助 Azure SSIS IR 是否已交换角色。You can test your Azure SQL Database failover group and check on Azure-SSIS IR monitoring page in ADF portal whether your primary and secondary Azure-SSIS IRs have swapped roles.

使用 Azure SQL 托管实例故障转移组配置双重备用 Azure-SSIS IR 对Configure a dual standby Azure-SSIS IR pair with Azure SQL Managed Instance failover group

若要配置与 Azure SQL 托管实例故障转移组同步运行的双重备用 Azure-SSIS IR 对,请完成以下步骤。To configure a dual standby Azure-SSIS IR pair that works in sync with Azure SQL Managed Instance failover group, complete the following steps.

  1. 使用 Azure 门户,可以在主要 Azure SQL 托管实例的“故障转移组”页上为主要和辅助 Azure SQL 托管实例创建故障转移组Using Azure portal, you can create a failover group for your primary and secondary Azure SQL Managed Instances on the Failover groups page of your primary Azure SQL Managed Instance.

  2. 使用 Azure 门户/ADF UI,你可以创建一个带有主要 Azure SQL 托管实例的新 Azure-SSIS IR,以在主要区域中托管 SSISDB。Using Azure portal/ADF UI, you can create a new Azure-SSIS IR with your primary Azure SQL Managed Instance to host SSISDB in the primary region. 如果现有 Azure-SSIS IR 已附加到 Azure SQL 托管实例托管的 SSIDB,并且仍在运行,则需要先将其停止才能重新配置它。If you have an existing Azure-SSIS IR that's already attached to SSIDB hosted by your primary Azure SQL Managed Instance and it's still running, you need to stop it first to reconfigure it. 这将是主要 Azure-SSIS IR。This will be your primary Azure-SSIS IR.

    在“集成运行时设置”窗格的“部署设置”页上选择使用 SSISDB时,请选中“结合使用双重备用 Azure-SSIS Integration Runtime 对和 SSISDB 故障转移”复选框。When selecting to use SSISDB on the Deployment settings page of Integration runtime setup pane, select also the Use dual standby Azure-SSIS Integration Runtime pair with SSISDB failover check box. 对于“双重备用对名称”,请输入一个名称以标识主要和辅助 Azure-SSIS IR 对。For Dual standby pair name, enter a name to identify your pair of primary and secondary Azure-SSIS IRs. 完成主要 Azure-SSIS IR 的创建后,它将启动并附加到主要 SSISDB,该 SSISDB 将以你的身份创建,具有读写访问权限。When you complete the creation of your primary Azure-SSIS IR, it will be started and attached to a primary SSISDB that will be created on your behalf with read-write access. 如果刚重新配置了它,则需要重新启动。If you've just reconfigured it, you need to restart it. 还可以检查是否已在辅助 Azure SQL 托管实例的“概述”页上将主要 SSISDB 复制到具有只读访问权限的辅助 SSISDB。You can also check whether the primary SSISDB has been replicated to a secondary one with read-only access on the Overview page of your secondary Azure SQL Managed Instance.

  3. 使用 Azure 门户/ADF UI,你可以通过辅助 Azure SQL 托管实例创建另一个 Azure-SSIS IR 来托管次要区域中的 SSISDB。Using Azure portal/ADF UI, you can create another Azure-SSIS IR with your secondary Azure SQL Managed Instance to host SSISDB in the secondary region. 这将是你的辅助 Azure-SSIS IR。This will be your secondary Azure-SSIS IR. 对于完整的 BCDR,请确保还会在次要区域中创建它所依赖的所有资源,例如,用于存储自定义安装程序脚本/文件的 Azure 存储、用于业务流程/计划包执行的 ADF 等。For complete BCDR, make sure that all resources it depends on are also created in the secondary region, for example Azure Storage for storing custom setup script/files, ADF for orchestration/scheduling package executions, etc.

    在“集成运行时设置”窗格的“部署设置”页上选择使用 SSISDB时,请选中“结合使用双重备用 Azure-SSIS Integration Runtime 对和 SSISDB 故障转移”复选框。When selecting to use SSISDB on the Deployment settings page of Integration runtime setup pane, select also the Use dual standby Azure-SSIS Integration Runtime pair with SSISDB failover check box. 对于“双重备用对名称”,请输入相同名称以标识主要和辅助 Azure-SSIS IR 对。For Dual standby pair name, enter the same name to identify your pair of primary and secondary Azure-SSIS IRs. 完成辅助 Azure-SSIS IR 的创建后,它将启动并附加到辅助 SSISDB。When you complete the creation of your secondary Azure-SSIS IR, it will be started and attached to the secondary SSISDB.

  4. Azure SQL 托管实例可以通过使用数据库主密钥 (DMK) 加密数据库中的敏感数据(例如 SSISDB)来保护这些数据。Azure SQL Managed Instance can secure sensitive data in databases, such as SSISDB, by encrypting them using Database Master Key (DMK). 默认情况下,DMK 本身使用服务主密钥 (SMK) 进行加密。DMK itself is in turn encrypted using Service Master Key (SMK) by default. 撰写本文时,Azure SQL 托管实例故障转移组不会从主 Azure SQL 托管实例复制 SMK,因此,在发生故障转移后,DMK 和 SSISDB 无法在辅助 Azure SQL 托管实例上解密。At the time of writing, Azure SQL Managed Instance failover group doesn't replicate SMK from the primary Azure SQL Managed Instance, so DMK and in turn SSISDB can't be decrypted on the secondary Azure SQL Managed Instance after failover occurs. 若要解决这个问题,可以添加一个密码加密,以在辅助 Azure SQL 托管实例上对 DMK 进行解密。To work around this, you can add a password encryption for DMK to be decrypted on the secondary Azure SQL Managed Instance. 使用 SSMS 完成以下步骤。Using SSMS, complete the following steps.

    1. 为主要 Azure SQL 托管实例中的 SSISDB 运行以下命令,以添加用于加密 DMK 的密码。Run the following command for SSISDB in your primary Azure SQL Managed Instance to add a password for encrypting DMK.

      ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'YourPassword'
      
    2. 在主要和辅助 Azure SQL 托管实例中,为 SSISDB 运行以下命令,以添加用于解密 DMK 的新密码。Run the following command for SSISDB in both your primary and secondary Azure SQL Managed Instances to add the new password for decrypting DMK.

      EXEC sp_control_dbmasterkey_password @db_name = N'SSISDB', @password = N'YourPassword', @action = N'add'
      
  5. 如果希望在发生 SSISDB 故障转移时停机时间近乎零,请将两个 Azure SSIS IR 保持运行状态。If you want to have a near-zero downtime when SSISDB failover occurs, keep both of your Azure-SSIS IRs running. 只有主要 Azure-SSIS IR 可以访问主要 SSISDB 来提取和执行包,还可以写入包执行日志,而辅助 Azure-SSIS IR 只能对部署在其他位置的包(例如在 Azure 文件存储中)执行相同操作。Only your primary Azure-SSIS IR can access the primary SSISDB to fetch and execute packages, as well as write package execution logs, while your secondary Azure-SSIS IR can only do the same for packages deployed somewhere else, for example in Azure Files.

    如果要最大程度地降低运行成本,可以在创建辅助 Azure-SSIS IR 后将其停止。If you want to minimize your running cost, you can stop your secondary Azure-SSIS IR after it's created. 发生 SSISDB 故障转移时,主要和辅助 Azure-SSIS IR 将交换角色。When SSISDB failover occurs, your primary and secondary Azure-SSIS IRs will swap roles. 如果主要 Azure-SSIS IR 停止,则需要重新启动它。If your primary Azure-SSIS IR is stopped, you need to restart it. 根据它是否注入到虚拟网络和使用的注入方法,运行时间在 5 分钟或 20 - 30 分钟左右。Depending on whether it's injected into a virtual network and the injection method used, it will take within 5 minutes or around 20 - 30 minutes for it to run.

  6. 如果使用 Azure SQL 托管实例代理进行业务流程/计划包执行,请确保将所有相关的 SSIS 作业及其作业步骤和关联的计划都复制到辅助 Azure SQL 托管实例(这些计划最初处于禁用状态)。If you use Azure SQL Managed Instance Agent for orchestration/scheduling package executions, make sure that all relevant SSIS jobs with their job steps and associated schedules are copied to your secondary Azure SQL Managed Instance with the schedules initially disabled. 使用 SSMS 完成以下步骤。Using SSMS, complete the following steps.

    1. 对于每个 SSIS 作业,右键单击并选择“将作业脚本撰写为”、“创建到”以及“新建查询编辑器窗口”下拉菜单项来生成脚本。For each SSIS job, right-click and select the Script Job as, CREATE To, and New Query Editor Window dropdown menu items to generate its script.

      生成 SSIS 作业脚本

    2. 对于每个生成的 SSIS 作业脚本,查找用于执行 sp_add_job 存储过程的命令,并根据需要修改/删除 @owner_login_name 参数的赋值。For each generated SSIS job script, find the command to execute sp_add_job stored procedure and modify/remove the value assignment to @owner_login_name argument as necessary.

    3. 对于每个更新的 SSIS 作业脚本,请在辅助 Azure SQL 托管实例上运行该脚本,以复制该作业及其作业步骤和关联的计划。For each updated SSIS job script, run it on your secondary Azure SQL Managed Instance to copy the job with its job steps and associated schedules.

    4. 使用以下脚本创建新的 T-SQL 作业,以在主要和辅助 Azure SQL 托管实例中基于主要/辅助 SSISDB 角色分别启用/禁用 SSIS 作业计划,并定期运行。Using the following script, create a new T-SQL job to enable/disable SSIS job schedules based on the primary/secondary SSISDB role, respectively, in both your primary and secondary Azure SQL Managed Instances and run it regularly. 发生 SSISDB 故障转移时,将启用禁用的 SSIS 作业计划,反之亦然。When SSISDB failover occurs, SSIS job schedules that were disabled will be enabled and vice versa.

      IF (SELECT Top 1 role_desc FROM SSISDB.sys.dm_geo_replication_link_status WHERE partner_database = 'SSISDB') = 'PRIMARY'
         BEGIN
            IF (SELECT enabled FROM msdb.dbo.sysschedules WHERE schedule_id = <ScheduleID>) = 0
               EXEC msdb.dbo.sp_update_schedule @schedule_id = <ScheduleID >, @enabled = 1
         END
      ELSE
         BEGIN
            IF (SELECT enabled FROM msdb.dbo.sysschedules WHERE schedule_id = <ScheduleID>) = 1
               EXEC msdb.dbo.sp_update_schedule @schedule_id = <ScheduleID >, @enabled = 0
         END
      
  7. 如果使用用于业务流程/计划包执行的 ADF,请确保所有带有执行 SSIS 包活动和关联触发器的相关 ADF 管道都复制到辅助 ADF(触发器最初处于禁用状态)。If you use ADF for orchestration/scheduling package executions, make sure that all relevant ADF pipelines with Execute SSIS Package activities and associated triggers are copied to your secondary ADF with the triggers initially disabled. 发生 SSISDB 故障转移时,需要启用它们。When SSISDB failover occurs, you need to enable them.

  8. 你可以测试 Azure SQL 托管实例故障转移组,并在 ADF 门户中查看 Azure-SSIS IR 监视页,无论主要和辅助 Azure SSIS IR 是否已交换角色。You can test your Azure SQL Managed Instance failover group and check on Azure-SSIS IR monitoring page in ADF portal whether your primary and secondary Azure-SSIS IRs have swapped roles.

将新 Azure-SSIS IR 附加到 Azure SQL 数据库/托管实例所托管的现有 SSISDB 中Attach a new Azure-SSIS IR to existing SSISDB hosted by Azure SQL Database/Managed Instance

如果发生灾难影响现有 Azure-SSIS IR,但不影响同一区域中的 Azure SQL 数据库/托管实例,则可以将其替换为另一个区域中的新 Azure-SSIS IR。If a disaster occurs and impacts your existing Azure-SSIS IR but not Azure SQL Database/Managed Instance in the same region, you can replace it with a new one in another region. 若要将 Azure SQL 数据库/托管实例托管的现有 SSISDB 附加到新 Azure-SSIS IR,请完成以下步骤。To attach your existing SSISDB hosted by Azure SQL Database/Managed Instance to a new Azure-SSIS IR, complete the following steps.

  1. 如果现有 Azure-SSIS IR 仍在运行,则需要先使用 Azure 门户/ADF UI 或 Azure PowerShell 停止它。If your existing Azure-SSIS IR is still running, you need to stop it first using Azure portal/ADF UI or Azure PowerShell. 如果灾难还影响同一区域中的 ADF,则可以跳过此步骤。If the disaster also impacts ADF in the same region, you can skip this step.

  2. 使用 SSMS,对 Azure SQL 数据库/托管实例中的 SSISDB 运行以下命令,以更新将允许来自新 ADF/Azure-SSIS IR 的连接的元数据。Using SSMS, run the following command for SSISDB in your Azure SQL Database/Managed Instance to update the metadata that will allow connections from your new ADF/Azure-SSIS IR.

    EXEC [catalog].[failover_integration_runtime] @data_factory_name = 'YourNewADF', @integration_runtime_name = 'YourNewAzureSSISIR'
    
  3. 使用 Azure 门户/ADF UIAzure PowerShell,在另一个区域中分别创建名为 YourNewADF/YourNewAzureSSISIR 的新 ADF/Azure-SSIS IR。Using Azure portal/ADF UI or Azure PowerShell, create your new ADF/Azure-SSIS IR named YourNewADF/YourNewAzureSSISIR, respectively, in another region. 如果使用 Azure 门户/ADF UI,则可以忽略“集成运行时设置”窗格的“部署设置”页上的测试连接错误。If you use Azure portal/ADF UI, you can ignore the test connection error on Deployment settings page of Integration runtime setup pane.

后续步骤Next steps

可以考虑 Azure-SSIS IR 的以下其他配置选项:You can consider these other configuration options for your Azure-SSIS IR: