针对 SQL 数据库异地复制和故障转移配置 Azure-SSIS 集成运行时Configure the Azure-SSIS integration runtime with SQL Database geo-replication and failover

适用于:是 Azure 数据工厂否 Azure Synapse Analytics(预览版)APPLIES TO: yesAzure Data Factory noAzure Synapse Analytics (Preview)

本文介绍了如何针对 Azure SQL 数据库异地复制和 SSISDB 数据库配置 Azure-SSIS 集成运行时 (IR)。This article describes how to configure the Azure-SSIS integration runtime (IR) with Azure SQL Database geo-replication for the SSISDB database. 发生故障转移时,你可以确保 Azure-SSIS IR 使用辅助数据库保持工作。When a failover occurs, you can ensure that the Azure-SSIS IR keeps working with the secondary database.

有关 SQL 数据库的异地复制和故障转移的详细信息,请参阅概述:活动异地复制和自动故障转移组For more info about geo-replication and failover for SQL Database, see Overview: Active geo-replication and auto-failover groups.

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

使用 SQL 数据库托管实例进行 Azure-SSIS IR 故障转移Azure-SSIS IR failover with a SQL Database managed instance

先决条件Prerequisites

Azure SQL 托管实例使用数据库主密钥 (DMK) 来帮助保护存储在数据库中的数据、凭据和连接信息**。An Azure SQL Database managed instance uses a database master key (DMK) to help secure data, credentials, and connection information that's stored in a database. 为启用 DMK 的自动解密,将通过服务器主密钥 (SMK) 对某个密钥副本进行加密**。To enable the automatic decryption of DMK, a copy of the key is encrypted through the server master key (SMK).

SMK 不会在故障转移组中复制。The SMK is not replicated in a failover group. 故障转移后,需要在主实例和辅助实例上添加用于 DMK 解密的密码。You need to add a password on both the primary and secondary instances for DMK decryption after failover.

  1. 在主实例上为 SSISDB 运行以下命令。Run the following command for SSISDB on the primary instance. 此步骤将添加新的加密密码。This step adds a new encryption password.

    ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'password'
    
  2. 在 Azure SQL 数据库托管实例上创建故障转移组。Create a failover group on an Azure SQL Database managed instance.

  3. 使用新的加密密码在辅助实例上运行 sp_control_dbmasterkey_password****。Run sp_control_dbmasterkey_password on the secondary instance, by using the new encryption password.

    EXEC sp_control_dbmasterkey_password @db_name = N'SSISDB',   
        @password = N'<password>', @action = N'add';  
    GO
    

应用场景 1:Azure-SSIS IR 指向读/写侦听器终结点Scenario 1: Azure-SSIS IR is pointing to a read/write listener endpoint

如果希望 Azure-SSIS IR 指向读/写侦听器终结点,则需要首先指向主服务器终结点。If you want the Azure-SSIS IR to point to a read/write listener endpoint, you need to point to the primary server endpoint first. 将 SSISDB 放入故障转移组后,可以切换到读/写侦听器终结点,然后重启 Azure-SSIS IR。After you put SSISDB in a failover group, you can change to the read/write listener endpoint and restart the Azure-SSIS IR.

解决方案Solution

发生故障转移时,请执行以下步骤:When failover occurs, take the following steps:

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 使用与辅助实例上自定义安装相关的新区域、虚拟网络和共享访问签名 (SAS) URI 信息对 Azure-SSIS IR 进行编辑。Edit the Azure-SSIS IR with new region, virtual network, and shared access signature (SAS) URI information for custom setup on the secondary instance. 由于 Azure-SSIS IR 指向读/写侦听器且终结点对 Azure-SSIS IR 是透明的,因此不需要编辑终结点。Because the Azure-SSIS IR is pointing to a read/write listener and the endpoint is transparent to the Azure-SSIS IR, you don't need to edit the endpoint.

    Set-AzDataFactoryV2IntegrationRuntime -Location "new region" `
                -VNetId "new VNet" `
                -Subnet "new subnet" `
                -SetupScriptContainerSasUri "new custom setup SAS URI"
    
  3. 重启 Azure-SSIS IR。Restart the Azure-SSIS IR.

应用场景 2:Azure-SSIS IR 指向主服务器终结点Scenario 2: Azure-SSIS IR is pointing to a primary server endpoint

如果 Azure-SSIS IR 指向主服务器终结点,则此方案适用。This scenario is suitable if the Azure-SSIS IR is pointing to a primary server endpoint.

解决方案Solution

发生故障转移时,请执行以下步骤:When failover occurs, take the following steps:

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 使用辅助实例的新区域、终结点和虚拟网络信息来编辑 Azure-SSIS IR。Edit the Azure-SSIS IR with new region, endpoint, and virtual network information for the secondary instance.

    Set-AzDataFactoryV2IntegrationRuntime -Location "new region" `
                -CatalogServerEndpoint "Azure SQL Database server endpoint" `
                -CatalogAdminCredential "Azure SQL Database server admin credentials" `
                -VNetId "new VNet" `
                -Subnet "new subnet" `
                -SetupScriptContainerSasUri "new custom setup SAS URI"
    
  3. 重启 Azure-SSIS IR。Restart the Azure-SSIS IR.

应用场景 3:Azure-SSIS IR 指向 SQL 数据库托管实例的公共终结点Scenario 3: Azure-SSIS IR is pointing to a public endpoint of a SQL Database managed instance

如果 Azure-SSIS IR 指向 Azure SQL 数据库托管实例的公共终结点且其不加入虚拟网络,则此方案适用。This scenario is suitable if the Azure-SSIS IR is pointing to a public endpoint of an Azure SQL Database managed instance and it doesn't join to a virtual network. 此方案与方案 2 的唯一区别在于,故障转移后无需编辑 Azure-SSIS IR 的虚拟网络信息。The only difference from scenario 2 is that you don't need to edit virtual network information for the Azure-SSIS IR after failover.

解决方案Solution

发生故障转移时,请执行以下步骤:When failover occurs, take the following steps:

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 利用辅助实例的新区域和终结点信息编辑 Azure-SSIS IR。Edit the Azure-SSIS IR with the new region and endpoint information for the secondary instance.

    Set-AzDataFactoryV2IntegrationRuntime -Location "new region" `
                -CatalogServerEndpoint "Azure SQL Database server endpoint" `
                -CatalogAdminCredential "Azure SQL Database server admin credentials" `
                -SetupScriptContainerSasUri "new custom setup SAS URI"
    
  3. 重启 Azure-SSIS IR。Restart the Azure-SSIS IR.

方案 4:将现有 SSISDB 实例(SSIS 目录)附加到新的 Azure-SSIS IRScenario 4: Attach an existing SSISDB instance (SSIS catalog) to a new Azure-SSIS IR

如果希望 SSISDB 在当前区域中发生 Azure 数据工厂或 Azure-SSIS IR 灾难时在新区域中使用新的 Azure-SSIS IR,则此方案适用。This scenario is suitable if you want SSISDB to work with a new Azure-SSIS IR in a new region when an Azure Data Factory or Azure-SSIS IR disaster occurs in the current region.

解决方案Solution

发生故障转移时,请执行以下步骤。When failover occurs, take the following steps.

Note

使用 PowerShell 执行步骤 4(创建 IR)。Use PowerShell for step 4 (creation of the IR). 如果不执行,Azure 门户将报告一条错误,指出“SSISDB 已存在”。If you don't, the Azure portal will report an error that says SSISDB already exists.

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 运行存储过程以更新 SSISDB 中的元数据,从而接受来自 <new_data_factory_name> 和 <new_integration_runtime_name> 的连接**** ****。Run a stored procedure to update metadata in SSISDB to accept connections from <new_data_factory_name> and <new_integration_runtime_name>.

    EXEC [catalog].[failover_integration_runtime] @data_factory_name='<new_data_factory_name>', @integration_runtime_name='<new_integration_runtime_name>'
    
  3. 在新区域中创建名为 <new_data_factory_name> 的新数据工厂****。Create a new data factory named <new_data_factory_name> in the new region.

    Set-AzDataFactoryV2 -ResourceGroupName "new resource group name" `
                      -Location "new region"`
                      -Name "<new_data_factory_name>"
    

    有关此 PowerShell 命令的详细信息,请参阅使用 PowerShell 创建 Azure 数据工厂For more info about this PowerShell command, see Create an Azure data factory using PowerShell.

  4. 使用 Azure PowerShell 在新区域中创建名为 <new_integration_runtime_name> 的新 Azure-SSIS IR****。Create a new Azure-SSIS IR named <new_integration_runtime_name> in the new region by using Azure PowerShell.

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName "new resource group name" `
                                           -DataFactoryName "new data factory name" `
                                           -Name "<new_integration_runtime_name>" `
                                           -Description $AzureSSISDescription `
                                           -Type Managed `
                                           -Location $AzureSSISLocation `
                                           -NodeSize $AzureSSISNodeSize `
                                           -NodeCount $AzureSSISNodeNumber `
                                           -Edition $AzureSSISEdition `
                                           -LicenseType $AzureSSISLicenseType `
                                           -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
                                           -VnetId "new vnet" `
                                           -Subnet "new subnet" `
                                           -CatalogServerEndpoint $SSISDBServerEndpoint `
                                           -CatalogPricingTier $SSISDBPricingTier
    

    有关此 PowerShell 命令的详细信息,请参阅在 Azure 数据工厂中创建 Azure-SSIS 集成运行时For more info about this PowerShell command, see Create the Azure-SSIS integration runtime in Azure Data Factory.

