Rajendra Gupta
How Amazon RDS Custom works

Remote desktop access for AWS RDS SQL Server with Amazon RDS Custom

December 14, 2021 by

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.

How Amazon RDS Custom works

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.

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.

RDS Custom for SQL Server architecture

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

Deploy AWS Identify and Access Management profile

In the create stack page, choose the following options:

Create stack with new template file

  • 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

    Stack Name

  • Click on the checkbox – I acknowledge that AWS CloudFormation might create IAM resources with custom names and create the stack

    Accept conditions

    It deploys the required components and its status changes to CREATE_COMPLETE

    Deployment state

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.

Note Route table ID

Deploy VPC using Cloud Formation

Create a new stack using the custom-vpc.json as shown below.

Deploy VPC using Cloud Formation

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

Choose Private Subnet and VPC

The following image shows that we have created VPC and IAM profiles using the AWS CloudFormation template.

Deployed Stacks

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

Choose an engine type

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.

RDS Custom secruity

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).

RDS custom automation

Deploy the AWS RDS custom for SQL Server, and you can view the RDS server with the AMI name.

View platform details

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.

Connect to DB 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.

RDP client

We have the remote desktop session for the AWS RDS SQL Server instance, as shown below.

Remote desktop access

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.

Login properties for admin user

  • 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.

Rajendra Gupta
AWS RDS

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

439 Views