aws rds监控慢sql_AWS RDS SQL Server的多可用区配置

aws rds监控慢sql

Amazon provides AWS RDS SQL Server as a managed relational database service. You can deploy databases instantly without worrying about underlying hardware platform, license cost, managing data centers. AWS RDS database instances are highly available. It provides a failover mechanism using the Mult-AZ deployments.

Amazon提供AWS RDS SQL Server作为托管关系数据库服务。 您可以立即部署数据库，而不必担心基础硬件平台，许可证成本，管理数据中心。 AWS RDS数据库实例是高度可用的。 它使用Mult-AZ部署提供故障转移机制。

We will cover answers to the following questions in this article:

我们将在本文中回答以下问题：

• What is a Multi-AZ configuration?
  什么是多可用区配置？
• Configuration Multi-AZ configuration in AWS RDS SQL Server
  AWS RDS SQL Server中的配置多可用区配置
• Validation steps
  验证步骤

Let me explain a few useful terms in AWS before explaining the Multi-AZ configuration:

在解释多可用区配置之前，让我解释一下AWS中的一些有用术语：

Region: Region represents a geographical area in AWS. It is a collection of two or more availability zones. Each region is wholly independent of each other. We deploy resources in AWS in a specific region using the AWS console.

地区：地区代表AWS中的一个地理区域。 它是两个或更多可用区的集合。 每个区域彼此完全独立。 我们使用AWS控制台在特定区域的AWS中部署资源。

Availability Zone (known as AZ): Availability zone refers to a data center. Each region contains multiple AZ’s that are isolated from each other.

可用区（称为AZ）：可用区是指数据中心。 每个区域包含彼此隔离的多个可用区。

AWS RDS中的多可用区配置概述 (Overview of Multi-AZ Configuration in AWS RDS)

Suppose you configured an AWS RDS SQL Server. If you do not have an RDS instance, follow the article AWS RDS SQL Server – Launching a new database instance and create an RDS SQL instance before proceeding for this article.

假设您配置了一个AWS RDS SQL Server。 如果您没有RDS实例，请在继续本文之前，遵循文章AWS RDS SQL Server –启动新的数据库实例并创建RDS SQL实例。

Open the RDS instance and you can see my RDS instance is in an us-east-1f availability zone:

打开RDS实例，您可以看到我的RDS实例在us-east-1f可用区中：

AWS RDS SQL Server中的多可用区部署 (Multi-AZ deployment in AWS RDS SQL Server)

Let’s say my AWS RDS instance is in availability zone us-east-1a. Suppose the availability zone is down due to power failure or hardware failure. In this case, your application cannot connect to the RDS database hosted in that AZ. We require a database to be highly available and fault tolerance for any such issues.

假设我的AWS RDS实例位于可用性区域us-east-1a中。 假设可用性区域由于电源故障或硬件故障而关闭。 在这种情况下，您的应用程序无法连接到该AZ中托管的RDS数据库。 我们要求数据库具有高可用性，并且对于任何此类问题均具有容错能力。

Amazon provides Multi-AZ deployment for RDS instance. It maintains a synchronous and stands by database copy in a different AZ but belonging to the same region. You can failover RDS instance to secondary replica and application can be available. It automatically performs a failover to the secondary replica in the following scenarios:

Amazon为RDS实例提供了多可用区部署。 它保持同步，并在其他AZ中但属于同一区域的数据库副本为备用。 您可以将RDS实例故障转移到辅助副本，并且应用程序可用。 在以下情况下，它将自动执行故障转移到辅助副本：

• Primary AZ availability issues
  主要可用区可用性问题
• Network connectivity issues for primary AZ
  主可用区的网络连接问题
• Hardware failure (Compute unit, storage)
  硬件故障（计算单元，存储）
• Scheduled patching in availability zone servers
  可用性区域服务器中的计划修补

In the following image, note down the following:

在下图中，记下以下内容：

• Primary AWS RDS SQL Server instance is in us-east-1a AZ
  主AWS RDS SQL Server实例位于us-east-1a AZ中
• It has a synchronous copy in another AZ in us-east-1b
  它在us-east-1b的另一个AZ中具有同步副本
• Both primary and secondary RDS instance belongs to a region
  主RDS实例和辅助RDS实例都属于一个区域
• The user connects to an RDS instance using the endpoint
  用户使用端点连接到RDS实例
• Endpoint automatically connects to a primary replica
  端点自动连接到主副本

Now, due to some issues, AWS RDS SQL Server instance on the primary replica is unavailable. In this case, failover happens from primary to the secondary replica. RDS instance in us-east-1b becomes primary.

现在，由于某些问题，主副本上的AWS RDS SQL Server实例不可用。 在这种情况下，故障转移发生在主副本到辅助副本上。 us-east-1b中的RDS实例成为主要实例。

User connections are automatically diverted to new primary replica (us-east-1b):

用户连接将自动转移到新的主副本（us-east-1b）：

By default, the Multi-AZ feature is not enabled for the RDS instance. Open the AWS management console, navigate to databases. In the RDS instance property, we have a property Multi-AZ.

默认情况下，RDS实例未启用多可用区功能。 打开AWS管理控制台，导航到数据库。 在RDS实例属性中，我们具有属性Multi-AZ。

In the following screenshot, we can see value N/A for Multi-AZ that shows this feature is disabled. We do not have any failover mechanism configured for this instance:

在以下屏幕截图中，我们可以看到“多可用区”的N / A值显示该功能已禁用。 我们没有为此实例配置任何故障转移机制：

Amazon RDS provides two kinds of Multi-AZ deployments for SQL Server.

Amazon RDS为SQL Server提供了两种多可用区部署。

始终可用的基于组的多可用区部署 (Always On availability Group based Multi-AZ deployments)

• SQL Server 2017 14.00.3049.1 or later( Enterprise Edition)
  SQL Server 2017 14.00.3049.1或更高版本（企业版）
• SQL Server 2016 13.00.5216.0 or later( Enterprise Edition)
  SQL Server 2016 13.00.5216.0或更高版本（企业版）

基于数据库镜像组的多可用区部署（上面指定的版本除外） (Database mirroring Group based Multi-AZ deployments (except versions specified above))

• Standard and Enterprise Editions
  标准版和企业版
• SQL Server 2017, 2016,2014,2012
  SQL Server 2017、2016、2014、2012年

在RDS实例中启用多可用区部署 (Enable Multi-AZ deployment in RDS instance)

We can modify the RDS instance to enable the Multi-AZ feature. The instance should be in an available status to make the changes. In the following screenshot, we can see Modify is greyed out because the instance is in the starting state:

我们可以修改RDS实例以启用多可用区功能。 该实例应处于可用状态以进行更改。 在以下屏幕截图中，我们可以看到Modify变灰，因为实例处于启动状态：

We can connect to this RDS instance using endpoint: myrdsinstance.cumznzii9fba.us-east-1.rds.amazonaws.com as shown below:

我们可以使用以下端点连接到该RDS实例：myrdsinstance.cumznzii9fba.us-east-1.rds.amazonaws.com，如下所示：

Once the instance is available, click on modify. We do not get any option to enable Multi-AZ in this instance because I have SQL Server expression edition installed; therefore, I do not get an option to enable Multi-Az deployment.

实例可用后，单击“修改”。 在这种情况下，我们没有任何启用Multi-AZ的选项，因为我安装了SQL Server表达式版本。 因此，我没有启用Multi-Az部署的选项。

Let’s create a new RDS instance with following edition and version of SQL Server:

让我们使用以下版本SQL Server创建一个新的RDS实例：

• SQL Server Standard edition
  SQL Server标准版
• SQL Server 2017 14.00.3049.1v1
  SQL Server 2017年14.00.3049.1v1

In the Availability & durability, you get an option for Multi-AZ deployment. Click on Yes (Mirroring/Always ON). RDS automatically selects mirroring or always on based on the instance edition and version:

在可用性和耐用性中，您可以选择进行多可用区部署 。 单击是（镜像/始终打开）。 RDS根据实例版本和版本自动选择镜像或始终基于镜像：