使用 SQL 数据库进行 Azure-SSIS IR 故障转移Azure-SSIS IR failover with SQL Database

应用场景 1:Azure-SSIS IR 指向读/写侦听器终结点Scenario 1: Azure-SSIS IR is pointing to a read/write listener endpoint

此方案适用于以下情况:This scenario is suitable when:

  • Azure-SSIS IR 指向故障转移组的读/写侦听器终结点。The Azure-SSIS IR is pointing to the read/write listener endpoint of the failover group.
  • SQL 数据库服务器未配置虚拟网络服务终结点规则**。The SQL Database server is not configured with the rule for the virtual network service endpoint.

如果希望 Azure-SSIS IR 指向读/写侦听器终结点,则需要首先指向主服务器终结点。If you want the Azure-SSIS IR to point to a read/write listener endpoint, you need to point to the primary server endpoint first. 将 SSISDB 放入故障转移组后,可以更改为读/写侦听器终结点,然后重启 Azure-SSIS IR。After you put SSISDB in a failover group, you can change to a read/write listener endpoint and restart the Azure-SSIS IR.

解决方案Solution

发生故障转移时,它对 Azure-SSIS IR 是透明的。When failover occurs, it's transparent to the Azure-SSIS IR. Azure-SSIS IR 会自动连接到故障转移组的新的主数据库。The Azure-SSIS IR automatically connects to the new primary of the failover group.

若要更新 Azure-SSIS IR 中的区域或其他信息,可将其停止,对其进行编辑,然后重启。If you want to update the region or other information in the Azure-SSIS IR, you can stop it, edit, and restart.

应用场景 2:Azure-SSIS IR 指向主服务器终结点Scenario 2: Azure-SSIS IR is pointing to a primary server endpoint

如果 Azure-SSIS IR 指向主服务器终结点,则此方案适用。This scenario is suitable if the Azure-SSIS IR is pointing to a primary server endpoint.

解决方案Solution

