aws rds监控慢sql_将AWS S3存储桶与AWS RDS SQL Server集成

最新推荐文章于 2021-01-31 22:58:54 发布

culuo4781

最新推荐文章于 2021-01-31 22:58:54 发布

阅读量436

点赞数

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

原文链接：https://www.sqlshack.com/integrating-aws-s3-buckets-with-aws-rds-sql-server/

版权

aws rds监控慢sql

This article gives you an overview of integrating AWS S3 buckets with AWS RDS SQL Server.

本文概述了如何将AWS S3存储桶与AWS RDS SQL Server集成在一起。

介绍 (Introduction)

AWS provides a fully managed relational database service (RDS) in the cloud. We do not require to manage the hardware, backups, patching, and focus on the application tasks. It provides the following database systems. It is secure, reliable, scalable, highly available, and low cost.

AWS在云中提供了完全托管的关系数据库服务（RDS）。我们不需要管理硬件，备份，修补和专注于应用程序任务。它提供以下数据库系统。它是安全，可靠，可扩展，高度可用且低成本的。

MySQL
MySQL
Microsoft SQL Server
Microsoft SQL服务器
Oracle
Oracle
MariaDB
玛丽亚数据库
Amazon Aurora
亚马逊极光
PostgreSQL
PostgreSQL

You can use this article AWS RDS SQL Server – Launching a new database instance to create a new Amazon RDS SQL instance.

您可以使用本文AWS RDS SQL Server –启动新的数据库实例来创建新的Amazon RDS SQL实例。

AWS also provides an object storage solution known as Simplified Storage Service. Suppose you need to import or export data from the RDS SQL Server. You can use the S3 bucket for this purpose. We can store any kind of objects such as text, CSV, XML in the S3 bucket. In this article, we will view the steps to integrate an AWS S3 bucket with RDS SQL Server.

AWS还提供了一种称为“简化存储服务”的对象存储解决方案。假设您需要从RDS SQL Server导入或导出数据。您可以将S3存储桶用于此目的。我们可以在S3存储桶中存储任何类型的对象，例如文本，CSV，XML。在本文中，我们将查看将AWS S3存储桶与RDS SQL Server集成的步骤。

We can download and upload the files from the S3 bucket to the RDS instance. RDS uses the D:\S3 folder for this purpose in the RDS instance. We will cover more on it later in this article.

我们可以将文件从S3存储桶下载并上传到RDS实例。为此，RDS在RDS实例中使用D：\ S3文件夹。我们将在本文后面详细介绍。

先决条件 (Prerequisites)

AWS RDS SQL Server – Launching a new database instance to create a new DB instance in RDS AWS RDS SQL Server –启动新的数据库实例以在RDS中创建新的数据库实例。
We cannot use RDS S3 integration in the Multi-AZ instances. You can follow the article Multi-AZ Configuration for AWS RDS SQL Server to understand the difference in single and Multi-AZ instance
我们不能在Multi-AZ实例中使用RDS S3集成。您可以按照文章适用于AWS RDS SQL Server的多可用区配置来了解单可用区和多可用区实例的区别

S3存储桶和IAM策略 (S3 bucket and IAM policy)

We need to remember that the S3 bucket and the RDS SQL instance should be in a region. For example, my RDS instance is in the us-east-1f region, so we cannot use an S3 bucket that does not belong to the RDS region.

我们需要记住，S3存储桶和RDS SQL实例应位于一个区域中。例如，我的RDS实例位于us-east-1f区域，因此我们不能使用不属于RDS区域的S3存储桶。

In the following image, we get a high-level overview of steps required to integrate an S3 bucket and AWS RDS SQL Server.

在下图中，我们概述了集成S3存储桶和AWS RDS SQL Server所需的步骤。

创建一个AWS S3存储桶 (Create an AWS S3 bucket)

Let’s create a new S3 bucket for this article. In the Services, go to S3 and click on Create Bucket.

让我们为本文创建一个新的S3存储桶。在服务中，转到S3，然后单击创建存储桶。

In this article, we create the bucket with default properties. Specify a bucket name (unique) and the region, as shown below.

在本文中，我们使用默认属性创建存储桶。指定存储桶名称（唯一）和区域，如下所示。

Click Ok, and it configures this SQLShackDemo with default settings.

单击“确定”，它将使用默认设置配置此SQLShackDemo。

