SSIS 目录SSIS Catalog

适用范围: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

SSISDB 目录是使用已部署到 Integration ServicesIntegration Services 服务器的 Integration ServicesIntegration Services (SSIS) 项目的中心点 。The SSISDB catalog is the central point for working with Integration ServicesIntegration Services (SSIS) projects that you've deployed to the Integration ServicesIntegration Services server. 例如,您可以设置项目和包参数,配置环境以便为包指定运行时值,执行包并对包进行故障排除,以及管理 Integration ServicesIntegration Services 服务器操作。For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration ServicesIntegration Services server operations.

备注

本文介绍常规 SSIS 目录及在本地运行的 SSIS 目录。This article describes the SSIS Catalog in general, and the SSIS Catalog running on premises. 还可在 Azure SQL 数据库中创建 SSIS 目录,并在 Azure 中部署和运行 SSIS 包。You can also create the SSIS Catalog in Azure SQL Database, and deploy and run SSIS packages in Azure. 有关详细信息,请参阅将 SQL Server Integration Services 工作负荷直接迁移到云For more info, see Lift and shift SQL Server Integration Services workloads to the cloud.

虽然也可在 Linux 上运行 SSIS 包,但在 Linux 上不支持 SSIS 目录。Although you can also run SSIS packages on Linux, the SSIS Catalog is not supported on Linux. 有关详细信息,请参阅使用 SSIS 在 Linux 上提取、转换和加载数据For more info, see Extract, transform, and load data on Linux with SSIS.

SSISDB 目录中存储的对象包括项目、包、参数、环境和操作历史记录。The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.

可通过查询 “SSISDB” 数据库中的视图来检查 “SSISDB” 目录中存储的对象、设置和操作数据。You inspect objects, settings, and operational data that are stored in the SSISDB catalog, by querying the views in the SSISDB database. 可通过调用 SSISDB 数据库中的存储过程或通过使用 SSISDB 目录的 UI 来管理对象。You manage the objects by calling stored procedures in the SSISDB database or by using the UI of the SSISDB catalog. 在很多情况下,同一个任务既可使用 UI 执行,也可以通过调用存储过程来执行。In many cases, the same task can be performed in the UI or by calling a stored procedure.

要维护 SSISDB 数据库,建议您应用管理用户数据库的标准企业策略。To maintain the SSISDB database, it is recommended that you apply standard enterprise policies for managing user databases. 有关创建维护计划的信息,请参阅 Maintenance PlansFor information about creating maintenance plans, see Maintenance Plans.

SSISDB 目录和 SSISDB 数据库支持 Windows PowerShell。The SSISDB catalog and the SSISDB database support Windows PowerShell. 有关将 SQL Server 与 Windows PowerShell 一起使用的详细信息,请参阅 SQL Server PowerShellFor more information about using SQL Server with Windows PowerShell, see SQL Server PowerShell. 有关如何使用 Windows PowerShell 完成任务(如部署项目)的示例,请参阅 blogs.msdn.com 上的博客文章 SQL Server 2012 中的 SSIS 和 PowerShellFor examples of how to use Windows PowerShell to complete tasks such as deploying a project, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com.

有关如何查看操作数据的详细信息,请参阅 监视运行包和其他操作For more information about viewing operations data, see Monitor Running Package and Other Operations.

您可以通过以下方式访问 中的 “SSISDB” SQL Server Management StudioSQL Server Management Studio 目录:连接到 SQL ServerSQL Server 数据库引擎,然后展开对象资源管理器中的 “Integration Services 目录” 节点。You access the SSISDB catalog in SQL Server Management StudioSQL Server Management Studio by connecting to the SQL ServerSQL Server Database Engine and then expanding the Integration Services Catalogs node in Object Explorer. 可通过展开对象资源管理器中的“数据库”节点来访问 中的 SSISDB SQL Server Management StudioSQL Server Management Studio 数据库。You access the SSISDB database in SQL Server Management StudioSQL Server Management Studio by expanding the Databases node in Object Explorer.

备注

不能重命名 SSISDB 数据库。You cannot rename the SSISDB database.

备注

如果 SQL ServerSQL Server SSISDB 数据库附加到的 实例停止或不响应,则 ISServerExec.exe 进程结束。If the SQL ServerSQL Server instance that the SSISDB database is attached to, stops or does not respond, the ISServerExec.exe process ends. 向 Windows 事件日志写入一条消息。A message is written to a Windows Event log.

如果 SQL ServerSQL Server 资源在群集故障转移过程中进行故障转移,正在运行的包不会重启。If the SQL ServerSQL Server resources fail over as part of a cluster failover, the running packages do not restart. 您可以使用检查点来重新启动包。You can use checkpoints to restart packages. 有关详细信息,请参阅 通过使用检查点重新启动包For more information, see Restart Packages by Using Checkpoints.

特性和功能Features and capabilities

目录对象标识符Catalog Object Identifiers

在目录中创建新对象时,为该对象指定一个名称。When you create a new object in the catalog, assign a name to the object. 对象名称就是一个标识符。The object name is an identifier. SQL ServerSQL Server 定义了有关可在标识符中使用的字符的规则。defines rules for which characters can be used in an identifier. 以下对象的名称必须遵循标识符规则。Names for the following objects must follow identifier rules.

  • 文件夹Folder

  • 项目Project

  • 环境Environment

  • 参数Parameter

  • 环境变量Environment Variable

文件夹、项目和环境Folder, Project, Environment

重命名文件夹、项目或环境时,请考虑以下规则。Consider the following rules when renaming a folder, project, or environment.

  • 无效字符包括 ASCII/Unicode 字符 1 到 31、引号 (")、小于号 (<)、大于号 (>)、竖线 (|)、退格符 (\b)、null (\0) 和制表符 (\t)。Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).

  • 名称不得包含前导空格或尾随空格。The name might not contain leading or trailing spaces.

  • 首字母不得为 @,但后续字符可以是 @。@ is not allowed as the first character, but subsequent characters might use @.

  • 名称的长度必须大于 0 且小于或等于 128。The length of the name must be greater than 0 and less than or equal to 128.

参数Parameter

命名参数时,请考虑以下规则。Consider the following rules when naming a parameter.

  • 名称的第一个字符必须是在 Unicode 标准 2.0 中定义的字母,或者是下划线 ()。The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore ().

  • 后续字符可以是在 Unicode 标准 2.0 中定义的字母或数字,或是下划线 ()。Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore ().

环境变量Environment Variable

命名环境变量时,请考虑以下规则。Consider the following rules when naming an environment variable.

  • 无效字符包括 ASCII/Unicode 字符 1 到 31、引号 (")、小于号 (<)、大于号 (>)、竖线 (|)、退格符 (\b)、null (\0) 和制表符 (\t)。Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).

  • 名称不得包含前导空格或尾随空格。The name might not contain leading or trailing spaces.

  • 首字母不得为 @,但后续字符可以是 @。@ is not allowed as the first character, but subsequent characters might use @.

  • 名称的长度必须大于 0 且小于或等于 128。The length of the name must be greater than 0 and less than or equal to 128.

  • 名称的第一个字符必须是在 Unicode 标准 2.0 中定义的字母,或者是下划线 ()。The first character of the name must be a letter as defined in the Unicode Standard 2.0, or an underscore ().

  • 后续字符可以是在 Unicode 标准 2.0 中定义的字母或数字,或是下划线 ()。Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or an underscore ().

目录配置Catalog Configuration

通过调整目录属性来优化目录的行为方式。You fine-tune how the catalog behaves by adjusting the catalog properties. 目录属性定义如何对敏感数据进行加密,以及如何保留操作和项目版本控制数据。Catalog properties define how sensitive data is encrypted, and how operations and project versioning data is retained. 若要设置目录属性,请使用“目录属性” 对话框,或调用 catalog.configure_catalog(SSISDB 数据库)存储过程。To set catalog properties, use the Catalog Properties dialog box or call the catalog.configure_catalog (SSISDB Database) stored procedure. 若要查看属性,请使用对话框或查询 catalog.catalog_properties(SSISDB 数据库)To view the properties, use the dialog box or query catalog.catalog_properties (SSISDB Database). 可以通过右键单击对象资源管理器中的 SSISDB 来访问该对话框。You access the dialog box by right-clicking SSISDB in Object Explorer.

操作和项目版本清理Operations and Project Version Cleanup

目录中很多操作的状态数据都存储在内部数据库表中。Status data for many of the operations in the catalog is stored in internal database tables. 例如,目录会跟踪包执行和项目部署的状态。For example, the catalog tracks the status of package executions and project deployments. 为了维持操作数据的大小,使用 中的 “SSIS Server 维护作业” SQL Server Management StudioSQL Server Management Studio 来删除旧数据。To maintain the size of the operations data, the SSIS Server Maintenance Job in SQL Server Management StudioSQL Server Management Studio is used to remove old data. 在安装 SQL ServerSQL Server 时创建此 Integration ServicesIntegration Services 代理作业。This SQL ServerSQL Server Agent job is created when Integration ServicesIntegration Services is installed.

您可以使用相同名称将 Integration ServicesIntegration Services 项目部署到目录中的同一文件夹,以对其进行更新或重新部署。You can update or redeploy an Integration ServicesIntegration Services project by deploying it with the same name to the same folder in the catalog. 默认情况下,每次重新部署某个项目时, SSISDB 目录都会保留早期版本的该项目。By default, each time you redeploy a project, the SSISDB catalog retains the previous version of the project. 为了维持操作数据的大小,使用了 “SSIS 服务器维护作业” 来删除旧版本的项目。To maintain the size of the operations data, the SSIS Server Maintenance Job is used to remove old versions of projects.

为了运行“SSIS 服务器维护作业” ,SSIS 创建了 SQL Server 登录 ##MS_SSISServerCleanupJobLogin##To run the SSIS Server Maintenance Job, SSIS creates the SQL Server login ##MS_SSISServerCleanupJobLogin##. 此登录仅供 SSIS 进行内部使用。This login is only for internal use by SSIS.

以下 SSISDB 目录属性将定义此 SQL ServerSQL Server 代理作业的行为方式。The following SSISDB catalog properties define how this SQL ServerSQL Server Agent job behaves. 可以使用“目录属性” 对话框或使用 catalog.catalog_properties(SSISDB 数据库)catalog.configure_catalog(SSISDB 数据库)查看和修改属性。You can view and modify the properties by using the Catalog Properties dialog box or by using catalog.catalog_properties (SSISDB Database) and catalog.configure_catalog (SSISDB Database).

定期清理日志Clean Logs Periodically
当此属性设置为 True时,操作清除作业步骤将会运行。The job step for operations cleanup runs when this property is set to True.

保持期(天)Retention Period (days)
定义可允许的操作数据的最长保存时间(以天为单位)。Defines the maximum age of allowable operations data (in days). 将删除较旧的数据。Older data are removed.

最小值为一天。The minimum value is one day. 最大值仅受到 SQL ServerSQL Server int 数据的最大值的限制。The maximum value is limited only by the maximum value of the SQL ServerSQL Server int data. 有关此数据类型的信息,请参阅 int、bigint、smallint 和 tinyint (Transact-SQL)For information about this data type, see int, bigint, smallint, and tinyint (Transact-SQL).

定期删除旧版本Periodically Remove Old Versions
当此属性设置为 True时,项目版本清除作业步骤将会运行。The job step for project version cleanup runs when this property is set to True.

每个项目的最大版本数Maximum Number of Versions per Project
定义在目录中存储项目的多少个版本。Defines how many versions of a project are stored in the catalog. 将删除较旧版本的项目。Older versions of projects are removed.

加密算法Encryption Algorithm

“加密算法” 属性可指定用于对敏感参数值进行加密的加密类型。The Encryption Algorithm property specifies the type of encryption that is used to encrypt sensitive parameter values. 可以从下列加密类型中选择。You can choose from the following types of encryption.

  • AES_256(默认值)AES_256 (default)

  • AES_192AES_192

  • AES_128AES_128

  • DESXDESX

  • TRIPLE_DES_3KEYTRIPLE_DES_3KEY

  • TRIPLE_DESTRIPLE_DES

  • DESDES

在向 Integration ServicesIntegration Services 服务器部署 Integration ServicesIntegration Services 项目时,该目录会自动对包数据和敏感值加密。When you deploy an Integration ServicesIntegration Services project to the Integration ServicesIntegration Services server, the catalog automatically encrypts the package data and sensitive values. 该目录还会在检索数据时自动解密数据。The catalog also automatically decrypts the data when you retrieve it. SSISDB 目录使用 ServerStorage 保护级别。The SSISDB catalog uses the ServerStorage protection level. 有关详细信息,请参阅 Access Control for Sensitive Data in PackagesFor more information, see Access Control for Sensitive Data in Packages.

更改加密算法是一项很耗时的操作。Changing the encryption algorithm is a time-intensive operation. 首先,服务器必须使用以前指定的算法来解密所有配置值。First, the server has to use the previously specified algorithm to decrypt all configuration values. 然后,服务器必须使用新算法来重新对这些值进行加密。Then, the server has to use the new algorithm to re-encrypt the values. 此时,在服务器上不能有其他 Integration ServicesIntegration Services 操作。During this time, there cannot be other Integration ServicesIntegration Services operations on the server. 因此,为使 Integration ServicesIntegration Services 操作继续运行而不会中断,加密算法在 Management StudioManagement Studio的对话框中是只读值。Thus, to enable Integration ServicesIntegration Services operations to continue uninterrupted, the encryption algorithm is a read-only value in the dialog box in Management StudioManagement Studio.

若要更改 “加密算法” 属性设置,请将 “SSISDB” 数据库设置为单用户模式,然后调用 catalog.configure_catalog 存储过程。To change the Encryption Algorithm property setting, set the SSISDB database to the single-user mode and then call the catalog.configure_catalog stored procedure. 将 ENCRYPTION_ALGORITHM 用于 property_name 参数。Use ENCRYPTION_ALGORITHM for the property_name argument. 有关支持的属性值,请参阅 catalog.catalog_properties(SSISDB 数据库)For the supported property values, see catalog.catalog_properties (SSISDB Database). 有关该存储过程的详细信息,请参阅 catalog.configure_catalog(SSISDB 数据库)For more information about the stored procedure, see catalog.configure_catalog (SSISDB Database).

有关单用户模式的详细信息,请参阅将数据库设置为单用户模式For more information about single-user mode, see Set a Database to Single-user Mode. 有关 SQL ServerSQL Server中加密和加密算法的信息,请参阅 SQL Server 加密一节中的有关主题。For information about encryption and encryption algorithms in SQL ServerSQL Server, see the topics in the section, SQL Server Encryption.

数据库主密钥用于加密。A database master key is used for the encryption. 创建目录时会创建此密钥。The key is created when you create the catalog.

下表列出了 “目录属性” 对话框中显示的属性名称和数据库视图中对应的属性。The following table lists the property names shown in the Catalog Properties dialog box and the corresponding properties in the database view.

属性名称( “目录属性” 对话框)Property Name (Catalog Properties dialog box) 属性名称(数据库视图)Property Name (database view)
加密算法名称Encryption Algorithm Name ENCRYPTION_ALGORITHMENCRYPTION_ALGORITHM
定期清理日志Clean Logs Periodically OPERATION_CLEANUP_ENABLED​OPERATION_CLEANUP_ENABLED​
保持期(天)Retention Period (days) RETENTION_WINDOWRETENTION_WINDOW
定期删除旧版本Periodically Remove Old Versions VERSION_CLEANUP_ENABLEDVERSION_CLEANUP_ENABLED
每个项目的最大版本数Maximum Number of Versions per Project MAX_PROJECT_VERSIONSMAX_PROJECT_VERSIONS
服务器范围的默认日志记录级别Server-wide Default Logging Level SERVER_LOGGING_LEVELSERVER_LOGGING_LEVEL

权限Permissions

文件夹中包含的项目、环境和包是安全对象。Projects, environments, and packages are contained in folders that are securable objects. 您可以授予对文件夹的权限,包括 MANAGE_OBJECT_PERMISSIONS 权限。You can grant permissions to a folder, including the MANAGE_OBJECT_PERMISSIONS permission. 利用 MANAGE_OBJECT_PERMISSIONS,您可以将文件夹内容的管理委托给用户,而无需为 ssis_admin 角色授予用户成员身份。MANAGE_OBJECT_PERMISSIONS enables you to delegate the administration of folder contents to a user without having to grant the user membership to the ssis_admin role. 您还可以授予对项目、环境和操作的权限。You can also grant permissions to projects, environments, and operations. 操作包括初始化 Integration ServicesIntegration Services、部署项目、创建和启动执行、验证项目和包以及配置 SSISDB 目录。Operations include initializing Integration ServicesIntegration Services, deploying projects, creating and starting executions, validating projects and packages, and configuring the SSISDB catalog.

有关数据库角色的详细信息,请参阅 数据库级别的角色For more information about database roles, see Database-Level Roles.

SSISDB 目录使用 DDL 触发器 (ddl_cleanup_object_permissions) 强制 SSIS 安全对象的权限信息的完整性。The SSISDB catalog uses a DDL trigger, ddl_cleanup_object_permissions, to enforce the integrity of permissions information for SSIS securables. 当从 SSISDB 数据库中删除数据库主体(如数据库用户、数据库角色或数据库应用程序角色)时,将会触发触发器。The trigger fires when a database principal, such as a database user, database role, or a database application role, is removed from the SSISDB database.

如果该主体已对其他主体授予或拒绝权限,则应先撤消授权者授予的权限,然后才能删除主体。If the principal has granted or denied permissions to other principals, revoke the permissions given by the grantor, before the principal can be removed. 否则,系统尝试删除主体时会返回一条错误消息。Otherwise, an error message is returned when the system tries to remove the principal. 触发器将删除数据库主体作为被授权者的所有权限记录。The trigger removes all permission records where the database principal is a grantee.

建议不要禁用触发器,因为它可确保在从 SSISDB 数据库中删除数据库主体之后,不会出现孤立的权限记录。It is recommended that the trigger is not disabled because it ensures that are no orphaned permission records after a database principal is dropped from the SSISDB database.

管理权限Managing Permissions

可以使用 SQL Server Management StudioSQL Server Management Studio 用户界面、存储过程以及 Microsoft.SqlServer.Management.IntegrationServices 命名空间来管理权限。You can manage permissions by using the SQL Server Management StudioSQL Server Management Studio UI, stored procedures, and the Microsoft.SqlServer.Management.IntegrationServices namespace.

若要使用 SQL Server Management StudioSQL Server Management Studio UI 管理权限,请使用以下对话框:To manage permissions using the SQL Server Management StudioSQL Server Management Studio UI, use the following dialog boxes:

若要使用 Transact-SQL 管理权限,请调用 catalog.grant_permission(SSISDB 数据库)catalog.deny_permission(SSISDB 数据库)catalog.revoke_permission(SSISDB 数据库)To manage permissions using Transact-SQL, call catalog.grant_permission (SSISDB Database), catalog.deny_permission (SSISDB Database), and catalog.revoke_permission (SSISDB Database). 若要查看所有对象的当前主体的有效权限,请查询 catalog.effective_object_permissions(SSISDB 数据库)To view effective permissions for the current principal for all objects, query catalog.effective_object_permissions (SSISDB Database). 本主题描述了不同类型的权限。This topic provides descriptions of the different types of permissions. 若要查看已显式分配给用户的权限,请查询 catalog.explicit_object_permissions(SSISDB 数据库)To view permissions that have been explicitly assigned to the user, query catalog.explicit_object_permissions (SSISDB Database).

文件夹Folders

文件夹包含 SSISDB 目录中的一个或多个项目和环境。A folder contains one or more projects and environments in the SSISDB catalog. 可以使用 catalog.folders(SSISDB 数据库) 视图来访问有关目录中的文件夹的信息。You can use the catalog.folders (SSISDB Database) view to access information about folders in the catalog. 可使用以下存储过程管理文件夹:You can use the following stored procedures to manage folders:

项目和包Projects and Packages

每个项目可以包含多个包。Each project can contain multiple packages. 项目和包都可以包含参数和对环境的引用。Both projects and packages can contain parameters and references to environments. 您可以使用 Configure Dialog Box访问参数和环境引用。You can access the parameters and environment references by using the Configure Dialog Box.

可通过调用以下存储过程来执行其他项目任务:You can carry out other project tasks by calling the following stored procedures:

以下视图提供了有关包、项目和项目版本的详细信息。These views provide details about packages, projects, and project versions.

参数Parameters

您可以使用参数在包执行时为包属性赋值。You use parameters to assign values to package properties at the time of package execution. 若要设置包或项目参数的值和清除这些值,请调用 catalog.set_object_parameter_value(SSISDB 数据库)catalog.clear_object_parameter_value(SSISDB 数据库)To set the value of a package or project parameter and to clear the value, call catalog.set_object_parameter_value (SSISDB Database) and catalog.clear_object_parameter_value (SSISDB Database). 若要为执行实例设置参数的值,请调用 catalog.set_execution_parameter_value(SSISDB 数据库)To set the value of a parameter for an instance of execution, call catalog.set_execution_parameter_value (SSISDB Database). 可以通过调用 catalog.get_parameter_values(SSISDB 数据库)来检索默认参数值。You can retrieve default parameter values by calling catalog.get_parameter_values (SSISDB Database).

以下视图显示了所有包和项目的参数,以及用于执行实例的参数值。These views show the parameters for all packages and projects, and parameter values that are used for an instance of execution.

服务器环境、服务器变量和服务器环境引用Server Environments, Server Variables, and Server Environment References

服务器环境包含服务器变量。Server environments contain server variables. 当在 Integration ServicesIntegration Services 服务器上执行或验证包时,可以使用这些变量值。The variable values can be used when a package is executed or validated on the Integration ServicesIntegration Services server.

利用以下存储过程,您可以为环境和变量执行很多其他管理任务。The following stored procedures enable you to perform many other management tasks for environments and variables.

通过调用 catalog.set_environment_variable_protection(SSISDB 数据库)存储过程,可以设置变量的敏感性位。By calling the catalog.set_environment_variable_protection (SSISDB Database) stored procedure, you can set the sensitivity bit for a variable.

若要使用服务器变量的值,请指定项目和服务器环境之间的引用。To use the value of a server variable, specify the reference between the project and the server environment. 您可以使用以下存储过程创建和删除引用。You can use the following stored procedures to create and delete references. 还可以指示环境是可以位于与项目相同的文件夹中,还是位于其他文件夹中。You can also indicate whether the environment can be located in the same folder as the project or in a different folder.

有关环境和变量的详细信息,请查询以下视图。For more details about environments and variables, query these views.

执行和验证Executions and Validations

执行就是一个包执行实例。An execution is an instance of a package execution. 调用 catalog.create_execution(SSISDB 数据库)catalog.start_execution(SSISDB 数据库)可创建并启动执行。Call catalog.create_execution (SSISDB Database) and catalog.start_execution (SSISDB Database) to create and start an execution. 若要停止执行或停止包/项目验证,请调用 catalog.stop_operation(SSISDB 数据库)To stop an execution or a package/project validation, call catalog.stop_operation (SSISDB Database).

若要暂停正在运行的包和创建转储文件,请调用 catalog.create_execution_dump 存储过程。To cause a running package to pause and create a dump file, call the catalog.create_execution_dump stored procedure. 转储文件提供了有关包执行的信息,可帮助您解决执行问题。A dump file provides information about the execution of a package that can help you troubleshoot execution issues. 有关生成和配置转储文件的详细信息,请参阅 Generating Dump Files for Package ExecutionFor more information about generating and configuring dump files, see Generating Dump Files for Package Execution.

有关执行、验证、操作期间记录的消息以及与错误有关的上下文信息的详细信息,请查询以下视图。For details about executions, validations, messages that are logged during operations, and contextual information related to errors, query these views.

可以通过调用 catalog.validate_project(SSISDB 数据库)catalog.validate_package(SSISDB 数据库)存储过程验证项目和包。You can validate projects and packages by calling the catalog.validate_project (SSISDB Database) and catalog.validate_package (SSISDB Database) stored procedures. catalog.validations(SSISDB 数据库)视图提供了有关验证的详细信息,例如,验证中要考虑的服务器环境引用、验证是依赖项验证还是完整验证以及使用 32 位运行时还是 64 位运行时来运行包。The catalog.validations (SSISDB Database) view provides details about validations such as the server environment references that are considered in the validation, whether it is a dependency validation or a full validation, and whether the 32-bit runtime or the 64-bit runtime is used to run the package.

创建 SSIS 目录Create the SSIS Catalog

当您在 SQL Server Data ToolsSQL Server Data Tools中设计和测试包后,可将包含包的项目部署到 Integration ServicesIntegration Services 服务器。After you design and test packages in SQL Server Data ToolsSQL Server Data Tools, you can deploy the projects that contain the packages to an Integration ServicesIntegration Services server. 在可以将项目部署到 Integration ServicesIntegration Services 服务器之前,该服务器必须包含 SSISDB 目录。Before you can deploy the projects to the Integration ServicesIntegration Services server, the server must contain the SSISDB catalog. SQL Server 2012 (11.x)SQL Server 2012 (11.x) 的安装程序并不会自动创建目录;您需要使用以下说明手动创建目录。The installation program for SQL Server 2012 (11.x)SQL Server 2012 (11.x) does not automatically create the catalog; you need to manually create the catalog by using the following instructions.

您可以在 SQL Server Management StudioSQL Server Management Studio中创建 SSISDB 目录。You can create the SSISDB catalog in SQL Server Management StudioSQL Server Management Studio. 还可以使用 Windows PowerShell 以编程方式创建目录。You also create the catalog programmatically by using Windows PowerShell.

在 SQL Server Management Studio 中创建 SSISDBTo create the SSISDB catalog in SQL Server Management Studio

  1. 打开 SQL Server Management StudioSQL Server Management StudioOpen SQL Server Management StudioSQL Server Management Studio.

  2. 连接到 SQL ServerSQL Server 数据库引擎。Connect to the SQL ServerSQL Server Database Engine.

  3. 在“对象资源管理器”中,展开服务器节点,右键单击“Integration Services 目录” 节点,然后单击“创建目录” 。In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  4. 单击 “启用 CLR 集成”Click Enable CLR Integration.

    该目录使用 CLR 存储过程。The catalog uses CLR stored procedures.

  5. 单击“在 SQL Server 启动时启用自动执行 Integration Services 存储过程” ,使 catalog.startup 存储过程在每次重启 SSISSSIS 服务器后运行。Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSISSSIS server instance is restarted.

    该存储过程对 SSISDB 目录的操作状态进行维护。The stored procedure performs maintenance of the state of operations for the SSISDB catalog. 它可以修复在 SSISSSIS 服务器实例出现故障时运行的任何包的状态。It fixes the status of any packages there were running if the SSISSSIS server instance goes down.

  6. 输入密码,然后单击 “确定”Enter a password, and then click Ok.

    该密码保护用于对目录数据进行加密的数据库主密钥。The password protects the database master key that is used for encrypting the catalog data. 将该密码保存在安全的位置。Save the password in a secure location. 同时建议您也备份数据库主密钥。It is recommended that you also back up the database master key. 有关详细信息,请参阅 Back Up a Database Master KeyFor more information, see Back Up a Database Master Key.

以编程方式创建 SSISDB 目录To create the SSISDB catalog programmatically

  1. 执行以下 PowerShell 脚本:Execute the following PowerShell script:

    # Load the IntegrationServices Assembly  
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
    
    # Store the IntegrationServices Assembly namespace to avoid typing it every time  
    $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  
    
    Write-Host "Connecting to server ..."  
    
    # Create a connection to the server  
    $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  
    
    # Create the Integration Services object  
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  
    
    # Provision a new SSIS Catalog  
    $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
    $catalog.Create()  
    
    

    有关如何使用 Windows PowerShell 和 Microsoft.SqlServer.Management.IntegrationServices 命名空间的更多示例,请参阅 blogs.msdn.com 上的博客文章 SQL Server 2012 中的 SSIS 和 PowerShellFor more examples of how to use Windows PowerShell and the Microsoft.SqlServer.Management.IntegrationServices namespace, see the blog entry, SSIS and PowerShell in SQL Server 2012, on blogs.msdn.com. 有关命名空间和代码示例的概述,请参阅 blogs.msdn.com 上的博客文章 SSIS 目录托管对象模型一瞥For an overview of the namespace and code examples, see the blog entry, A Glimpse of the SSIS Catalog Managed Object Model, on blogs.msdn.com.

“目录属性”对话框Catalog Properties Dialog Box

使用“目录属性”对话框可以配置 SSISDB 目录。Use the Catalog Properties dialog box to configure the SSISDB catalog. 目录属性定义如何对敏感数据进行加密、如何保留操作和项目版本控制数据以及验证操作何时超时。SSISDB 目录是用于 Integration ServicesIntegration Services 项目、包、参数和环境的中心存储区和管理点。Catalog properties define how sensitive data is encrypted, how operations and project versioning data is retained, and when validation operations time out. The SSISDB catalog is a central storage and administration point for Integration ServicesIntegration Services projects, packages, parameters, and environments.

还可以在 catalog.catalog_properties 视图中查看目录属性,并使用 catalog.configure_catalog 存储过程设置属性。You can also view catalog properties in the catalog.catalog_properties view, and set the properties by using the catalog.configure_catalog stored procedure. 有关详细信息,请参阅 catalog.catalog_properties(SSISDB 数据库)catalog.configure_catalog(SSISDB 数据库)For more information, see catalog.catalog_properties (SSISDB Database) and catalog.configure_catalog (SSISDB Database).

您希望做什么?What do you want to do?

打开“目录属性”对话框Open the Catalog Properties Dialog Box

  1. 打开 SQL ServerSQL ServerManagement StudioManagement StudioOpen SQL ServerSQL ServerManagement StudioManagement Studio.

  2. 连接到 Microsoft SQL Server 数据库引擎。Connect Microsoft SQL Server Database Engine.

  3. 在对象资源管理器中,展开“Integration Services” 节点,右键单击“SSISDB” ,然后单击“属性” 。In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Properties.

配置选项Configure the Options

选项Options

下表描述对话框中的某些属性以及 catalog.catalog_properties 视图中的相应属性。The following table describes certain properties in the dialog box and the corresponding properties in the catalog.catalog_properties view.

属性名称(“目录属性”对话框)Property Name (Catalog Properties dialog box) 属性名称(catalog.catalog_properties 视图)Property Name (catalog.catalog_properties view) 描述Description
加密算法名称Encryption Algorithm Name ENCRYPTION_ALGORITHMENCRYPTION_ALGORITHM 指定用于对于目录中的敏感参数值进行加密的加密类型。Specifies the type of encryption that is used to encrypt the sensitive parameter values in the catalog. 下面是可能的值:The following are the possible values:

DESDES

TRIPLE_DESTRIPLE_DES

TRIPLE_DES_3KEYTRIPLE_DES_3KEY

DESPXDESPX

AES_128AES_128

AES_192AES_192

AES_256(默认值)AES_256 (default)
每个项目的最大版本数Maximum Number of Versions per Project MAX_PROJECT_VERSIONSMAX_PROJECT_VERSIONS 指定可以在目录中存储项目的多少个版本。Specify how many versions of a project are stored in the catalog. 当项目版本清理作业运行时,如果旧版项目数超过此上限,就会遭到删除。Older versions of projects that exceed the maximum are removed when the project version cleanup job runs.
定期清理日志Clean Logs Periodically OPERATION_CLEANUP_ENABLEDOPERATION_CLEANUP_ENABLED 将该属性设置为 True 可指示 SQL Server 代理作业“操作清除”运行。Set the property to True to indicate that the SQL Server Agent job, operations cleanup, runs. 否则,将该属性设置为 False。Otherwise, set the property to False.
保持期(天)Retention Period (days) RETENTION_WINDOWRETENTION_WINDOW 指定可允许操作数据的最长时间(天)。Specify the maximum age of allowable operations data (in days). 指定天数前的数据由 SQL 代理作业“操作清理”删除。Data that is older than the specified number of days are removed by the SQL Agent job, operations cleanup.

备份、还原和移动 SSIS 目录Back up, Restore, and Move the SSIS Catalog

适用对象:是SQL Server 否Azure SQL 数据库 否Azure SQL 数据仓库 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 2017 Integration Services (SSIS)SQL Server 2017 Integration Services (SSIS) 包含 SSISDB 数据库。includes the SSISDB database. 查询 SSISDB 数据库中的视图可以检查 SSISDB 目录中存储的对象、设置和操作数据。You query views in the SSISDB database to inspect objects, settings, and operational data that are stored in the SSISDB catalog. 本主题说明如何备份和还原该数据库。This topic provides instructions for backing up and restoring the database.

SSISDB 目录存储部署到 Integration ServicesIntegration Services 服务器的包 。The SSISDB catalog stores the packages that you've deployed to the Integration ServicesIntegration Services server. 有关该目录的详细信息,请参阅 SSIS 目录For more information about the catalog, see SSIS Catalog.

备份 SSIS 数据库To Back up the SSIS Database

  1. 打开 SQL Server Management StudioSQL Server Management Studio 并连接到 SQL ServerSQL Server实例。Open SQL Server Management StudioSQL Server Management Studio and connect to an instance of SQL ServerSQL Server.

  2. 使用 BACKUP MASTER KEY Transact-SQL 语句备份 SSISDB 数据库的主密钥。Back up the master key for the SSISDB database, by using the BACKUP MASTER KEY Transact-SQL statement. 该密钥存储在您指定的文件中。The key is stored in a file that you specify. 使用密码加密该文件中的主密钥。Use a password to encrypt the master key in the file.

    有关语句的详细信息,请参阅 BACKUP MASTER KEY (Transact-SQL)For more information about the statement, see BACKUP MASTER KEY (Transact-SQL).

    在下面的示例中,将主密钥导出到 c:\temp directory\RCTestInstKey 文件。In the following example, the master key is exported to the c:\temp directory\RCTestInstKey file. 使用 LS2Setup! 密码加密主密钥。The LS2Setup! password is used to encrypt the master key.

    backup master key to file = 'c:\temp\RCTestInstKey'  
           encryption by password = 'LS2Setup!'  
    
    
  3. 中使用 “备份数据库” SQL Server Management StudioSQL Server Management Studio对话框备份 SSISDB 数据库。Back up the SSISDB database by using the Backup Database dialog box in SQL Server Management StudioSQL Server Management Studio. 有关详细信息,请参阅操作说明:备份数据库 (SQL Server Management Studio)For more information, see How to: Back Up a Database (SQL Server Management Studio).

  4. 通过执行以下操作,生成 ##MS_SSISServerCleanupJobLogin## 的 CREATE LOGIN 脚本。Generate the CREATE LOGIN script for ##MS_SSISServerCleanupJobLogin##, by doing the following things. 有关详细信息,请参阅 CREATE LOGIN (Transact-SQL)For more information, see CREATE LOGIN (Transact-SQL).

    1. SQL Server Management StudioSQL Server Management Studio的对象资源管理器中,展开 “安全性” 节点,然后展开 “登录名” 节点。In Object Explorer in SQL Server Management StudioSQL Server Management Studio, expand the Security node and then expand the Logins node.

    2. 右键单击 ##MS_SSISServerCleanupJobLogin## ,然后依次单击“编写登录脚本为” > “CREATE 到” > “新查询编辑器窗口”。 Right-click ##MS_SSISServerCleanupJobLogin##, and then click Script Login as > CREATE To > New Query Editor Window.

  5. 若要将 SSISDB 数据库还原到从未创建 SSISDB 目录的 SQL ServerSQL Server 实例,请执行以下操作,生成 sp_ssis_startup 的 CREATE PROCEDURE 脚本。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate the CREATE PROCEDURE script for sp_ssis_startup, by doing the following things. 有关详细信息,请参阅 CREATE PROCEDURE (Transact-SQL)For more information, see CREATE PROCEDURE (Transact-SQL).

    1. 在对象资源管理器中,展开“数据库”节点,然后展开“主” > “可编程性” > “存储过程”节点。 In Object Explorer, expand the Databases node and then expand the master > Programmability > Stored Procedures node.

    2. 右键单击“dbo.sp_ssis_startup” ,再依次单击“将存储过程脚本编写为” > “CREATE,并编写到” > “新查询编辑器窗口” 。Right-click dbo.sp_ssis_startup, and then click Script Stored Procedure as > CREATE To > New Query Editor Window.

  6. 确认 SQL Server 代理已启动Confirm that SQL Server Agent has been started

  7. 若要将 SSISDB 数据库还原到从未创建 SSISDB 目录的 SQL ServerSQL Server 实例,请执行以下操作,生成 SSIS 服务器维护作业的脚本。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate a script for the SSIS Server Maintenance Job by doing the following things. 创建 SSISDB 目录时自动在 SQL ServerSQL Server 代理中创建该脚本。The script is created in SQL ServerSQL Server Agent automatically when the SSISDB catalog is created. 该作业帮助清除保留期窗口之外的操作日志并删除较旧版本的项目。The job helps clean up cleanup operation logs outside the retention window and remove older versions of projects.

    1. 在对象资源管理器中,展开 “SQL Server 代理” 节点,然后展开 “作业” 节点。In Object Explorer, expand the SQL Server Agent node and then expand the Jobs node.

    2. 右键单击“SSIS 服务器维护作业”,再依次单击“将存储过程脚本编写为” > “CREATE,并编写到” > “新查询编辑器窗口” 。Right-click SSIS Server Maintenance Job, and then click Script Job as > CREATE To > New Query Editor Window.

还原 SSIS 数据库To Restore the SSIS Database

  1. 如果要将 SSISDB 数据库还原到从不创建 SSISDB 目录的 SQL ServerSQL Server 实例,请通过运行 sp_configure 存储过程来启用公共语言运行时 (clr)。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) by running the sp_configure stored procedure. 有关详细信息,请参阅 sp_configure (Transact-SQL)clr enabled 选项For more information, see sp_configure (Transact-SQL) and clr enabled Option.

    use master   
           sp_configure 'clr enabled', 1  
           reconfigure  
    
    
  2. 如果要将 SSISDB 数据库还原到从不创建 SSISDB 目录的 SQL ServerSQL Server 实例,请创建非对称密钥和对应非对称密钥的登录名并将 UNSAFE 权限授予该登录名。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, create the asymmetric key and the login from the asymmetric key, and grant UNSAFE permission to the login.

    Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey  
           FROM Executable File = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'  
    
    

    Integration ServicesIntegration Services CLR 存储过程要求将 UNSAFE 权限授予该登录名,因为该登录名需要对受限制资源(如 Microsoft Win32 API)的更高访问权限。CLR stored procedures require UNSAFE permissions to be granted to the login because the login requires additional access to restricted resources, such as the Microsoft Win32 API. 有关 UNSAFE 代码权限的详细信息,请参阅 Creating an AssemblyFor more information about the UNSAFE code permission, see Creating an Assembly.

    Create Login MS_SQLEnableSystemAssemblyLoadingUser  
           FROM Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey   
    
           Grant unsafe Assembly to MS_SQLEnableSystemAssemblyLoadingUser  
    
    
  3. 中使用 “还原数据库” SQL Server Management StudioSQL Server Management Studio对话框从备份中还原 SSISDB 数据库。Restore the SSISDB database from the backup by using the Restore Database dialog box in SQL Server Management StudioSQL Server Management Studio. 有关详细信息,请参阅以下主题:For more information, see the following topics:

  4. 执行你在 备份 SSIS 数据库 中为 ##MS_SSISServerCleanupJobLogin##、sp_ssis_startup 和 SSIS 服务器维护作业创建的脚本。Execute the scripts that you created in the To Back up the SSIS Database for ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup, and SSIS Server Maintenance Job. 确认 SQL Server 代理已启动。Confirm that SQL Server Agent has been started.

  5. 运行以下语句,将 sp_ssis_startup 过程设置为自动执行。Run the following statement to set the sp_ssis_startup procedure for autoexecution. 有关详细信息,请参阅 sp_procoption (Transact-SQL)For more information, see sp_procoption (Transact-SQL).

    EXEC sp_procoption N'sp_ssis_startup','startup','on'  
    
  6. 通过在 SQL Server Management StudioSQL Server Management Studio 中使用“登录属性”对话框,将 SSISDB 用户 ##MS_SSISServerCleanupJobUser##(SSISDB 数据库)映射到 ##MS_SSISServerCleanupJobLogin##。 Map the SSISDB user ##MS_SSISServerCleanupJobUser## (SSISDB database) to ##MS_SSISServerCleanupJobLogin##, by using the Login Properties dialog box in SQL Server Management StudioSQL Server Management Studio.

  7. 使用下列方法之一还原主密钥。Restore the master key by using one of the following methods. 有关加密的详细信息,请参阅 Encryption HierarchyFor more information about encryption, see Encryption Hierarchy.

    • 方法 1Method 1

      如果已备份数据库主密钥且具有用于加密主密钥的密码,则使用此方法。Use this method if you've already performed a backup of the database master key, and you have the password used to encrypt the master key.

             Restore master key from file = 'c:\temp\RCTestInstKey'  
             Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'  
             Encryption by password = 'LS3Setup!' -- 'New Password'  
             Force  
      
      

      备注

      确认 SQL ServerSQL Server 服务帐户有权读取备份密钥文件。Confirm that the SQL ServerSQL Server service account has permissions to read the backup key file.

      备注

      如果服务主密钥尚未加密数据库主密钥,SQL Server Management StudioSQL Server Management Studio 中便会显示以下警告消息。You see the following warning message displayed in SQL Server Management StudioSQL Server Management Studio if the database master key has not yet been encrypted by the service master key. 忽略警告消息。Ignore the warning message.

      无法解密当前主密钥。此错误已被忽略,因为指定了 FORCE 选项。The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

      FORCE 参数指定即使当前数据库主密钥未打开,也应继续执行还原过程。The FORCE argument specifies that the restore process should continue even if the current database master key is not open. 对于 SSISDB 目录,由于要在其中还原数据库的实例尚未启用数据库主密钥,因此会看到这条消息。For the SSISDB catalog, because the database master key has not been opened on the instance where you are restoring the database, you see this message.

    • 方法 2Method 2

      如果您具有用于创建 SSISDB 的原始密码,则使用此方法。Use this method if you have the original password that was used to create SSISDB.

      open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
             Alter Master Key Add encryption by Service Master Key  
      
  8. 通过运行 Integration ServicesIntegration Services catalog.check_schema_version ,确定 SSISDB 目录架构与二进制文件(ISServerExec 和 SQLCLR 程序集)是否兼容。Determine whether the SSISDB catalog schema and the Integration ServicesIntegration Services binaries (ISServerExec and SQLCLR assembly) are compatible, by running catalog.check_schema_version.

  9. 若要确认 SSISDB 数据库已成功还原,请针对 SSISDB 目录执行操作,如运行部署到 Integration ServicesIntegration Services 服务器的包。To confirm that the SSISDB database has been restored successfully, perform operations against the SSISDB catalog such as running packages that have been deployed to the Integration ServicesIntegration Services server. 有关详细信息,请参阅运行 Integration Services (SSIS) 包For more information, see Run Integration Services (SSIS) Packages.

移动 SSIS 数据库To Move the SSIS Database

  • 按移动用户数据库的说明操作。Follow the instructions for moving user databases. 有关详细信息,请参阅 Move User DatabasesFor more information, see Move User Databases.

    确保您备份 SSISDB 数据库的主密钥并保护备份文件。Ensure that you back up the master key for the SSISDB database and protect the backup file. 有关详细信息,请参阅 备份 SSIS 数据库For more information, see To Back up the SSIS Database.

    确保在尚未创建 SSISDB 目录的新 SQL ServerSQL Server 实例中创建 Integration Services (SSIS) 相关对象。Ensure that the Integration Services (SSIS) relevant objects are created in the new SQL ServerSQL Server instance where the SSISDB catalog has not yet been created.

升级 SSIS 目录 (SSISDB)Upgrade the SSIS Catalog (SSISDB)

当数据库早于 SQL Server 实例当前版本时,运行 SSISDB 升级向导来升级 SSIS 目录数据库 SSISDB。Run the SSISDB Upgrade Wizard to upgrade the SSIS Catalog database, SSISDB, when the database is older than the current version of the SQL Server instance. 如果符合下列任一条件,表明可能是旧数据库。The database may be older when one of the following conditions is true.

  • 从旧版 SQL Server 还原数据库。You restored the database from an older version of SQL Server.

  • 在升级 SQL Server 实例之前,未从 AlwaysOn 可用性组中删除数据库。You did not remove the database from an Always On Availability Group before upgrading the SQL Server instance. 此条件可以防止数据库自动升级。This condition prevents the automatic upgrade of the database. 有关详细信息,请参阅 Upgrading SSISDB in an availability groupFor more info, see Upgrading SSISDB in an availability group.

该向导只能升级本地服务器实例上的数据库。The wizard can only upgrade the database on a local server instance.

通过运行 SSISDB 升级向导升级 SSIS 目录 (SSISDB)Upgrade the SSIS Catalog (SSISDB) by running the SSISDB Upgrade Wizard

  1. 备份 SSIS 目录数据库 (SSISDB)。Back up the SSIS Catalog database, SSISDB.

  2. SQL Server Management StudioSQL Server Management Studio中,展开本地服务器,然后展开“Integration Services 目录” 。In SQL Server Management StudioSQL Server Management Studio, expand the local server, and then expand Integration Services Catalogs.

  3. 右键单击“SSISDB” ,然后选择“数据库升级” 以启动 SSISDB 升级向导。Right-click on SSISDB, and then select Database Upgrade to launch the SSISDB Upgrade Wizard. 或者通过使用提升的权限在本地服务器上运行 C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe 来启动 SSISDB 升级向导。Or launch the SSISDB Upgrade Wizard by running C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe with elevated permissions on the local server.

    启动 SSISDB 升级向导

  4. 在“选择实例” 页上,选择本地服务器上的 SQL Server 实例。On the Select Instance page, select a SQL Server instance on the local server.

    重要

    该向导只能升级本地服务器实例上的数据库。The wizard can only upgrade the database on a local server instance.

    选中复选框以指示,你已在运行此向导之前备份 SSISDB 数据库。Select the checkbox to indicate that you have backed up the SSISDB database before running the wizard.

    在 SSISDB 升级向导中选择服务器Select the server in the SSISDB Upgrade Wizard

  5. 选择“升级” 以升级 SSIS 目录数据库。Select Upgrade to upgrade the SSIS Catalog database.

  6. 在“结果” 页上,查看结果。On the Result page, review the results.

    在 SSISDB 升级向导中查看结果Review the results in the SSISDB Upgrade Wizard

对 SSIS 目录 (SSISDB) 使用 Always OnAlways On for SSIS Catalog (SSISDB)

AlwaysOn 可用性组功能是一个高可用性和灾难恢复解决方案,可以提供替代数据库镜像的企业级方案。The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. 可用性组针对一组离散的用户数据库(称为可用性数据库,它们共同实现故障转移)支持故障转移环境。An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. 有关详细信息,请参阅 AlwaysOn 可用性组For more information, see Always On Availability Groups.

为了对 SSIS 目录 (SSISDB) 及其内容(项目、包、执行日志等)提供高可用性,可以将 SSISDB 数据库(与其他任何用户数据库相同)添加到 AlwaysOn 可用性组。In order to provide the high-availability for the SSIS catalog (SSISDB) and its contents (projects, packages, execution logs, etc.), you can add the SSISDB database (just the same as any other user database) to an Always On Availability Group. 当发生故障转移时,其中一个辅助节点将自动成为新的主节点。When a failover occurs, one of the secondary nodes automatically becomes the new primary node.

重要

发生故障转移时,正在运行的包不会重启或恢复。When a failover occurs, packages that were running do not restart or resume.

本节内容:In this section:

  1. 先决条件Prerequisites

  2. 为 AlwaysOn 配置 SSIS 支持Configure SSIS support for Always On

  3. 在可用性组中升级 SSISDBUpgrading SSISDB in an availability group

先决条件Prerequisites

为 SSISDB 数据库启用 Always On 支持前,请先执行以下先决性步骤。Do the following prerequisite steps before enabling Always On support for the SSISDB database.

  1. 设置 Windows 故障转移群集。Set up a Windows failover cluster. 请参阅 安装适用于 Windows Server 2012 的故障转移群集功能和工具 的博客文章以获取相关说明。See Installing the Failover Cluster Feature and Tools for Windows Server 2012 blog post for instructions. 在所有群集节点上安装功能和工具。Install the feature and tools on all cluster nodes.

  2. 在群集的每个节点上安装具有 Integration Services (SSIS) 功能的 SQL Server 2016。Install SQL Server 2016 with Integration Services (SSIS) feature on each node of the cluster.

  3. 为每个 SQL Server 实例启用 Always On 可用性组。Enable Always On Availability Groups for each SQL Server instance. 有关详细信息,请参阅 启用 AlwaysOn 可用性组See Enable Always On Availability Groups for details.

为 AlwaysOn 配置 SSIS 支持Configure SSIS support for Always On

重要

  • 必须在可用性组的 主节点 上执行这些步骤。You must perform these steps on the primary node of the availability group.
  • 将 SSISDB 添加到 Always On 可用性组后 ,必须为 Always On 启用 SSIS 支持 。You must enable SSIS support for Always On after you add SSISDB to an Always On Availability Group.

备注

若要详细了解此过程,请参阅数据平台 MVP Marcos Freccia 发布的以下演练及附加屏幕截图:将 SSISDB 添加到 SQL Server 2016 的 AGFor more info about this procedure, see the following walkthrough with additional screen shots by Data Platform MVP Marcos Freccia: Adding SSISDB to AG for SQL Server 2016.

步骤 1:创建 Integration Services 目录Step 1: Create Integration Services Catalog

  1. 启动 SQL Server Management Studio 并连接到你想要设置为适用于 SSISDB 的 AlwaysOn 高可用性组的 主节点 的群集中的 SQL Server 实例。Launch SQL Server Management Studio and connect to a SQL Server instance in the cluster that you want to set as the primary node of Always On high availability group for SSISDB.

  2. 在“对象资源管理器”中,展开服务器节点,右键单击“Integration Services 目录” 节点,然后单击“创建目录” 。In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.

  3. 单击 “启用 CLR 集成”Click Enable CLR Integration. 该目录使用 CLR 存储过程。The catalog uses CLR stored procedures.

  4. 单击“在 SQL Server 启动时启用自动执行 Integration Services 存储过程” ,使 catalog.startup 存储过程在每次重启 SSIS 服务器后运行。Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted. 该存储过程对 SSISDB 目录的操作状态进行维护。The stored procedure performs maintenance of the state of operations for the SSISDB catalog. 它可以修复当 SSIS 服务器实例出现故障时正在运行的任何包的状态。It fixes the status of any packages there were running if and when the SSIS server instance goes down.

  5. 输入 密码,然后单击“确定” 。Enter a password, and then click Ok. 该密码保护用于对目录数据进行加密的数据库主密钥。The password protects the database master key that is used for encrypting the catalog data. 将该密码保存在安全的位置。Save the password in a secure location. 同时建议您也备份数据库主密钥。It is recommended that you also back up the database master key. 有关详细信息,请参阅 Back Up a Database Master KeyFor more information, see Back Up a Database Master Key.

步骤 2:将 SSISDB 添加到 AlwaysOn 可用性组Step 2: Add SSISDB to an Always On Availability Group

将 SSISDB 数据库添加到 AlwaysOn 可用性组的方法与将任何其他用户数据库添加到可用性组的方法几乎相同。Adding the SSISDB database to an Always On Availability Group is almost same as adding any other user database into an availability group. 请参阅 使用可用性组向导See Use the Availability Group Wizard.

提供在“新建可用性组” 向导的“选择数据库” 页中创建 SSIS 目录时指定的密码。Provide the password that you specified while creating the SSIS Catalog in the Select Databases page of the New Availability Group wizard.

新建可用性组New Availability Group

步骤 3:为 AlwaysOn 启用 SSIS 支持Step 3: Enable SSIS support for Always On

创建 Integration Service 目录后,右键单击“Integration Service 目录” 节点,再单击“启用 Always On 支持” 。After you create the Integration Service Catalog, right-click the Integration Service Catalogs node, and click Enable Always On Support. 应该能看到以下“为 AlwaysOn 启用支持” 的对话框。You should see the following Enable Support for Always On dialog box. 如果此菜单被禁用,请确认已安装所有必备组件,然后单击“刷新” 。If this menu item is disabled, confirm that you have all the prerequisites installed and click Refresh.

为 Always On 启用支持

警告

为 AlwaysOn 启用 SSIS 支持之前,不支持 SSISDB 数据库的自动故障转移。Auto-failover of SSISDB database is not supported until you enable SSIS Support for Always On.

表中列出了从 Always On 可用性组新添加的次要副本。The newly added secondary replicas from the Always On availability group are shown in the table. 单击列表中每个副本的“连接…”按钮,然后输入身份验证凭据以连接到副本 。Click Connect... button for each replica in the list and enter authentication credentials to connect to the replica. 用户帐户必须是每个副本上 sysadmin 组的成员,才能为 Always On 启用 SSIS 支持。The user account must be a member of sysadmin group on each replica to enable SSIS support for Always On. 成功连接到每个副本后,单击“确定” 按钮以启用对 AlwaysOn 的 SSIS 支持。After you successfully connect to each replica, click OK to enable SSIS support for Always On.

如果在完成其他先决性步骤后,关联菜单中的“启用 Always On 支持” 选项显示为已禁用,请尝试执行以下操作:If the Enable Always On support option on the context menu appears to be disabled after you've completed the other prerequisites, try these things:

  1. 单击“刷新” 选项,刷新关联菜单。Refresh the context menu by clicking the Refresh option.
  2. 确保连接到主节点。Make sure you are connecting to the primary node. 必须在主节点上启用 Always On 支持。You have to enable Always On support on the primary node.
  3. 确保 SQL Server 版本为 13.0 或更高版本。Make sure the SQL Server version is 13.0 or higher. 仅在 SQL Server 2016 及更高版本上,SSIS 才支持 Always On。SSIS supports Always On only on SQL Server 2016 and later versions.

在可用性组中升级 SSISDBUpgrading SSISDB in an availability group

如果正在从以前的版本升级 SQL Server,并且 SSISDB 位于 AlwaysOn 可用性组中,则“AlwaysOn 可用性组中的 SSISDB 检查”规则可能会阻止你的升级。If you're upgrading SQL Server from a previous version, and SSISDB is in an Always On availability group, your upgrade may be blocked by the "SSISDB in Always On Availability Group check" rule. 出现这种阻止是因为升级是以单用户模式运行的,而可用性数据库必须是多用户数据库。This blocking occurs because upgrade runs in single-user mode, while an availability database must be a multi-user database. 因此,在升级或修补过程中,所有可用性数据库(包括 SSISDB)均将处于脱机状态,不会进行升级或修补。Therefore, during upgrade or patching, all availability databases including SSISDB are taken offline and are not upgraded or patched. 为了让升级继续进行下去,应先从可用性组中删除 SSISDB,升级或修补每一节点,再将 SSISDB 添加回可用性组中。To let upgrade continue, first remove SSISDB from the availability group, then upgrade or patch each node, then add SSISDB back to the availability group.

如果被“Always On 可用性组中的 SSISDB 检查”规则阻止,请按照以下步骤操作,升级 SQL Server。If you are blocked by the "SSISDB in Always On Availability Group check" rule, follow these steps to upgrade SQL Server.

  1. 从可用性组中删除 SSISDB 数据库。Remove the SSISDB database from the availability group. 有关详细信息,请参阅将辅助数据库从可用性组删除 (SQL Server)将主数据库从可用性组删除 (SQL Server)For more info, see Remove a Secondary Database from an Availability Group (SQL Server) and Remove a Primary Database from an Availability Group (SQL Server).

  2. 在升级向导中,单击“重新运行” 。Click Rerun in the upgrade wizard. 此时,将会通过“Always On 可用性组中的 SSISDB 检查”规则。The "SSISDB in Always On Availability Group check" rule passes.

  3. 单击“下一步” 以继续升级。Click the Next to continue the upgrade.

  4. 升级所有节点后,将 SSISDB 数据库添加回 AlwaysOn 可用性组中。After you have upgraded all the nodes, add the SSISDB database back to the Always On availability group. 有关详细信息,请参阅将数据库添加到可用性组 (SQL Server)For more info, see Add a Database to an Availability Group (SQL Server).

如果升级 SQL Server 时没有受到阻止,并且 SSISDB 位于 Always On 可用性组中,请在升级 SQL Server 数据库引擎后单独升级 SSISDB。If you're not blocked when you upgrade SQL Server, and SSISDB is in an Always On availability group, upgrade SSISDB separately after you upgrade the SQL Server database engine. 按照以下过程中所述,使用 SSIS 升级向导升级 SSISDB。Use the SSIS Upgrade Wizard to upgrade the SSISDB as described in the following procedure.

  1. 将 SSISDB 数据库移出可用性组,或者如果 SSISDB 是可用性组中唯一的数据库,则删除可用性组。Move the SSISDB database out of the availability group, or delete the availability group if SSISDB is the only database in the availability group. 在可用性组的主节点 上,启动 SQL Server Management Studio 来执行此任务。Launch SQL Server Management Studio on the primary node of the availability group to perform this task.

  2. 从所有 副本节点删除 SSISDB 数据库。Remove the SSISDB database from all replica nodes.

  3. 主节点上升级 SSISDB 数据库。Upgrade the SSISDB database on the primary node. 在 SQL Server Management Studio 内的“对象资源管理器” 中,展开“Integration Services 目录” ,右键单击“SSISDB” ,然后选择“数据库升级” 。InObject Explorer in SQL Server Management Studio, expand Integration Services Catalogs, right-click SSISDB, and then select Database Upgrade. 按照“SSISDB 升级向导” 中的说明来升级数据库。Follow the instructions in the SSISDB Upgrade Wizard to upgrade the database. 在主节点 上,本地启动 SSIDB 升级向导 。Launch the SSIDB Upgrade Wizard locally on the primary node.

  4. 按照步骤 2:将 SSISDB 添加到 AlwaysOn 可用性组中的说明将 SSISDB 添加回可用性组。Follow the instructions in Step 2: Add SSISDB to an Always On Availability Group to add the SSISDB back to an availability group.

  5. 按照步骤 3:为 AlwaysOn 启用 SSIS 支持中的说明进行操作。Follow the instructions in Step 3: Enable SSIS support for Always On.

相关内容Related Content