aws rds监控慢sql_探索AWS RDS SQL Server上SQL Server集成服务（SSIS）

最新推荐文章于 2022-06-22 17:35:56 发布

culuo4781

最新推荐文章于 2022-06-22 17:35:56 发布

阅读量1.1k

点赞数

文章标签：数据库 python java linux 大数据

原文链接：https://www.sqlshack.com/explore-sql-server-integration-services-ssis-on-aws-rds-sql-server/

版权

aws rds监控慢sql

In the previous article, Deploy tabular databases in SSAS on AWS RDS SQL Server, we explored that AWS RDS SQL Server now supports SQL Server Analysis Service (SSAS). It enables you to implement tabular project models for RDS instance as well. In this article, we will explore the support of SQL Server integration service in the RDS environment.

在上一篇文章“ 在AWS RDS SQL Server上的SSAS中部署表格数据库”中，我们探讨了AWS RDS SQL Server现在支持SQL Server Analysis Service（SSAS）。它也使您能够为RDS实例实现表格项目模型。在本文中，我们将探讨RDS环境中SQL Server集成服务的支持。

The SSIS integration for AWS RDS SQL Server will be covered in two articles:

两篇文章将介绍适用于AWS RDS SQL Server的SSIS集成：

- Overview of SSIS package on RDS instance
  RDS实例上的SSIS包概述
- Prerequisites
  先决条件
- Parameter group and Options group for SSIS feature
  SSIS功能的参数组和选项组
- SSIS DB permissions for master user and Windows authenticated user
  主用户和Windows身份验证用户的SSIS DB权限
- Create an integration service project in Visual Studio 2019
  在Visual Studio 2019中创建集成服务项目
- Deploy projects in SSIS RDS instance using S3 integration and stored procedures
  使用S3集成和存储过程在SSIS RDS实例中部署项目
- Create credentials, proxy accounts for SQL Server agent
  创建凭据，SQL Server代理的代理帐户
- Create SQL Server agent jobs for SSIS package stored in the SSISDB catalog
  为存储在SSISDB目录中的SSIS包创建SQL Server代理作业

Let’s get started with the SSIS feature in the AWS RDS SQL Server.

让我们开始使用AWS RDS SQL Server中的SSIS功能。

介绍 (Introduction)

Amazon gives you two options to implement SQL Server services:

Amazon为您提供了两种选择来实现SQL Server服务：

Elastic Compute Cloud (EC2): We can deploy an EC2 instance and install SQL Server on it. An EC2 instance is similar to a virtual machine that we deploy in on-premise infrastructure. You have full control over the SQL Server, its services, version, edition, features in this 弹性计算云（EC2）：我们可以部署EC2实例并在其上安装SQL Server。 EC2实例类似于我们在内部部署基础架构中部署的虚拟机。您可以在此完全控制SQL Server，其服务，版本，版本，功能。
AWS RDS SQL Server: It is an AWS managed database platform for SQL Server. Here, the user does not worry about the infrastructure. They can select the supported SQL version and start using it. RDS instance comes with some feature restrictions AWS RDS SQL Server：它是用于SQL Server的AWS托管数据库平台。在这里，用户不必担心基础架构。他们可以选择支持SQL版本并开始使用它。 RDS实例具有一些功能限制

Previously, RDS supported only database service in SQL Server. Recently on May 19^th, 2020, Amazon announced that now RDS supports the SSIS package using SQL Server Integration Service in the AWS RDS SQL Server as well.

以前，RDS在SQL Server中仅支持数据库服务。最近在5月19 ^日， 2020年，亚马逊宣布现在RDS在AWS RDS SQL Server中也使用SQL Server Integration Service支持SSIS包。

In this article, we will explore the process to enable SSIS, create an SSIS package, and deploy it in the RDS instance.

在本文中，我们将探讨启用SSIS，创建SSIS包并将其部署在RDS实例中的过程。

AWS RDS SQL Server上的SSIS概述 (Overview of SSIS on AWS RDS SQL Server)

SQL Server Integration Service (SSIS) is an integral and useful component of SQL Server. It helps us to design various data flows and transformations. It provides many useful transformations for cleaning, aggregating, merging data, FTP, and database maintenance tasks. We can also take data from various sources such as a flat-file, relational, non-relational databases, and design work flow, logics to extract or merge them.

SQL Server集成服务（SSIS）是SQL Server不可或缺的有用组件。它帮助我们设计各种数据流和转换。它为清理，聚合，合并数据，FTP和数据库维护任务提供了许多有用的转换。我们还可以从各种来源获取数据，例如平面文件，关系，非关系数据库，设计工作流程，提取或合并它们的逻辑。

Let’s explore SSIS feature requirements for an AWS RDS SQL instance.

让我们研究AWS RDS SQL实例的SSIS功能要求。

SSIS功能可用性 (SSIS feature availability)

SSIS feature is available on both Single-AZ and Multi-AZ SQL Server configurations.

SAZ功能在单可用区和多可用区SQL Server配置上均可用。