We need to create IAM (Identity Access Management) policy to integrate S3 and RDS. Navigate to IAM in the Services and click on Create Policy.

我们需要创建IAM（身份访问管理）策略来集成S3和RDS。导航到服务中的IAM，然后单击“ 创建策略”。

In the create policy window, we can either use a Visual editor or specify a JSON(Javascript object notation). You can refer to an article on SQLShack to learn JSON language.

在创建策略窗口中，我们可以使用可视编辑器或指定JSON（Javascript对象表示法）。您可以参考SQLShack上的文章以学习JSON语言。

We have many permissions available for the AWS S3 bucket. We use the following permissions in this article.

我们有许多可用于AWS S3存储桶的权限。我们在本文中使用以下权限。

Access Level Category	Access Commands	Description
List	ListAllMyBuckets	It returns a list of S3 buckets for the authenticated AWS user
List	ListBucket	It returns a list of objects in an AWS S3 bucket
Read	GetBucketACL	It returns the access control list ( ACL) for a bucket
Read	Get-BucketLocation	It returns a bucket AWS region
Read	GetObject	It is required to retrieve objects from an S3 bucket. We use to download files from S3 to D:\S3 folder
Write	PutObject	It is required to upload a file from D:\S3 folder to S3 bucket

访问级别类别	访问命令	描述
清单	ListAllMyBuckets	它为经过身份验证的AWS用户返回S3存储桶的列表
清单	ListBucket	它返回AWS S3存储桶中的对象列表
读	GetBucketACL	它返回存储桶的访问控制列表（ACL）
读	获取桶位置	它返回一个存储桶AWS区域
读	获取对象	需要从S3存储桶中检索对象。我们用于将文件从S3下载到D：\ S3文件夹
写	PutObject	需要将文件从D：\ S3文件夹上传到S3存储桶

Now, go back to create a policy wizard and select the following parameters.

现在，返回以创建一个策略向导并选择以下参数。

Service: S3 服务：S3
Actions: here, we specify the actions required for the AWS S3 bucket using this policy:

操作：在这里，我们使用以下策略指定AWS S3存储桶所需的操作：

Similarly, select the other access levels defined in the above permissions table. We can give full permission for this bucket using the All S3 actions checkbox.

同样，选择上面的权限表中定义的其他访问级别。我们可以使用“ 所有S3操作”复选框为该存储桶授予完全权限。
Resources

资源资源

Here, we need to specify the Amazon Resource Name (ARN) for the S3 bucket. It is a unique name for the S3 object, and you get it from the S3 bucket properties. For this ARN, go to buckets, select the bucket and use the Copy ARN option:

在这里，我们需要为S3存储桶指定Amazon Resource Name（ARN）。它是S3对象的唯一名称，您可以从S3存储桶属性中获得它。对于此ARN，请转到存储桶，选择存储桶，然后使用“ 复制ARN”选项：

Click on ARN in the IAM policy and paste the ARN you copied earlier.

在IAM策略中单击ARN，然后粘贴您先前复制的ARN。

Once you add it, you see configurations, as shown below. We will skip the object parameter because it gives us the flexibility to restrict access to specific objects.

添加后，您将看到配置，如下所示。我们将跳过object参数，因为它使我们能够灵活地限制对特定对象的访问。

Click on the Review policy and specify a name for this policy.

单击查看策略，然后为此策略指定名称。

It creates the policy, and you can see it as a customer-managed policy.

它会创建策略，您可以将其视为客户管理的策略。

Click on this policy, and you can view the equivalent JSON statements. You can easily make changes in the JSON or copy it to create another policy.

单击此策略，您可以查看等效的JSON语句。您可以轻松地在JSON中进行更改或将其复制以创建另一个策略。

IAM角色以使用S3策略 (IAM Role to use the S3 policy)

In the next step, we define an IAM role that uses the IAM policy we defined for the S3 bucket. Navigate to Roles in the IAM console. You also get a brief description of IAM roles on the home page of Roles.

在下一步中，我们定义一个IAM角色，该角色使用为S3存储桶定义的IAM策略。在IAM控制台中导航到“ 角色 ”。您还可以在“角色”主页上获得IAM角色的简短描述。

Click on Create role. On this page, select the AWS service that uses the IAM role. We require an IAM role for the AWS RDS SQL Server.

单击创建角色 。在此页面上，选择使用IAM角色的AWS服务。我们需要AWS RDS SQL Server的IAM角色。

Select the user cases as RDS – Add Role to Database.

选择用户案例作为RDS –向数据库添加角色。

In the next step, search the S3 bucket policy that we created earlier.

在下一步中，搜索我们之前创建的S3存储桶策略。

Click Next. We need to give an appropriate name to this custom role on the review page.

点击下一步。我们需要在查看页面上为此自定义角色指定一个适当的名称。

Click on Create role, and it creates a user-defined role for you.

点击创建角色 ，它会为您创建一个用户定义的角色。

将IAM角色附加到AWS RDS SQL Server (Attach the IAM role with AWS RDS SQL Server)

We need to attach the IAM role with the SQL instance. This IAM role already has an IAM policy attached to it.

我们需要将IAM角色附加到SQL实例。该IAM角色已经附加了IAM策略。

Go to the RDS instance and navigate to Connectivity & Security.

转到RDS实例，然后导航到“ 连通性和安全性”。

Attach the IAM role with AWS RDS SQL Server

In this Connectivity & Security, scroll down and look for Manage IAM roles. The RDS instance should be in the available state to add an IAM role. If RDS is in the stopped state, it does not enable Add Role options in the Manage IAM roles.

在此连接性和安全性中，向下滚动并查找“管理IAM角色”。 RDS实例应处于可用状态以添加IAM角色。如果RDS处于停止状态，则它不会在“管理IAM”角色中启用“ 添加角色”选项。

Once it is available, find a suitable role from the drop-down and click on Add Role. It applies to the IAM role without restarting the SQL instance.

一旦可用，请从下拉菜单中找到合适的角色，然后单击“添加角色”。它适用于IAM角色，而无需重新启动SQL实例。

将S3与RDS SQL实例集成 (Use S3 integration with RDS SQL instance)

Once we have applied for the IAM role in the RDS instance, we can connect to the S3 bucket using the RDS SQL instance. RDS provides stored procedures to upload and download data from an S3 bucket. We need to use S3 ARN to access the S3 bucket and objects inside it. At a time, we can have two in-progress tasks in the queue. If we schedule any tasks, we should be careful that it should not override each other.

在RDS实例中申请IAM角色后，就可以使用RDS SQL实例连接到S3存储桶。 RDS提供了存储过程，可以从S3存储桶上载和下载数据。我们需要使用S3 ARN来访问S3存储桶和其中的对象。一次，我们可以在队列中有两个正在进行的任务。如果安排任何任务，则应注意不要将它们相互替代。

将文件从S3存储桶上传到RDS实例 (Upload file from S3 bucket to RDS instance)

As we highlighted earlier, the RDS instance D:\S3 folder to store the files you upload in an S3 bucket. It uses a stored procedure msdb.dbo.rds_download_from_s3 for this purpose.

如前所述，RDS实例D：\ S3文件夹用于将您上载的文件存储在S3存储桶中。为此，它使用存储过程msdb.dbo.rds_download_from_s3 。

First, open the S3 bucket and upload a file into it.

首先，打开S3存储桶并将文件上传到其中。

Now, connect to RDS SQL Instance in SSMS using the endpoint. You can get the endpoint from the instance property page. You should have set Public Accessibility property to true. If not, modify the instance appropriately and allow public access to the RDS instance.

现在，使用端点连接到SSMS中的RDS SQL实例。您可以从实例属性页面获取端点。您应该将Public Accessibility属性设置为true。如果不是，请适当修改实例并允许公众访问RDS实例。

In the SSMS, new query window, execute the following query. It uses the following arguments.

在“ SSMS，新查询”窗口中，执行以下查询。它使用以下参数。

S3 object ARN(@s3_arn_of_file): It is the object ARN path in the S3 bucket S3对象ARN（@ s3_arn_of_file）：这是S3存储桶中的对象ARN路径
RDS file path (@rds_file_path): It is an optional argument. We can specify a subfolder inside the D:\S3 directory. RDS automatically creates this folder for you. If we do not specify any folder, RDS stores files in the ‘D:\S3 folder RDS文件路径（@rds_file_path）：这是一个可选参数。我们可以在D：\ S3目录中指定一个子文件夹。 RDS会自动为您创建此文件夹。如果我们未指定任何文件夹，则RDS将文件存储在'D：\ S3文件夹中
Overwrite(@overwrite_file): We can override a file if it already exists using this argument. By default, it does not overwrite the file (@overwrite_file=0)