发生故障转移时,请执行以下步骤:When failover occurs, take the following steps:

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 使用辅助实例的新区域、终结点和虚拟网络信息来编辑 Azure-SSIS IR。Edit the Azure-SSIS IR with new region, endpoint, and virtual network information for the secondary instance.

    Set-AzDataFactoryV2IntegrationRuntime -Location "new region" `
                    -CatalogServerEndpoint "Azure SQL Database server endpoint" `
                    -CatalogAdminCredential "Azure SQL Database server admin credentials" `
                    -VNetId "new VNet" `
                    -Subnet "new subnet" `
                    -SetupScriptContainerSasUri "new custom setup SAS URI"
    
  3. 重启 Azure-SSIS IR。Restart the Azure-SSIS IR.

应用场景 3:将现有 SSISDB(SSIS 目录)附加到新的 Azure-SSIS IRScenario 3: Attach an existing SSISDB (SSIS catalog) to a new Azure-SSIS IR

若要在次要区域中预配新的 Azure-SSIS IR,则此方案适用。This scenario is suitable if you want to provision a new Azure-SSIS IR in a secondary region. 如果希望 SSISDB 在当前区域中发生 Azure 数据工厂或 Azure-SSIS IR 灾难时继续在新区域中使用新的 Azure-SSIS IR,则此方案也适用。It's also suitable if you want your SSISDB to keep working with a new Azure-SSIS IR in a new region when an Azure Data Factory or Azure-SSIS IR disaster occurs in the current region.

解决方案Solution

发生故障转移时,请执行以下步骤。When failover occurs, take the following steps.

Note

使用 PowerShell 执行步骤 4(创建 IR)。Use PowerShell for step 4 (creation of the IR). 如果不执行,Azure 门户将报告一条错误,指出“SSISDB 已存在”。If you don't, the Azure portal will report an error that says SSISDB already exists.

  1. 停止主要区域中的 Azure-SSIS IR。Stop the Azure-SSIS IR in the primary region.

  2. 运行存储过程以更新 SSISDB 中的元数据,从而接受来自 <new_data_factory_name> 和 <new_integration_runtime_name> 的连接**** ****。Run a stored procedure to update metadata in SSISDB to accept connections from <new_data_factory_name> and <new_integration_runtime_name>.

    EXEC [catalog].[failover_integration_runtime] @data_factory_name='<new_data_factory_name>', @integration_runtime_name='<new_integration_runtime_name>'
    
  3. 在新区域中创建名为 <new_data_factory_name> 的新数据工厂****。Create a new data factory named <new_data_factory_name> in the new region.

    Set-AzDataFactoryV2 -ResourceGroupName "new resource group name" `
                         -Location "new region"`
                         -Name "<new_data_factory_name>"
    

    有关此 PowerShell 命令的详细信息,请参阅使用 PowerShell 创建 Azure 数据工厂For more info about this PowerShell command, see Create an Azure data factory using PowerShell.

  4. 使用 Azure PowerShell 在新区域中创建名为 <new_integration_runtime_name> 的新 Azure-SSIS IR****。Create a new Azure-SSIS IR named <new_integration_runtime_name> in the new region by using Azure PowerShell.

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName "new resource group name" `
                                           -DataFactoryName "new data factory name" `
                                           -Name "<new_integration_runtime_name>" `
                                           -Description $AzureSSISDescription `
                                           -Type Managed `
                                           -Location $AzureSSISLocation `
                                           -NodeSize $AzureSSISNodeSize `
                                           -NodeCount $AzureSSISNodeNumber `
                                           -Edition $AzureSSISEdition `
                                           -LicenseType $AzureSSISLicenseType `
                                           -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
                                           -VnetId "new vnet" `
                                           -Subnet "new subnet" `
                                           -CatalogServerEndpoint $SSISDBServerEndpoint `
                                           -CatalogPricingTier $SSISDBPricingTier
    

    有关此 PowerShell 命令的详细信息,请参阅在 Azure 数据工厂中创建 Azure-SSIS 集成运行时For more info about this PowerShell command, see Create the Azure-SSIS integration runtime in Azure Data Factory.

后续步骤Next steps

考虑 Azure-SSIS IR 的以下其他配置选项:Consider these other configuration options for the Azure-SSIS IR: