将 SQL Server Integration Services 工作负荷直接迁移到云Lift and shift SQL Server Integration Services workloads to the cloud

适用范围:SQL Server,包括 Linux Azure SQL 数据库yesAzure SQL 数据仓库no并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

现在,可将 SQL Server Integration Services (SSIS) 项目、包和工作负荷移到 Azure 云。You can now move your SQL Server Integration Services (SSIS) projects, packages, and workloads to the Azure cloud. 在 Azure SQL 数据库的 SSIS 目录 (SSISDB) 或 SQL 数据库托管实例中使用 SQL Server Management Studio (SSMS) 等熟悉的工具来部署、运行和管理 SSIS 项目和包。Deploy, run, and manage SSIS projects and packages in the SSIS Catalog (SSISDB) on Azure SQL Database or SQL Database Managed Instance with familiar tools such as SQL Server Management Studio (SSMS).

优势Benefits

将本地 SSIS 工作负荷移到 Azure 具有以下潜在好处:Moving your on-premises SSIS workloads to Azure has the following potential benefits:

  • 降低运营成本和减轻在本地或 Azure 虚拟机上运行 SSIS 时的基础结构管理负担。Reduce operational costs and reduce the burden of managing infrastructure that you have when you run SSIS on-premises or on Azure virtual machines.
  • 通过实现每个群集指定多个节点,以及 Azure 和 Azure SQL 数据库的高可用性功能,增加高可用性。Increase high availability with the ability to specify multiple nodes per cluster, as well as the high availability features of Azure and of Azure SQL Database.
  • 通过实现每个节点指定多个核心(纵向扩展)和每个群集指定多个节点(横向扩展),提高可伸缩性。Increase scalability with the ability to specify multiple cores per node (scale up) and multiple nodes per cluster (scale out).

Azure 上的 SSIS 的体系结构Architecture of SSIS on Azure

下表突出显示了本地 SSIS 和 Azure 上的 SSIS 之间的差异。The following table highlights the differences between SSIS on premises and SSIS on Azure.

最显著的差异是存储与运行时的分离。The most significant difference is the separation of storage from runtime. Azure 数据工厂为 Azure 上的 SSIS 包承载运行时引擎。Azure Data Factory hosts the runtime engine for SSIS packages on Azure. 运行时引擎名为 Azure-SSIS Integration Runtime (Azure-SSIS IR)。The runtime engine is called the Azure-SSIS Integration Runtime (Azure-SSIS IR). 有关详细信息,请参阅 Azure-SSIS Integration RuntimeFor more info, see Azure-SSIS Integration Runtime.

位置Location 存储器Storage 运行时Runtime 可伸缩性Scalability
本地On premises SQL ServerSQL Server SQL Server 托管的 SSIS 运行时SSIS runtime hosted by SQL Server SSIS Scale Out(SQL Server 2017 及更高版本中)SSIS Scale Out (in SQL Server 2017 and later)

自定义解决方案(之前的 SQL Server 版本中)Custom solutions (in prior versions of SQL Server)
在 Azure 上On Azure SQL 数据库或 SQL 数据库托管实例SQL Database or SQL Database Managed Instance Azure SSIS Integration Runtime(Azure 数据工厂的一个组件)Azure-SSIS Integration Runtime, a component of Azure Data Factory Azure-SSIS Integration Runtime 的缩放选项Scaling options for the Azure-SSIS Integration Runtime

在 Azure 上预配 SSISProvision SSIS on Azure

预配Provision. 必须先预配 SSIS 目录 (SSISDB) 和 Azure SSIS Integration Runtime,然后才能在 Azure 中部署和运行 SSIS 包。Before you can deploy and run SSIS packages in Azure, you have to provision the SSIS Catalog (SSISDB) and the Azure-SSIS Integration Runtime.

仅需设置 Azure-SSIS IR 一次。You only have to provision the Azure-SSIS IR one time. 此后,可使用 SQL Server Data Tools (SSDT) 和 SQL Server Management Studio (SSMS) 等熟悉工具来部署、配置、运行、监视、计划和管理包。After that, you can use familiar tools such as SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) to deploy, configure, run, monitor, schedule, and manage packages.

备注

暂不向任何 Azure 区域提供 Azure-SSIS Integration Runtime。The Azure-SSIS Integration Runtime is not yet available in all Azure regions. 有关支持区域的信息,请参阅可用产品(按区域)- Microsoft AzureFor info about the supported regions, see Products available by region - Microsoft Azure.

纵向和横向扩展。设置 Azure-SSIS IR 时,可通过指定以下选项的值,实现纵向和横向扩展:Scale up and out. When you provision the Azure-SSIS IR, you can scale up and scale out by specifying values for the following options:

  • 群集中的节点大小(包括核心数)和节点数。The node size (including the number of cores) and the number of nodes in the cluster.
  • 用于承载 SSIS 目录数据库 (SSISDB) 的 Azure SQL 数据库的现有实例,以及数据库的服务层。The existing instance of Azure SQL Database to host the SSIS Catalog Database (SSISDB), and the service tier for the database.
  • 每个节点最大并行执行数。The maximum parallel executions per node.

改善性能Improve performance. 有关详细信息,请参阅配置 Azure-SSIS Integration Runtime 以获得高性能For more info, see Configure the Azure-SSIS Integration Runtime for high performance.

降低成本。Reduce costs. 若要降低成本,请仅在需要时才运行 Azure-SSIS IR。To reduce costs, run the Azure-SSIS IR only when you need it. 有关详细信息,请参阅如何计划 Azure SSIS integration runtime 的开始和结束时间For more info, see How to schedule starting and stopping of an Azure SSIS integration runtime.

设计包Design packages

继续通过使用 SSDT 或使用安装了 SSDT 的 Visual Studio ,在本地设计和生成包。You continue to design and build packages on-premises in SSDT, or in Visual Studio with SSDT installed.

连接到数据源Connect to data sources

若要使用 Windows 身份验证从云连接到本地数据源,请参阅从 Azure 的 SSIS 包中使用 Windows 身份验证连接到数据源和文件共享To connect to on-premises data sources from the cloud with Windows authentication, see Connect to data sources and file shares with Windows Authentication from SSIS packages in Azure.

若要连接到文件和文件共享,请参阅使用 Azure 中部署的 SSIS 包在本地和在 Azure 中打开和保存文件To connect to files and file shares, see Open and save files on premises and in Azure with SSIS packages deployed in Azure.

可用的 SSIS 组件Available SSIS components

如果要设置 SQL 数据库实例来承载 SSISDB,还需安装用于 SSIS 的 Azure 功能包和 Access 可再发行组件。When you provision an instance of SQL Database to host SSISDB, the Azure Feature Pack for SSIS and the Access Redistributable are also installed. 除提供与内置组件支持的数据源的连接外,这些组件还提供与 Excel 和 Access 文件和各种 Azure 数据源的连接。These components provide connectivity to various Azure data sources and to Excel and Access files, in addition to the data sources supported by the built-in components.

还可以安装其他组件,例如可以安装默认情况下未安装的驱动程序。You can also install additional components - for example, you can install a driver that's not installed by default. 有关详细信息,请参阅 Azure-SSIS integration runtime 的自定义安装For more info, see Customize setup for the Azure-SSIS integration runtime.

如果拥有企业版许可证,还可使用其他组件。If you have an Enterprise Edition license, additional components are available. 有关详细信息,请参阅预配 Azure-SSIS Integration Runtime 企业版For more info, see Provision Enterprise Edition for the Azure-SSIS Integration Runtime.

如果你是 ISV,则可更新许可组件的安装,使其在 Azure 上可用。If you're an ISV, you can update the installation of your licensed components to make them available on Azure. 有关详细信息,请参阅为 Azure-SSIS Integration Runtime 安装已付费或已许可的自定义组件For more info, see Install paid or licensed custom components for the Azure-SSIS integration runtime.

事务支持Transaction support

通过本地和 Azure 虚拟机上的 SQL Server,可使用 Microsoft 分布式事务处理协调器 (MSDTC) 事务。With SQL Server on premises and on Azure virtual machines, you can use Microsoft Distributed Transaction Coordinator (MSDTC) transactions. 要在 Azure-SSIS IR 的每个节点上配置 MSDTC,请使用自定义安装功能。To configure MSDTC on each node of the Azure-SSIS IR, use the custom setup capability. 有关详细信息,请参阅 Azure-SSIS 集成运行时的自定义安装For more info, see Custom setup for the Azure-SSIS integration runtime.

借助 Azure SQL 数据库,只能使用弹性事务。With Azure SQL Database, you can only use elastic transactions. 有关详细信息,请参阅跨云数据库的分布式事务For more info, see Distributed transactions across cloud databases.

部署和运行包Deploy and run packages

首先,请参阅教程:在 Azure 中部署和运行 SQL Server Integration Services (SSIS) 包To get started, see Tutorial: Deploy and run a SQL Server Integration Services (SSIS) package in Azure.

必备条件Prerequisites

要将 SSIS 包部署到 Azure,必须具有以下任一 SQL Server Data Tools (SSDT) 版本:To deploy SSIS packages to Azure, you have to have one of the following versions of SQL Server Data Tools (SSDT):

  • 对于 Visual Studio 2017,需要版本 15.3 或更高版本。For Visual Studio 2017, version 15.3 or later.
  • 对于 Visual Studio 2015,需要版本 17.2 或更高版本。For Visual Studio 2015, version 17.2 or later.

连接到 SSISDBConnect to SSISDB

承载 SSISDB 的 SQL 数据库的名称(格式为 <sql_database_name>.database.windows.net)将成为四部分名称的第一部分,从 SSDT 和 SSMS 中部署和运行包时会使用到该名称。The name of the SQL Database that hosts SSISDB becomes the first part of the four-part name to use when you deploy and run packages from SSDT and SSMS, in the following format - <sql_database_name>.database.windows.net. 有关如何连接到 Azure 中的 SSIS 目录数据库的详细信息,请参阅连接到 Azure 中的 SSIS 目录 (SSISDB)For more info about how to connect to the SSIS Catalog database in Azure, see Connect to the SSIS Catalog (SSISDB) in Azure.

部署项目和包Deploy projects and packages

在 Azure 上将项目部署到 SSISDB 时,需要使用“项目部署模型”,而不是包部署模型。You have to use the project deployment model, not the package deployment model, when you deploy projects to SSISDB on Azure.

若要在 Azure 上部署项目,可以使用以下多种熟悉的工具和脚本编写选项中的一种来实现:To deploy projects on Azure, you can use one of several familiar tools and scripting options:

  • SQL Server Management Studio (SSMS)SQL Server Management Studio (SSMS)
  • Transact-SQL(从 SSMS、Visual Studio Code 或其他工具)Transact-SQL (from SSMS, Visual Studio Code, or another tool)
  • 命令行工具A command-line tool
  • PowerShell 或 C# 与 SSIS 管理对象模型PowerShell or C# and the SSIS management object model

此部署过程会对包进行验证,确保其可以在 Azure-SSIS Integration Runtime 上运行。The deployment process validates packages to ensure that they can run on the Azure-SSIS Integration Runtime. 有关详细信息,请参阅验证部署到 Azure 的 SQL Server Integration Services (SSIS) 包For more info, see Validate SQL Server Integration Services (SSIS) packages deployed to Azure.

有关使用 SSMS 和 Integration Services 部署向导的部署示例,请参阅教程:在 Azure 中部署和运行 SQL Server Integration Services (SSIS) 包For a deployment example that uses SSMS and the Integration Services Deployment Wizard, see Tutorial: Deploy and run a SQL Server Integration Services (SSIS) package in Azure.

版本支持Version support

可以将使用任意版本的 SSIS 创建的包部署到 Azure。You can deploy a package created with any version of SSIS to Azure. 将包部署到 Azure 时,如果没有出现验证错误,则包将自动升级到最新的包格式。When you deploy a package to Azure, if there are no validation errors, the package is automatically upgraded to the latest package format. 换言之,包始终会升级到最新版的 SSIS。In other words, it is always upgraded to the latest version of SSIS.

运行包Run packages

若要运行在 Azure 中部署的 SSIS 包,可以使用多种方法。To run SSIS packages deployed in Azure, you can use a variety of methods. 有关详细信息,请参阅运行部署在 Azure 中的 SQL Server Integration Services (SSIS) 包For more info, see Run SQL Server Integration Services (SSIS) packages deployed in Azure.

在 Azure 数据工厂管道中运行包Run packages in an Azure Data Factory pipeline

若要在 Azure 数据工厂管道中运行 SSIS 包,请使用“执行 SSIS 包活动”。To run an SSIS package in an Azure Data Factory pipeline, use the Execute SSIS Package Activity. 有关详细信息,请参阅在 Azure 数据工厂中使用“执行 SSIS 包”活动运行 SSIS 包For more info, see Run an SSIS package using the Execute SSIS Package Activity in Azure Data Factory.

在数据工厂管道中使用“执行 SSIS 包活动”运行包时,可以在运行时将值传递给包。When you run a package in a Data Factory pipeline with the Execute SSIS Package Activity, you can pass values to the package at runtime. 若要传递一个或多个运行时值,请在 SSISDB 中使用 SQL Server Management Studio (SSMS) 创建 SSIS 执行环境。To pass one or more runtime values, create SSIS execution environments in SSISDB with SQL Server Management Studio (SSMS). 在每个环境中,创建变量,并赋予与项目或包的参数相对应的值。In each environment, create variables and assign values that correspond to the parameters for your projects or packages. 在 SSMS 中配置 SSIS 包,将这些环境变量与项目或包参数关联起来。Configure your SSIS packages in SSMS to associate those environment variables with your project or package parameters. 在管道中运行包时,通过在“执行 SSIS 包”活动 UI 的“设置”选项卡上指定不同的环境路径,可在各环境之间切换。When you run the packages in the pipeline, switch between environments by specifying different environment paths on the Settings tab of the Execute SSIS Package activity UI. 有关 SSIS 环境的详细信息,请参阅创建和映射服务器环境For more info about SSIS environments, see Create and Map a Server Environment.

监视包Monitor packages

要监视运行的包,可在 SSMS 中使用下列报表选项。To monitor running packages, use the following reporting options in SSMS.

  • 右键单击“SSISDB”,然后选择“活动操作”以打开“活动操作”对话框。Right-click SSISDB, and then select Active Operations to open the Active Operations dialog box.
  • 在对象资源管理器中选择包,然后依次选择“报表”、“标准报表”、“所有执行”。Select a package in Object Explorer, right-click and select Reports, then Standard Reports, then All Executions.

要监视 Azure-SSIS Integration Runtime,请参阅监视 Azure-SSIS Integration RuntimeTo monitor the Azure-SSIS Integration Runtime, see Monitor the Azure-SSIS integration runtime.

计划包Schedule packages

若要计划安排 Azure 中部署的包的执行,可使用多种工具。To schedule the execution of packages deployed in Azure, you can use a variety of tools. 有关详细信息,请参阅计划 Azure 中部署的 SQL Server Integration Services (SSIS) 包的执行For more info, see Schedule the execution of SQL Server Integration Services (SSIS) packages deployed in Azure.

后续步骤Next steps

若要开始使用 Azure 上的 SSIS 工作负荷,请参阅以下文章:To get started with SSIS workloads on Azure, see the following articles: