This article will explore AWS RDS SQL Server with Amazon RDS Custom as a managed database service for applications that require an operating system and database customization.
Introduction
Imagine you have sysadmin access to the AWS RDS SQL Server instance! Well, most of us know that we do not get control over the operating system and database environment in the PaaS database. It is a drawback for applications requiring specific drivers, patches, SQL Common Language Runtime(CLR).
SQL Server was limited in what it could offer users on the EC2 platform, and although many features were recently added to help bridge this gap (including SSIS, SSRS, and SSAS), there were still some things that were causing a minor disruption. Things like limited access to the OS and SA privileges made it challenging to use elevated items without installing SQL Server on-premises.
The AWS re: Invent 2021 announced Amazon RDS Custom for SQL Server lets you access and customize the operating system. You can apply patches, modify database configurations for supporting the 3rd party applications requiring privileged access.
Image Reference: AWS
The Amazon RDS Custom provides the following benefits:
- Amazon RDS Custom offers businesses greater customization than utilizing standard Amazon RDS instances. While not all types of standard databases are available in the Amazon RDS Custom flavor, those that do qualify can be further customized to address specific needs that may go beyond what is typically possible through normal means
- Amazon RDS Custom is perfect for web and mobile applications that need high throughput, massive scalability, and highly available databases. You can use Amazon RDS Custom in conjunction with AWS CloudFormation to standardize your database deployments and relieve the burden of managing the time-consuming day-to-day operations associated with deploying a highly customized database infrastructure
- Amazon RDS Custom scales with minimal latency and is available only in the cloud, allowing businesses to achieve fast performance for optimal response times and conversion
Deploy RDS Custom for SQL Server
The following diagram shows a high-level deployment for RDS custom.
Image Reference: AWS
- Your application uses the RDS custom database instance endpoint for querying the database
- You can use the AWS system manager or the remote desktop client to access the RDS host server
The RDS custom for SQL Server resides in the virtual private network. The database instance consists of an Amazon EC2 instance with the operating system, EBS storage, and instance endpoint. If there is an error due to custom configuration, RDS custom monitors it and notifies the system administrator.
Image Reference: AWS Architecture
To deploy the AWS RDS SQL Server using Amazon RDS Custom, we require the following requirements:
- AWS Identify and Access Management profile
- Virtual Private Cloud (Amazon VPC)
We will use the AWS CloudFormation templates for deploying the custom IAM profile and VPC.
Deploy stack using AWS CloudFormation
- Download the custom-sqlserver-iam.json and custom-vpc.json and save them in a folder or S3 bucket
-
Launch AWS CloudFormation from the services
-
Click on Create Stack and With new resources (standard)
Deploy AWS Identify and Access Management profile
In the create stack page, choose the following options:
- Prepare template: The template is ready
- Template source: Upload a template file
- Upload a template file: Choose custom-sqlserver-iam.json, and it uploads the JSON file in the S3 bucket
-
On the next page, specify the stack name
-
Click on the checkbox – I acknowledge that AWS CloudFormation might create IAM resources with custom names and create the stack
It deploys the required components and its status changes to CREATE_COMPLETE
Create Route table
Launch Route Table in AWS Services and create a new route table [rdscustomroutetable] for your VPC. Note down the route table ID as shown below.
Deploy VPC using Cloud Formation
Create a new stack using the custom-vpc.json as shown below.
Specify a stack name and choose the following parameters on the next page.
- Private subnets: Choose the two private subnets for the RDS custom DB instance
- Private VPC: Choose the private VPC for the RDS instance
- Route table: Specify the route table ID that we created earlier
The following image shows that we have created VPC and IAM profiles using the AWS CloudFormation template.
If you want to configure VPC and IAM profile manually, follow the article https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-setup-sqlserver.html#custom-setup-sqlserver.cf
Create an AWS RDS custom instance for AWS RDS SQL Server
Once the prerequisite is taken care of, you can head to the RDS console to create a database instance. Under the create database section, select standard and select SQL Server. Select Amazon RDS Custom as shown below.
- Engine type: Microsoft SQL Server
- Database Management Type: Amazon RDS Custom
- Edition: Choose Standard, Enterprise, or web edition
You can choose your preferred settings like instance identifier, master username/password, instance class, and storage. New changes are specific to the RDS custom security section to select the IAM instance profile.
Scroll down, and you have the RDS custom database automation section. In this section, you can choose the following options:
- Full automation
- Paused
During the initial RDS custom deployment, it uses the full automation mode. In this mode, the instance is under full monitoring and recovery. Later, you can pause the automation for any custom changes. Therefore, you can choose the pause mode to pause RDS custom automation for a specific period. It ensures that your customization does not interfere with the RDS custom automation. The console gives feasibility to resume manually or recover instances automatically after a specific period. You can pause the RDS instance to the default 60 minutes to 1440 minutes (maximum).
Deploy the AWS RDS custom for SQL Server, and you can view the RDS server with the AMI name.
RDS Custom databases are traditional AWS RDS SQL Server instances with one exception: RDS will deploy them to your AWS account instead of its own. That means you now have an EC2 instance that is yours to manage and use however you see fit!
You can use the AWS session manager or RDP client to take remote desktop access of the AWS RDS custom server. Navigate to the session manager and select the EC2 instance.
AWS automatically creates a key pair to connect to the AWS RDS SQL Server instance using the RDP. The key pair name is do-not-delete-rds-custom-DBInstanceIdentifier. Click on the RDP client and download the remote desktop file. The below page lists the public DNS(endpoint) from the custom RDS SQL Server and clicks on the Get Password link to generate the administrator account password.
We have the remote desktop session for the AWS RDS SQL Server instance, as shown below.
Connect to AWS RDS SQL Server using SQL Server Management Studio or client tools such as SQLCMD, Azure Data Studio. We can verify the privilege permission for the user account. As shown below, the admin user has [sysadmin] access.
- Note: The Amazon RDS Custom for SQL Server is available for specific reasons. Currently, it supports the following regions:
- US East – Ohio and N. Virginia
- US West – Oregon
- Asia Pacific – Singapore, Sydney, Tokyo
- EU – Frankfurt, Ireland, Stockholm
Conclusion
AWS RDS Custom is an excellent solution for customers who want to take control of an operating system and database configuration of AWS RDS SQL Server instance. These advantages include using a supported SQL Server version, enabling advanced configuration options, and having AWS control over backups.
If you are interested in running SQL Server on AWS RDS, RDS Custom is a great option to manage SQL Server in AWS. Furthermore, you can use the native AWS RDS management console to manage SQL Server instances, just like you do with other AWS RDS instances. So what are you waiting for? Get started with your next project on AWS by visiting https://aws.amazon.com/rds/custom.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023