aws rds监控慢sql_在AWS中为SQL Server配置RDS环境

最新推荐文章于 2024-03-01 16:49:41 发布

culuo4781

最新推荐文章于 2024-03-01 16:49:41 发布

阅读量564

点赞数

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

原文链接：https://www.sqlshack.com/configuring-rds-environment-in-aws-for-sql-server/

版权

aws rds监控慢sql

In this article, I’m going to explain how to configure an RDS Environment in Amazon Web Services (AWS) for SQL Server. Amazon RDS, also known as the Amazon Relational Database Service, is a Database-as-a-Service offered by Amazon. This enables us to create an instance of a relational database in RDS including many databases like SQL Server, MySQL, PostgreSQL etc. The entire hosting is managed by AWS, so there is no worry to maintain any on-premises data center or infrastructure from the customer’s end.

在本文中，我将解释如何在Amazon Web Services（ AWS ）中为SQL Server 配置RDS环境 。 Amazon RDS，也称为Amazon Relational Database Service，是Amazon提供的数据库即服务。这使我们能够在RDS中创建一个关系数据库的实例，其中包括许多数据库，例如SQL Server，MySQL，PostgreSQL等。整个托管由AWS管理，因此不必担心从服务器维护任何本地数据中心或基础架构。客户端。

Amazon RDS also provides other useful features in addition to the database service. The users can also migrate their existing on-premises databases to RDS Environment, create backups of the same on the cloud and store the backups in Amazon S3 (another file storage service from Amazon). We can also automate the backups as per our needs and choose a convenient pricing model as per the requirements. In this article, I’ll explain how to create an RDS instance and then demonstrate how to restore a backup from a local PC to the RDS database that we will be creating.

除了数据库服务外，Amazon RDS还提供了其他有用的功能。用户还可以将其现有的本地数据库迁移到RDS环境，在云上创建相同的备份，并将备份存储在Amazon S3（Amazon的另一种文件存储服务）中。我们还可以根据需要自动执行备份，并根据要求选择便捷的定价模型。在本文中，我将解释如何创建RDS实例，然后演示如何将备份从本地PC 恢复到我们将要创建的RDS数据库。

步骤1：建立RDS环境 (Step 1: Set up an RDS Environment)

The very first step in configuring the RDS Environment would be to sign-up for an AWS Account. The free tier is eligible with RDS Service running for 750 hours per month. Once you create an account and sign in to the AWS console, search for the service “RDS” and select RDS from the drop-down menu.

配置RDS环境的第一步是注册一个AWS账户。免费套餐可享受每月运行750小时的RDS服务。创建帐户并登录到AWS控制台后，搜索服务“ RDS ”，然后从下拉菜单中选择RDS 。

Figure 1 – RDS Configuration

图1 – RDS配置

Click on the drop-down, a new portal for Amazon RDS will show up where you can manage all your RDS instances. Here, navigate to the Databases on the left-hand pane and click on Create Database.

单击下拉列表，将显示一个新的Amazon RDS门户，您可以在其中管理所有RDS实例。在这里，导航至左侧窗格中的数据库 ，然后单击创建数据库 。

Figure 2 – Create Database

图2 –创建数据库

步骤2：建立资料库 (Step 2: Creating the Database)

You’ll be navigated to the database creation page, where you can configure the RDS environment further. Select Standard Create as the database creation method. Select SQL Server as the engine and SQL Server Express Edition as the edition.

您将导航到数据库创建页面，您可以在其中进一步配置RDS环境。选择“ 标准创建”作为数据库创建方法。选择“ SQL Server”作为引擎，然后选择“ SQL Server Express Edition ”。

Figure 3 – Creating the Database

图3 –创建数据库

We will configure the RDS environment for the latest version of SQL Server which is 2017. Please select the version accordingly. Choose Free Tier as the template and provide an instance name and username for the instance. Remember that you’ll be connecting to this instance using the user details that you configure here.

我们将为SQL Server的最新版本 （2017年）配置RDS环境。请相应选择版本。选择“ 免费套餐”作为模板，并提供实例的实例名称和用户名 。请记住，您将使用在此处配置的用户详细信息连接到该实例。

Figure 4 – Choosing Version and Template

图4 –选择版本和模板

Once you have provided the instance name and the master username, the next step is to provide a master password for the user. Please provide a strong password that is at least 12 characters long. For more security, I usually go with a length of 16 and a combination of alphanumeric characters.

提供实例名称和主用户名后，下一步就是为用户提供主密码 。请提供至少12个字符的强密码。为了提高安全性，我通常使用长度为16的字符和字母数字字符的组合。

Select the instance size as db.t2.micro and storage type as General Purpose SSD with 20 Gigabytes of storage.

将实例大小选择为db.t2.micro ，将存储类型选择为具有20 GB存储空间的通用SSD 。

Figure 5 – Configuring Instance Size

图5 –配置实例大小

When you scroll below, the next section is to configure the connectivity to the database instance. For the Virtual Private Cloud select the Default VPC that is available. Repeat the same for the Subnet Group as well, we will use the Default Subnet available here. Since we would like to be able to connect to the RDS environment from the public domain and not only within AWS, select Yes for the Publicly accessible option. Also, for the VPC Security Group, select Choose Existing and select the Default.

当您在下面滚动时，下一部分将配置与数据库实例的连接。对于虚拟私有云，选择可用的默认VPC 。对子网组也重复同样的操作，我们将使用此处可用的默认子网 。由于我们希望能够从公共域（不仅在AWS内部）连接到RDS环境，因此对于“ 公共可访问性”选项选择“ 是 ”。此外，对于VPC安全组 ，选择选择现有，然后选择默认。

Figure 6 – Configure Connectivity

图6 –配置连接

Once all the configurations are done as required, click on Create Database.

按照要求完成所有配置后，点击创建数据库 。

Figure 7 – Create Database

图7 –创建数据库

步骤3：配置RDS数据库 (Step 3: Configuring the RDS Database)

As you create the database in the previous step, it will take some time for AWS to create the instance up and running. Meanwhile, you can monitor the status while it is being created as follows.

在上一步中创建数据库时，AWS将花费一些时间来创建并运行实例。同时，可以在创建状态时监视其状态，如下所示。

Figure 8 – Creating a Database

图8 –创建数据库

Once the database is created the status will be updated to Available.

创建数据库后，状态将更新为“ 可用” 。

Figure 9 – Database Available

图9 –数据库可用

You can now copy the endpoint of this RDS environment and keep it safe to connect from any client application like SQL Server Management Studio later.

现在，您可以复制此RDS环境的终结点，并确保以后可以从任何客户端应用程序（如SQL Server Management Studio）进行连接。

Figure 10 – RDS Endpoint

图10 – RDS端点

The next step is to configure the Security Group to allow inbound traffic to access the SQL Server. In order to do this, click on the Default VPC Security Group. You’ll be navigated to a new page to manage the traffic.

下一步是将安全组配置为允许入站流量访问SQL Server。为此，请单击默认VPC安全组。您将被导航到一个新页面来管理流量。

Figure 11 – Selecting the Default Security Group

图11 –选择默认安全组

In the Security Groups page that appears, you can see all the security groups that are available in your AWS account. Since I’m using the default group only, there is only one security group. Click on the security group to configure further.

在出现的“ 安全组”页面中，您可以查看您的AWS账户中可用的所有安全组。由于我仅使用默认组，因此只有一个安全组。单击安全组以进一步配置。

Figure 12 – Security Groups

图12 –安全组

In the Security Group page, click on Edit Inbound Rules.

在“安全组”页面中，单击“ 编辑入站规则” 。

Figure 13 – Edit Inbound Rules

图13 –编辑入站规则

Click on Add Rule and add a rule for Custom Type to the port 1433 (default for SQL Server) with source as Anywhere. This will allow traffic from outside the AWS environment to connect to the RDS environment.

单击“ 添加规则”，然后将“ 自定义类型” 规则添加到端口1433 （SQL Server的默认设置），源为Anywhere 。这将允许来自AWS环境外部的流量连接到RDS环境。

Figure 14 – Adding Custom Rule

图14 –添加自定义规则

Once the rules are added, you can see all the rules available for the security group.

添加规则后，您可以看到可用于安全组的所有规则。

Figure 15 – Inbound Rules

图15 –入站规则

步骤4：连接到RDS环境 (Step 4: Connecting to the RDS Environment)

Start the SQL Server Management Studio and provide connection details. Enter the endpoint that we selected in the previous steps as the Server Name. Provide the username and the master password that we had set while creating the database.

启动SQL Server Management Studio并提供连接详细信息。输入在先前步骤中选择的端点作为“ 服务器名称” 。提供我们在创建数据库时设置的用户名和主密码 。

Figure 16 – Connecting using SSMS

图16 –使用SSMS连接

You can see that the connection to the RDS database is successful and a default database with the name RDS is available.

您可以看到与RDS数据库的连接成功，并且具有名称RDS的默认数据库可用。

Figure 17 – RDS Connection Successful

图17 – RDS连接成功

步骤5：用于还原备份的其他配置 (Step 5: Additional Configuration for restoring a backup )

In order to restore a backup from a local PC to the RDS database, it is essential that we first upload the backup (BAK) file to AWS S3. You can think of S3 as a file system on the cloud, like OneDrive or Google Drive. Also, we will need to configure the Subnet Groups and Option Groups for the database in order to restore the backup from S3.

