aws rds监控慢sql_AWS RDS SQL Server入门

最新推荐文章于 2024-06-26 09:35:25 发布

culuo4781

最新推荐文章于 2024-06-26 09:35:25 发布

阅读量712

点赞数

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

原文链接：https://www.sqlshack.com/getting-started-with-aws-rds-sql-server/

版权

aws rds监控慢sql

This article describes the concepts and steps to setup AWS RDS SQL Server(Relational Database Service) — a web service which gives the ability to set up and scale relational database within AWS.

本文介绍了设置AWS RDS SQL Server（关系数据库服务）的概念和步骤，该Web服务提供了在AWS内设置和扩展关系数据库的能力。

In this article, we will cover:

在本文中，我们将介绍：

Explain Cloud service offerings
解释云服务产品
Detail AWS key terminologies
详细的AWS关键术语
Configure and setup AWS RDS SQL Server instance
配置和设置AWS RDS SQL Server实例
And more…
和更多…

入门 (Getting started)

In modern-day, management there is a whole lot around mission statements. Your mission should sit alongside unique values that together offer a vision for world-changing needs. Even the most senior of IT pros in the organization—who still think that a single cloud-platform can’t optimally address all of their IT challenges. In fact, in today’s world, we use multiple solutions that can capitalize on the strengths of all the proven cloud models. That being said, the added complexity; nature of the business and different approach requires good strategies for building and managing multi-cloud environments.

在当今的管理中，围绕任务陈述的内容很多。您的使命应该与独特的价值观并存，共同为世界变化的需求提供愿景。即使是组织中最高级的IT专业人员，他们仍然认为单个云平台无法最佳解决所有IT挑战。实际上，在当今世界，我们使用了多种解决方案，可以利用所有经过验证的云模型的优势。话虽这么说，增加了复杂性；业务的性质和不同的方法要求建立和管理多云环境的良好策略。

Let us deep-dive into the IT balancing act that is inherent with the following service offering across all the cloud-platforms:

让我们深入研究跨所有云平台的以下服务产品所固有的IT平衡行为：

Infrastructure as a service (IaaS), in simple terms, it’s a VM hosting the targets but doesn’t affect software licensing, technical support costs, or business agility
简而言之，基础架构即服务（IaaS）是托管目标的VM，但不影响软件许可，技术支持成本或业务敏捷性
Platform as a service (PaaS), in this model, it adds software licensing benefits and reduces platform software support costs and eliminates extra overhead that incurs on the server maintenance
平台即服务（PaaS），在此模型中，它增加了软件许可权益并降低了平台软件支持成本，并消除了服务器维护产生的额外开销
Software as a service (SaaS), an application is integrated as service, eliminating almost all possible overheads of the traditional IT costs and other activities
软件即服务（SaaS），应用程序作为服务集成，消除了传统IT成本和其他活动的几乎所有可能的开销

介绍 (Introduction)

The biggest challenge of any DBA could face in the recent trend is to define the migration strategy from an on-premises database to the cloud. The one question always strikes in everyone’s mind is whether to use cloud or not, how to maximize the benefits, how to define the migration strategies for cost-effectiveness. Cloud means pricing, the pricing directly proportional to planning so better planning and strategy might help the organization vision in achieving the required set of goals. Moving to the cloud requires an understanding of financial implications and good planning to get effective benefits from moving to the cloud.

在最近的趋势中，任何DBA可能面临的最大挑战是定义从本地数据库到云的迁移策略。每个人都经常想到的一个问题是是否使用云，如何最大程度地利用收益，如何为成本效益定义迁移策略。云意味着定价，定价与计划成正比，因此更好的计划和策略可能有助于组织实现预期目标集的愿景。迁移到云需要了解财务影响和良好的计划，才能从迁移到云中获得有效的收益。

The Microsoft, AWS, Google and many more vendors provide a platform to host database as a service

Microsoft，AWS，Google和更多供应商提供了一个平台来托管数据库即服务

This article is an effort to detail the concepts and AWS key terminologies and walk through the steps to configure AWS RDS SQL Server instance.

本文旨在详细介绍概念和AWS关键术语，并逐步介绍配置AWS RDS SQL Server实例的步骤。

Before moving to any cloud models such as Private, Public, and Hybrid, the cloud architects must take the right strategies to fit the applications on the cloud models. It is the combined efforts of the cloud team to identify the dependencies to understand the required infrastructures needed to host the applications. The application fitment in the cloud works well with proper assessment and it needed to be done thoroughly.

在迁移到任何云模型（例如私有，公共和混合）之前，云架构师必须采取正确的策略，以使应用适合云模型。云团队的共同努力是确定依赖关系，以了解托管应用程序所需的必需基础架构。通过适当的评估，云中的应用程序适合性很好，并且需要彻底进行。

Migration can be achieved using several available methods. The vital part of any migration is feature support and size. Based on this trend, the migration can be handled in various ways

可以使用几种可用的方法来实现迁移。任何迁移的关键部分都是功能支持和大小。根据这种趋势，可以通过多种方式处理迁移

Generate scripting option
生成脚本选项
Import and Export Wizard – SSIS
导入和导出向导– SSIS
Open Source migration wizard
开源迁移向导
SQL Native backups
SQL本机备份

开始吧 (Get started)

Let’s discuss the basic terminology needed for everyone to understand in the cloud world

让我们讨论一下云世界中每个人都需要理解的基本术语

AWS – AWS – Amazon Web Services 亚马逊网络服务
RDS – AWS RDS (Relational Database Service) or RDS can support several different relational database platforms such as SQL, MySQL, Postgres, Oracle, and Amazon Aurora. Amazon RDS is a web service that makes it easier to operate, set up, configure, and scale relational database systems in the cloud. In this article, we will discuss AWS RDS SQL Server
RDS – AWS RDS（关系数据库服务）或RDS可以支持几种不同的关系数据库平台，例如SQL，MySQL，Postgres，Oracle和Amazon Aurora。 Amazon RDS是一项Web服务，可让您更轻松地在云中操作，设置，配置和扩展关系数据库系统。在本文中，我们将讨论AWS RDS SQL Server
On-Premise databases – in-house databases on the premises 本地数据库 –场所内部数据库
Virtual Private Cloud (VPC) – A virtual private cloud (VPC) is a virtual network dedicated to your AWS account. It is logically isolated from other virtual networks in the AWS Cloud. This is your network in the sky. A VPC is like having a security fence around your cloud’s network 虚拟私有云（VPC） –虚拟私有云（VPC）是专用于您的AWS账户的虚拟网络。它在逻辑上与AWS Cloud中的其他虚拟网络隔离。这是您的空中网络。 VPC就像在您的云网络周围建立安全防护网一样
S3 Storage bucket – Amazon Simple Storage Service (Amazon S3) is object storage with a simple web service interface to store and retrieve any amount of data from anywhere on the web S3存储桶 – Amazon Simple Storage Service（Amazon S3）是具有简单Web服务界面的对象存储，可以从Web上的任何位置存储和检索任何数量的数据
Regions – These are geographic locations. For example, there is a region in Ireland called EU-West-1. There is one in Virginia called US-East-1. The locations identifiers like US-East-1 know as codes

地区 –这些是地理位置。例如，爱尔兰有一个名为EU-West-1的地区。在弗吉尼亚州，有一个叫做US-East-1。位置标识符（例如US-East-1）称为代码

https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/

https://aws.amazon.com/about-aws/global-infrastructure/regional-product-services/
Availability Zones – Amazon cloud computing resources are hosted in multiple locations worldwide. These locations are composed of regions and Availability Zones. Each region is a separate geographic area. Each region has multiple, isolated locations known as Availability Zones. Amazon RDS provides you the ability to place resources, such as instances, and data in multiple locations