• Note: AWS RDS SQL Server Standard edition is not free to use. In the Create instance page, it gives you an estimate of a monthly estimate:

  注意： AWS RDS SQL Server标准版不是免费使用。 在“创建实例”页面中，它为您提供了每月估算值：

  

By default, it enables automatic backs and specifies the backup retention period 7 days:

默认情况下，它启用自动备份并指定备份保留期7天：

If you specify a backup retention period as zero-days, Multi-AZ configuration fails with the following error message. It cannot apply mirroring to instances with backup retention set to zero:

如果将备份保留期指定为零天，则多可用区配置将失败，并显示以下错误消息。 它不能将镜像应用于备份保留时间设置为零的实例：

It takes 15-20 minutes for a database instance creation. Once the new RDS instance is available, open the instance properties.

创建数据库实例需要15到20分钟。 新的RDS实例可用后，打开实例属性。

You can see that primary replica is in us-east–1f and running SQL Server standard edition:

您可以看到主副本位于us-east-1f中，并且正在运行SQL Server标准版：

In the screenshot below, verify that Multi-AZ is enabled, and it is using database mirroring mechanism. You can also see the secondary zone us-east-1d that acts as a secondary or stand by the database instance:

在下面的屏幕快照中，确认已启用Multi-AZ，并且它正在使用数据库镜像机制。 您还可以看到辅助区域us-east-1d充当数据库实例的辅助区域或备用数据库实例：

Note down the endpoint from connectivity and security. Connect the instance using the endpoint.

从连接性和安全性上记下端点。 使用端点连接实例。

Endpoint: multiazdemo.cumznzii9fba.us-east-1.rds.amazonaws.com

端点：multiazdemo.cumznzii9fba.us-east-1.rds.amazonaws.com

Execute the command, and it creates a new database into AWS RDS:

执行命令，它将在AWS RDS中创建一个新数据库：

CREATE DATABASE MultiAZTest;

In the following screenshot, we do not see this database configured in the database mirroring:

在以下屏幕截图中，我们没有在数据库镜像中看到该数据库的配置：

Wait for some time and refresh the database instance. You can see that the newly created database is also configured in the database mirroring automatically. It’s status changes to Principal Synchronized. It takes database snapshot, restores the copy on standby server and configures database mirroring. We do not require any manual interventions here:

等待一段时间，然后刷新数据库实例。 您可以看到新创建的数据库也在数据库镜像中自动配置。 它的状态更改为“主体同步”。 它获取数据库快照，在备用服务器上还原副本并配置数据库镜像。 在这里，我们不需要任何手动干预：

We can also view the SQL Server error logs for detailed information about this newly created database. Navigate to the RDS Dashboard and click on Logs. Select the latest logs and click on View:

我们还可以查看SQL Server错误日志，以获取有关此新创建的数据库的详细信息。 导航到RDS 仪表板 ，然后单击Logs 。 选择最新的日志，然后单击查看 ：

It opens the logs in a separate window. You can see that database mirroring is configured for database MultilAZTest and logs show the principal server copy:

它将在单独的窗口中打开日志。 您可以看到已为数据库MultilAZTest配置了数据库镜像，并且日志显示了主体服务器副本：

Amazon RDS does not allow you to access the stand by a copy of the RDS instance. AWS entirely manages it. It cannot be used for diverting database traffic to the secondary instance. It comes in picture in case of any disaster or unavailability of instance in primary AZ.

Amazon RDS不允许您通过RDS实例的副本访问支架。 AWS完全管理它。 它不能用于将数据库流量转移到辅助实例。 在主可用区发生任何灾难或实例不可用的情况下，它将以图片形式显示。

多可用区AWS RDS SQL Server的手动数据库故障转移 (Manual database failover for Multi-AZ AWS RDS SQL Server)

Let’s say we require rebooting the primary replica of the RDS instance. Click on Actions and Reboot:

假设我们需要重新启动RDS实例的主副本。 单击操作并重新启动 ：

In Multi-AZ configuration, we get an option to reboot instance with failover. If we enable this checkbox, the RDS instance fails over to standby copy in another availability zone:

在多可用区配置中，我们可以选择通过故障转移重启实例。 如果启用此复选框，则RDS实例将故障转移到另一个可用性区域中的备用副本：