为了将备份从本地PC还原到RDS数据库，至关重要的是我们首先将备份（BAK）文件上传到AWS S3。您可以将S3视为云上的文件系统，例如OneDrive或Google Drive。另外，我们将需要为数据库配置子网组和选项组 ，以便从S3恢复备份。

Click on Subnet Groups and provide the details as shown in the figure below. Select the default VPC and click on Add all the subnets related to this VPC.

单击子网组，然后提供详细信息，如下图所示。选择默认的VPC ，然后单击“ 添加与此VPC相关的所有子网” 。

Figure 18 – Creating DB Subnet Group

图18 –创建数据库子网组

Scroll below and click on the Create button to create the DB subnet group.

向下滚动并单击创建按钮以创建数据库子网组。

Figure 19 – Creating DB Subnet Group

图19 –创建数据库子网组

You can see the DB Subnet Group once created.

创建后，您可以看到数据库子网组 。

Figure 20 – DB Subnet Group Created

图20 –创建的数据库子网组

The next step is to set up the option group where we will define which option we would like to choose. In this case, we will choose the option to restore a database.

下一步是设置选项组，我们将在其中定义我们要选择的选项。在这种情况下，我们将选择还原数据库的选项。

Click on Option Groups on the left-hand pane. Provide the details as shown in the figure below. Select sqlserver-ex as the Engine and the Engine Version as 14. Click on Create once completed.

单击左侧窗格上的选项组。提供详细信息，如下图所示。选择sqlserver-ex作为引擎，选择引擎版本为14 。完成后单击创建。

Figure 21 – Creating Option Group

图21 –创建选项组

Once the option group is created select it and click on Add option. This will enable us to add options for the option group that we selected.

创建选项组后，将其选中，然后单击“ 添加选项” 。这将使我们能够为所选的选项组添加选项。

Figure 22 – Add Options

图22 –添加选项

In the Add Option page, select the option as SQLSERVER_BACKUP_RESTORE. Choose the Create a New Role option and provide a name for the IAM role.

在“添加选项”页面中，将选项选择为SQLSERVER_BACKUP_RESTORE 。选择“ 创建新角色”选项，并提供IAM角色的名称。

Figure 23 – Selecting Option

图23 –选择选项

The next step is to provide the S3 bucket name from which the backup file will be available. A bucket in S3 is the root level folder once you’re in your s3 console. Select the option as Immediately for scheduling so that the changes are applied right away. Once completed, click on Add option.

下一步是提供S3存储桶名称，从中可以使用备份文件。一旦进入s3控制台，S3中的存储桶就是根目录文件夹。选择选项“ 立即调度”，以便立即应用更改。完成后，点击添加选项 。

Figure 24 – Add Option

图24 –添加选项

Now that the option group is created, we need to tell the RDS environment to use the new option group that we just created.

现在已经创建了选项组，我们需要告诉RDS环境使用刚刚创建的新选项组。

Click on Databases on the left-hand pane and click on Modify.

单击左侧窗格上的数据库 ，然后单击修改。

Figure 25 – Modify Database

图25 –修改数据库

In the Database options page, select the name of the Option group from the drop-down.

在“ 数据库选项”页面中，从下拉列表中选择“ 选项”组的名称。

Figure 26 – Selecting Option Group

图26 –选择选项组

步骤6：将备份上传到S3 (Step 6: Upload the backup to S3)

In order to upload the backup file to S3, navigate to the S3 console in AWS, and open the bucket that you defined in the previous steps. Upload the backup file from your local PC to the bucket.

为了将备份文件上传到S3，请导航到AWS中的S3控制台，然后打开在先前步骤中定义的存储桶。将备份文件从本地PC上传到存储桶。

Figure 27 – Uploading Backup File

图27 –上传备份文件

Figure 28 – Backup File Uploaded

图28 –上传的备份文件

步骤7：还原备份 (Step 7: Restore the Backup)

The final step now is to restore the backup from S3 into the RDS environment. In order to do that, simply execute the script below after connecting to the RDS instance. This script will connect to the backup file stored in S3 and restore the backup to the RDS database instance.

现在的最后一步是将备份从S3还原到RDS环境。为此，只需在连接到RDS实例后执行以下脚本即可。该脚本将连接到S3中存储的备份文件，并将备份还原到RDS数据库实例。

EXEC msdb.dbo.rds_restore_database
@restore_db_name = 'AdventureWorks',
@s3_arn_to_restore_from = 'arn:aws:s3:::rds-sql-backup-001/AdventureWorksLT2017.bak'

Figure 29 – Restore Database Script

图29 –恢复数据库脚本

The restore process might take some time to complete. You can check the status of the restore by running the script below. Once the restore is completed, you’ll see that the value for Lifecycle has been updated to “SUCCESS”.

恢复过程可能需要一些时间才能完成。您可以通过运行以下脚本来检查还原的状态。恢复完成后，您将看到Lifecycle的值已更新为“ SUCCESS ”。