Note: You can refer article Multi-AZ Configuration for AWS RDS SQL Server to understand Single and Multi-AZ RDS SQL Server.
注意：您可以参考文章AWS RDS SQL Server的多可用区配置以了解单可用区和多可用区RDS SQL Server。

We will use a single AZ RDS instance in this article. You can follow the above article and configure it to use multi-AZ RDS with SSIS functionality.

在本文中，我们将使用单个AZ RDS实例。您可以按照上面的文章进行配置并将其配置为使用具有SSIS功能的多可用区RDS。

支持SQL版本 (Supported SQL editions and versions)

It supports SQL Server 2016 and SQL Server 2017 standard or Enterprise edition.

它支持SQL Server 2016和SQL Server 2017标准版或企业版。

SQL Server 2016: Minimum version supported is 13.00.5426.0.v1 SQL Server 2016 ：支持的最低版本为13.00.5426.0.v1
SQL Server 2017: Minimum version supported is14.00.3223.3.v1 SQL Server 2017 ：支持的最低版本为14.00.3223.3.v1

In this article, I use the AWS RDS SQL Server instance SQL Server 2017 version 14.00.3281.6.v1 standard edition, as shown below.

在本文中，我使用AWS RDS SQL Server实例SQL Server 2017版本14.00.3281.6.v1标准版，如下所示。

AWS托管Microsoft活动目录 (AWS Managed Microsoft active directory)

The RDS instance should be a member of the AWS managed active directory. It allows you to connect to SQL Server with Windows authentication.

RDS实例应该是AWS托管活动目录的成员。它允许您通过Windows身份验证连接到SQL Server。

We already covered this required in the following two series of articles:

我们已经在以下两个系列文章中介绍了此要求：

In the following screenshot, we see SQLShackdemo.com active directory in joined status for my RDS instance:

在以下屏幕截图中，我们看到RDS实例的SQLShackdemo.com活动目录处于连接状态：

适用于AWS RDS SQL Server的Amazon S3存储桶集成 (Amazon S3 bucket Integration for AWS RDS SQL Server)

We need to integrate an Amazon S3 bucket with the RDS instance with an appropriate IAM role. RDS uses an S3 bucket to download and upload project-related files for the SSIS package.

我们需要使用适当的IAM角色将Amazon S3存储桶与RDS实例集成。 RDS使用S3存储桶为SSIS包下载和上传与项目相关的文件。

You should refer to Integrating an AWS RDS SQL Server with Amazon S3 bucket to learn the integration process.

您应参阅将AWS RDS SQL Server与Amazon S3存储桶集成以了解集成过程。

In the below screenshot, I have sqlshackrole in my RDS instance that is integrated with [sqlshackdemo] S3 bucket:

在下面的截图，我有sqlshackrole在与[sqlshackdemo]集成RDS我S3如斗：

AWS RDS SQL Server上的CLR集成 (CLR integration on AWS RDS SQL Server)

Enables Common Language Runtime(CLR) integration on the corresponding RDS instance. It allows RDS instance to use stored procedures, function, triggers written in any .Net language such as VB.net

在相应的RDS实例上启用公共语言运行时（CLR）集成。它允许RDS实例使用以任何.Net语言（例如VB.net）编写的存储过程，函数，触发器

To enable CLR integration on RDS SQL Server, first view the existing parameter group for RDS. The RDS instance should be in an available status to modify it. Click on modify and view its parameter group.

要在RDS SQL Server上启用CLR集成，请首先查看RDS的现有参数组。 RDS实例应处于可用状态以对其进行修改。单击修改并查看其参数组。

In my case, it is using the default parameter group i.e. default.sqlserver-se-14.0

就我而言，它使用的是默认参数组，即default.sqlserver-se-14.0

We cannot modify a default parameter group. We create a new parameter group with additional configuration. In the RDS dashboard, click on the Parameter groups taband create a new parameter group:

我们无法修改默认参数组。我们使用其他配置创建一个新的参数组。在RDS仪表板中 ，单击“ 参数组”选项卡并创建一个新的参数组：

In the Create parameter group window, select the SQL Server version, specify a group name and description. You should choose the correct DB instance version similar to your RDS instance version else it might work correctly with associated AWS RDS server.

在“ 创建参数组”窗口中，选择SQL Server版本，指定组名和描述。您应该选择与RDS实例版本相似的正确数据库实例版本，否则它可能会与关联的AWS RDS服务器一起正常工作。

Once you create it, select the new parameter group, open it, and click on edit parameters. Search for CLR enabled, change the value to 1, and save changes:

创建它之后，选择新的参数组，将其打开，然后单击编辑参数。搜索启用的CLR，将值更改为1，然后保存更改：

Now, go back to RDS instance and modify the option group from a default default.sqlserver-se-14.0 to new parameter group newparametergroup:

现在，返回到RDS实例，并将选项组从默认default.sqlserver-se-14.0修改为新参数组newparametergroup ：

Verify the changes and apply them immediately. In case you do not want to apply it immediately, select to apply the changes during the next scheduled maintenance window. It also shows you the current maintenance window:

验证更改并立即应用它们。如果您不想立即应用它，请选择在下一个计划的维护时段内应用更改。它还显示了当前的维护窗口：

AWS RDS SQL Server中的选项组以启用SSIS功能 (Option group in AWS RDS SQL Server to enable SSIS feature)

Enable SSIS using the options group. In my RDS instance, it uses ssasforrds option group:

使用选项组启用SSIS。在我的RDS实例中，它使用ssasforrds选项组：

AWS RDS SQL Server to enable SSIS feature

Navigate to the Option groups tab in the RDS dashboard, select the option group ssasforrds and click on the Add option button:

导航到RDS仪表板中的“ 选项组”选项卡，选择选项组ssasforrds ，然后单击“添加选项”按钮：

Select the SSIS option from the drop-down list and apply the changes immediately:

从下拉列表中选择SSIS选项，并立即应用更改：

It will add SSIS to the existing option group:

它将SSIS添加到现有选项组：

We do not need to change the option group in the RDS instance because we modified the existing option group. In case you create a new option group, modify the RDS instance to utilize the new option group having an SSIS feature.

我们不需要更改RDS实例中的选项组，因为我们修改了现有的选项组。如果创建新的选项组，请修改RDS实例以利用具有SSIS功能的新选项组。

Now, connect to AWS RDS SQL Server using endpoint in SSMS. You can see a new database SSISDB is available in the Databases node:

现在，使用SSMS中的终端节点连接到AWS RDS SQL Server。您可以在“数据库”节点中看到一个新的数据库SSISDB：

You can expand the Integration Service Catalog node, and it will show the SSISDB catalog after we enabled the SSIS feature in the option group:

您可以展开Integration Service Catalog节点，并且在选项组中启用SSIS功能后，它将显示SSISDB目录：

主用户和Windows身份验证用户的SSIS DB权限 (SSIS DB permissions for master and Windows authenticated user)

As you know, RDS creates a master user once we create an AWS RDS instance. We cannot use the master user to execute the SSIS package in the RDS environment. The master user has the following permissions in the SSISDB:

如您所知，一旦我们创建了AWS RDS实例，RDS就会创建一个主用户。我们不能使用主用户在RDS环境中执行SSIS包。主用户在SSISDB中具有以下权限：

alter on ssis_admin role
更改ssis_admin角色
alter on ssis_logreader role
更改ssis_logreader角色
alter any user
更改任何用户

This master user can provide the SSIS_ADMIN, SSIS_LOGREADER permissions to other users. We need permissions for Windows users so that Windows authentication can be used.

该主用户可以向其他用户提供SSIS_ADMIN，SSIS_LOGREADER权限。我们需要Windows用户的权限，以便可以使用Windows身份验证。

Expand the Security node in the Object Explorer panel, then the Logins node, and open the properties of the login for a Windows user. Click on the User Mapping tab, select the SSISDB database, and give SSIS_ADMIN, SSIS_LOGREADER permissions for the user:

展开“对象资源管理器”面板中的“安全性”节点，然后展开“登录名”节点，然后为Windows用户打开登录名的属性。单击“用户映射”选项卡，选择SSISDB数据库，并为用户授予SSIS_ADMIN，SSIS_LOGREADER权限：

We also need few additional permissions for the Windows user to run the SSIS package with the SQL Server agent job. In the below query, we provided these permissions to the user sqlshackdemo\rajendra.gupta. You can replace it with your windows user and execute the script. This script should run from the admin user security context in SSMS:

对于Windows用户，我们还需要很少的其他权限才能通过SQL Server代理作业运行SSIS包。在下面的查询中，我们向用户sqlshackdemo \ rajendra.gupta提供了这些权限。您可以将其替换为Windows用户并执行脚本。该脚本应从SSMS中的管理员用户安全上下文运行：

USE [msdb];
GO
CREATE USER [sqlshackdemo\rajendra.gupta] FOR LOGIN [sqlshackdemo\rajendra.gupta];
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [sqlshackdemo\rajendra.gupta] WITH GRANT OPTION;
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [sqlshackdemo\rajendra.gupta];
GO
USE [master];
GO
GRANT ALTER ANY CREDENTIAL TO [sqlshackdemo\rajendra.gupta];
GO

结论 (Conclusion)

In the first article on the SSIS feature in AWS RDS SQL Server, we enabled SSIS services and provided permissions to the Windows user to connect using Windows authentication. In the second article, we will create an SSIS package, schedule it to run through SQL Server agent jobs.

在有关AWS RDS SQL Server中SSIS功能的第一篇文章中，我们启用了SSIS服务，并为Windows用户提供了使用Windows身份验证进行连接的权限。在第二篇文章中，我们将创建一个SSIS包，安排它在SQL Server代理作业中运行。

翻译自: https://www.sqlshack.com/explore-sql-server-integration-services-ssis-on-aws-rds-sql-server/

aws rds监控慢sql

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
aws rds监控慢sql_探索AWS RDS SQL Server上SQL Server集成服务（SSIS）

aws rds监控慢sql In the previous article, Deploy tabular databases in SSAS on AWS RDS SQL Server, we explored that AWS RDS SQL Server now supports SQL Server Analysis Service (SSAS). It enables...
复制链接

扫一扫