覆盖（@overwrite_file）：如果文件已经存在，我们可以使用此参数覆盖它。默认情况下，它不会覆盖文件（@ overwrite_file = 0）
```
exec msdb.dbo.rds_download_from_s3
      @s3_arn_of_file='arn:aws:s3:::sqlshackdemo/ABC.txt',
      @rds_file_path='D:\S3\SQLShackDemo\ABC.txt',
      @overwrite_file=1;
```
Now, check the task status using the stored procedure rds_task_status.

现在，使用存储过程rds_task_status检查任务状态。
```
EXEC msdb..rds_task_status @task_id = 2;
```
To verify the files in the D:\S3 directory, first, run the stored procedure exec msdb.dbo.rds_gather_file_details and get a task id for it. In the next step, we can either use the procedure rds_task_status or msdb.dbo.rds_fn_list_file_details to get file details.

要验证D：\ S3目录中的文件，首先，运行存储过程exec msdb.dbo.rds_gather_file_details并为其获取任务ID。在下一步中，我们可以使用过程rds_task_status或msdb.dbo.rds_fn_list_file_details获取文件详细信息。
Check file using the msdb..rds_task_status stored procedure

使用msdb..rds_task_status存储过程检查文件

It gives you a file list with its properties in the task_info column.

它在task_info列中为您提供一个文件列表及其属性。
```
EXEC msdb.dbo.rds_gather_file_details;
 
EXEC msdb..rds_task_status
     @task_id = 4;
```
msdb.dbo.rds_fn_list_file_details function. It gives output in a tabular format with the following columns: msdb.dbo.rds_fn_list_file_details函数检查文件。它以表格格式提供输出，其中包含以下列：
- Absolute file path
  绝对文件路径
- Size in bytes
  大小（以字节为单位）
- Last modified time in UTC
  UTC的上次修改时间
- An option that shows whether the item is a directory or not
  显示项目是否为目录的选项

In the below output, we can see two files in the SQLShackDemo folder. The first row indicates the root folder and does not have a file in it.

在下面的输出中，我们可以在SQLShackDemo文件夹中看到两个文件。第一行表示根文件夹，其中没有文件。

EXEC msdb.dbo.rds_gather_file_details;
 
SELECT * FROM msdb.dbo.rds_fn_list_file_details(4);

删除RDS实例上的文件 (Deleting Files on the RDS Instance)

We can delete the files from the RDS instance directory using the MSDB stored procedure msdb.dbo.rds_delete_from_filesystem.

我们可以使用MSDB存储过程msdb.dbo.rds_delete_from_filesystem从RDS实例目录中删除文件。

In the below procedure, we want to delete the ABC.txt file from the SQLShackDemo folder.

在下面的过程中，我们要从SQLShackDemo文件夹中删除ABC.txt文件。

EXEC msdb.dbo.rds_delete_from_filesystem
 
@rds_file_path = ‘D:\S3\SQLShackDemo\ABC.txt’;

We can also delete a subfolder created in the ‘D:\S3 directory. It also requires an additional argument @force_delete=1, and it tells RDS to delete the complete directory.

我们还可以删除在'D：\ S3目录中创建的子文件夹。它还需要一个附加参数@ force_delete = 1，并告诉RDS删除整个目录。

EXEC msdb.dbo.rds_delete_from_filesystem 
     @rds_file_path = 'D:\S3\SQLShackDemo\',
   @force_delete=1;

结论 (Conclusion)

In this article, we explored the process of integrating an AWS S3 bucket with AWS RDS SQL Server. You can use this process to upload and import data into the S3 bucket and RDS directory.

在本文中，我们探讨了将AWS S3存储桶与AWS RDS SQL Server集成的过程。您可以使用此过程将数据上传并导入到S3存储桶和RDS目录中。

翻译自: https://www.sqlshack.com/integrating-aws-s3-buckets-with-aws-rds-sql-server/

aws rds监控慢sql

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
aws rds监控慢sql_将AWS S3存储桶与AWS RDS SQL Server集成

aws rds监控慢sql This article gives you an overview of integrating AWS S3 buckets with AWS RDS SQL Server. 本文概述了如何将AWS S3存储桶与AWS RDS SQL Server集成在一起。介绍 (Introduction) AWS provides a fully man...
复制链接

扫一扫