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.
In this article, we will cover:
- Explain Cloud service offerings
- Detail AWS key terminologies
- Configure and setup AWS RDS SQL Server instance
- 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.
Let us deep-dive into the IT balancing act that is inherent with the following service offering across all the cloud-platforms:
- 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
- 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
- Software as a service (SaaS), an application is integrated as service, eliminating almost all possible overheads of the traditional IT costs and other activities
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.
The Microsoft, AWS, Google and many more vendors provide a platform to host database as a service
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.
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
- Open Source migration wizard
- SQL Native backups
Get started
Let’s discuss the basic terminology needed for everyone to understand in the cloud world
- 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
- 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
- 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
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
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
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
- Multi-AZ Failover – Amazon RDS supports high-availability and failover for DB instances using Multi-AZ deployments
- 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
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.
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:
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.
- Note: Amazon EC2 can be deployed using the ‘Bring Your Own License (BYOL)’ option but Amazon RDS uses ‘License Included option’
Check out the following link to understand more about feature support and other details of 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.
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.
Launch SQL Server Instance
This section details the step needed to set up the AWS RDS SQL Server instance.
-
Sign in to the AWS
-
Open Amazon Management Console:
-
In the navigation pane, choose RDS, this will take you to the RDS Dashboard:
-
On the top right corner of the Amazon RDS console, choose the region where you want to deploy the DB instance:
- In this case, the Ohio (US-East) region is selected. This purely based on the latency details; please refer to the aforementioned cloudping.info page for more information
-
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
-
In the Select Engine window, choose the SQL Server icon and then choose the Microsoft SQL Server edition button that you want to use
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:
-
Select the Time Zone. In this case, the Central Standard Time (CST) time zone is selected:
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:
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:
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:
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)
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.
That’s all for now…
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021