可用区 – Amazon云计算资源在全球多个位置托管。这些位置由区域和可用区组成。每个区域都是一个单独的地理区域。每个区域都有多个孤立的位置，称为可用区。 Amazon RDS使您能够在多个位置放置资源（例如实例和数据）

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RegionsAndAvailabilityZones.html

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RegionsAndAvailabilityZones.html
AWS Console – The single point of administration used to manage all of our AWS services AWS控制台 –用于管理我们所有AWS服务的单一管理点
Multi-AZ Failover – Amazon RDS supports high-availability and failover for DB instances using Multi-AZ deployments
多可用区故障转移 – Amazon RDS支持使用多可用区部署的数据库实例的高可用性和故障转移
Spin Up – The term used to create a new instance of just about anything. We will “spin up” several new RDS instances in this course 旋转 -用于创建几乎所有内容的新实例的术语。在本课程中，我们将“提升”几个新的RDS实例

AWS Database Migration Service helps to migrate databases to AWS easily. Selecting the region to in-house the database would be the first step in setting AWS RDS SQL Server services.

AWS Database Migration Service帮助您轻松地将数据库迁移到AWS。选择内部数据库区域将是设置AWS RDS SQL Server服务的第一步。

The cloudping.info site gives you an overview to opt for the regions. The command button HTTP Ping is refreshed and sampled the data for several runs before choosing the region:

cloudping.info网站为您提供了选择区域的概述。在选择区域之前，将刷新命令按钮HTTP Ping并采样几次运行的数据：

It is always better to choose the region after careful analysis and understanding of the business implication. The chosen region will have a significant impact on the performance of the entire system.

在仔细分析和理解业务影响之后，最好选择区域。所选区域将对整个系统的性能产生重大影响。

AWSRDS SQL Server supports multiple editions and versions of SQL Server starting from 2012, 2014, 2016, and 2017 that includes Express, Web, Standard, and Enterprise. Usually, the deployments are done in a few minutes.

AWSRDS SQL Server从2012、2014、2016和2017年开始支持SQL Server的多个版本，包括Express，Web，Standard和Enterprise。通常，部署会在几分钟内完成。

Note: Amazon EC2 can be deployed using the ‘Bring Your Own License (BYOL)’ option but Amazon RDS uses ‘License Included option’注意：可以使用“自带许可证（BYOL）”选项来部署Amazon EC2，但是Amazon RDS使用“包含许可证”选项来部署。

Check out the following link to understand more about feature support and other details of AWS RDS SQL Server:

请查看以下链接，以了解有关功能支持和AWS RDS SQL Server的其他详细信息的更多信息：

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

局限性 (Limitation)

The SQL Server suite bundles with several database components: such as SQL Server Integration Service, SQL Server Analysis Services, and SQL Server Reporting Services. The PaaS RDS database service will only install the database and give you a functional SQL Server instance.

SQL Server套件捆绑了多个数据库组件：例如SQL Server集成服务，SQL Server Analysis Services和SQL Server Reporting Services。 PaaS RDS数据库服务将仅安装数据库并为您提供功能正常SQL Server实例。

It can be said that there is quite a lot of limitation and it can’t be used like as an on-premise database with RDS but there are other options to set up EC2 instance and set up like an on-premise database.

可以说有很多限制，它不能像带有RDS的本地数据库一样使用，但是还有其他选项可以设置EC2实例并像本地数据库一样进行设置。

启动SQL Server实例 (Launch SQL Server Instance)

This section details the step needed to set up the AWS RDS SQL Server instance.

本部分详细介绍了设置AWS RDS SQL Server实例所需的步骤。

Sign in to the AWS

登录到AWS

https://aws.amazon.com/console/

https://aws.amazon.com/console/
Open Amazon Management Console:

打开Amazon管理控制台：
In the navigation pane, choose RDS, this will take you to the RDS Dashboard:

在导航窗格中，选择RDS，这将带您进入RDS仪表板：
On the top right corner of the Amazon RDS console, choose the region where you want to deploy the DB instance:

在Amazon RDS控制台的右上角，选择您想要部署数据库实例的区域：
Ohio (US-East) region is selected. This purely based on the latency details; please refer to the aforementioned cloudping.info page for more information 俄亥俄州（美国东部）地区。这完全基于延迟细节；请参考前面提到的cloudping.info页面以获取更多信息
Choose Create database to Launch DB Instance Wizard:

选择“创建数据库以启动数据库实例向导” ：

Now, the wizard opens on the Select Engine page

现在，向导将在“选择引擎”页面上打开
- Note: The available edition of SQL Server varies by region注意：SQL Server的可用版本因地区而异
In the Select Engine window, choose the SQL Server icon and then choose the Microsoft SQL Server edition button that you want to use

在“ 选择引擎”窗口中，选择“ SQL Server”图标，然后选择要使用的Microsoft SQL Server版本按钮。
- Note: For the demo, I am enabling the options that are eligible for the free tier database:
  
  注意：对于该演示，我将启用适用于免费套餐数据库的选项：
On the Specify DB Details page, choose the SQL Server version. In this case, the micro instance is selected and it’s a free tier. Selecting other DB instance class would come the underpaid tier model:

在“ 指定数据库详细信息”页上，选择SQL Server版本。在这种情况下，将选择微实例，它是免费层。选择其他数据库实例类将成为报酬不足的层模型：
Select the Time Zone. In this case, the Central Standard Time (CST) time zone is selected:

选择时区。在这种情况下，将选择“ 中央标准时间（CST）”时区：
Under the settings, enter the unique instance identifier and credential details. All should be entered in lowercase. Once completing all the entries, click the Next button:

在设置下，输入唯一的实例标识符和凭据详细信息。全部应以小写形式输入。完成所有输入后，单击“ 下一步”按钮：
Next, on the Configure Advanced Settings page, provide additional information that AWS RDS SQL Server needs to launch the SQL Server DB instance. In this case, the default configuration is used. In this case, in the database options, collation SQL_Latin1_General_CP1_CI_AS is used:

接下来，在“ 配置高级设置”页面上，提供AWS RDS SQL Server启动SQL Server数据库实例所需的其他信息。在这种情况下，将使用默认配置。在这种情况下，在数据库选项中，使用排序规则SQL_Latin1_General_CP1_CI_AS ：
Click the View DB Instance details button:

单击查看数据库实例详细信息按钮：
In the View DB Instance details page, You can see that the new SQL Server instance, mydemosql2017 appears in the list of DB instances. The Info section indicates status. You can see that the info section is “Creating”. When the state changes to “Available”, you can connect to the DB instance. Depending on the size, it can take up to 15-20 minutes before the new instance is available:

在“查看数据库实例详细信息”页面中，您可以看到新SQL Server实例mydemosql2017出现在数据库实例列表中。信息部分指示状态。您可以看到信息部分为“正在创建”。当状态更改为“可用”时，您可以连接到数据库实例。根据大小的不同，可能需要15至20分钟才能使新实例可用：
Now, let us connect to SQL instance using the SQL account. Before connecting, make sure to enable a trusted certificate. After type in the credentials, Click Options:

现在，让我们使用SQL帐户连接到SQL实例。连接之前，请确保启用可信证书。输入凭据后，单击选项：

Select the Trust server certificate and Click Connect:

选择信任服务器证书，然后单击连接：

We can see that the SQL instance is connected from a local machine using SSMS( SQL Server Management Studio)

我们可以看到SQL实例是使用SSMS（SQL Server Management Studio）从本地计算机连接的

结论 (Conclusion)

Thus far, we’ve seen the concepts and steps to set up the AWS RDS SQL Server instance. IT professionals must know the cloud vendor’s capabilities, cost nuances, monitoring options, and connectivity architectures to set up the environment. This article is an effort to give an initial push to the administrators to start exploring the options. Moving to the cloud requires an understanding of financial implications and good planning.

到目前为止，我们已经看到了设置AWS RDS SQL Server实例的概念和步骤。 IT专业人员必须了解云供应商的功能，成本细微差别，监视选项和连接架构，才能设置环境。本文旨在为管理员提供初步推动，以开始探索这些选项。迁移到云需要了解财务影响和良好的计划。

That’s all for now…

目前为止就这样了…