• Failover happens from primary to stand by RDS replica
  故障转移从主要发生到备用RDS副本
• The current stand by replica takes over the role of primary and starts accepting database connections
  当前的备用副本将接管主要角色，并开始接受数据库连接
• It reboots stand by (old primary) instance
  它重新启动备用（旧的主实例）实例

Put a tick in checkbox for a reboot with failover and reboot:

在复选框中打勾，以通过故障转移和重新启动进行重新启动：

It takes 1-2 minutes, for instance to failover, to perform database recovery and start accepting database connections. In the rebooting state, it shows the primary AZ, i.e. us-east-1f, in this case:

例如，完成故障转移需要1-2分钟的时间来执行数据库恢复并开始接受数据库连接。 在重新引导状态下，它显示了主要可用区，即us-east-1f，在这种情况下：

Once the RDS instance fails over and becomes available, verify the primary and secondary availability zones:

一旦RDS实例故障转移并变得可用，请验证主要和辅助可用性区域：

• Primary AZ before failover: us-east-1f

  故障转移之前的主要可用区：us-east-1f

  

• Secondary AZ before failover: us-east-1d

  故障转移之前的辅助可用区：us-east-1d

  

• Primary AZ after failover: us-east-1d

  故障转移后的主要可用区：us-east-1d

  

• Secondary AZ with failover: us-east-1f

  具有故障转移功能的辅助可用区：us-east-1f

  

You can connect to the RDS instance, and it shows all databases in synchronized mode. Application or end-user does not need to worry about the availability zone in which instance is in principal mode:

您可以连接到RDS实例，它以同步模式显示所有数据库。 应用程序或最终用户无需担心实例处于主体模式的可用性区域：

关于AWS RDS SQL Server的多可用区部署的几个要点 (Few important points about Multi-AZ deployment for AWS RDS SQL Server)

• AWS allows a single secondary copy of the RDS instance in a similar region
  AWS允许在相似区域中提供RDS实例的单个辅助副本
• We cannot configure Multi-AZ deployment for standby RDS instance in a different region
  我们无法为其他区域中的备用RDS实例配置多可用区部署
• We cannot access the stand by replica databases
  我们无法访问备用副本数据库
• Databases should be in full recovery model for Multi-AZ deployment
  数据库应处于完全恢复模型中以进行多可用区部署
• AWS automatically replicates users, logins and permissions on standby replica. No user intervention required
  AWS自动在备用副本上复制用户，登录名和权限。 无需用户干预
• RDS maintains a synchronized copy of databases in another availability zone. In synchronized mode, it waits the transaction commit acknowledgment from the standby database and then commits on primary or principal database copy. We might face latency due to synchronous commit in comparison with stand-alone database copy
  RDS在另一个可用性区域中维护数据库的同步副本。 在同步模式下，它将等待来自备用数据库的事务提交确认，然后在主数据库或主体数据库副本上进行提交。 与独立数据库副本相比，由于同步提交，我们可能会面临延迟
• We need to replicate SQL Server agent jobs manually on a standby instance. AWS RDS does not take care of it
  我们需要在备用实例上手动复制SQL Server代理作业。 AWS RDS不会照顾它
• Usually, we have a 1-2 minute of RDS instance failover including database recovery on a new primary replica. It might take longer depending upon database size, the number of active transactions, recovery process (undo, redo) efforts required
  通常，我们需要1-2分钟的RDS实例故障转移，包括在新的主副本上进行数据库恢复。 可能需要更长的时间，具体取决于数据库大小，活动事务的数量，所需的恢复过程（撤消，重做）

结论 (Conclusion)

In this article, we explored the Multi-AZ deployments for AWS RDS SQL Server. It is useful for providing an RDS instance failover mechanism for critical application. We can minimize application downtime with this failover mechanism.

在本文中，我们探索了适用于AWS RDS SQL Server的多可用区部署。 为关键应用程序提供RDS实例故障转移机制很有用。 通过这种故障转移机制，我们可以最大程度地减少应用程序停机时间。

翻译自: https://www.sqlshack.com/multi-az-configuration-for-aws-rds-sql-server/

aws rds监控慢sql