AWS Database Blog

Launch an Amazon RDS Custom for SQL Server Instance using AWS CloudFormation

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service which provides administrative access to the underlying operating system and database (DB) environment. This allows you to run third-party applications, install drivers or access native features of SQL Server or Windows, while getting the benefits of a managed database. You can also use SQL Server Developer Edition or leverage your existing SQL Server licenses via the Bring Your Own Media capability.

In this post, we explain how to both set up RDS Custom dependencies and launch the RDS Custom instance itself using a predefined AWS CloudFormation template. We also explain the Custom Engine Version (CEV) creation using AWS CLI.

Alternatively, if you’re looking to get started with RDS Custom and launch an instance from the RDS Console rather than from CloudFormation, see this getting started guide instead, which only uses CloudFormation to set up the pre-requisites needed for RDS Custom.

Solution overview

The following chart shows all resources that you need to launch an RDS Custom instance:

The high-level steps to implement this solution are as follows:

  1. Create a network setup like VPC, subnets, security groups.
  2. Create an Amazon IAM instance profile
  3. Create an Amazon KMS key
  4. Launch an Amazon EC2 instance with a Windows AMI.
  5. Use SQL Server Management Studio (SSMS) or another client to connect to the RDS Custom for SQL Server instance

We’ve divided this post into two major options to launch an Amazon RDS Custom for SQL Server instance via CloudFormation:

  • Launch an RDS Custom for SQL Server in a new VPC
  • Launch an RDS Custom for SQL Server in an existing VPC

Prerequisites

Before you begin, make sure you have the following prerequisites:

To check the pricing, refer to Amazon RDS Custom pricing.

Launch an RDS Custom for SQL Server instance in a new VPC

To launch an RDS Custom SQL Server instance in a new VPC, do the following:

  1. Download the CloudFormation template.
  2. Open the CloudFormation console and complete the prerequisites for RDS Custom for SQL Server by launching the networking stack.
  3. Create the CloudFormation stack

The stack creation process can take approximately 30 to 40 minutes to deploy.

For more information about the parameters being passed during the network setup, refer to Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup).

The following outputs show the resources deployed by the customsqlserver- network-prereq stack:

The stack creation process can take approximately 30 to 40 minutes to deploy.

(Optional) If you would like your changes to C: drive to persist across events such as disk corruptions and failovers, you can use your own customized AMI as a basis for RDS Custom. This is called using a Custom Engine Version, or CEV. You do this by taking your own AMI, customizing it, and then creating a CEV from that AMI. Once the CEV is created, you can use it as a basis for RDS Custom instances.

If you do not wish to use a Custom Engine Version, then you can use an RDS-Provided Engine version, which contains a default AMI and SQL Server installation. You can read about the differences between CEV and RPEV in AWS Documentation.

An in-depth, step-by-step guide to creating a CEV, which uses SQL Server Developer Edition can be found here. You can create a custom engine version (CEV) using the AWS Management Console or the AWS CLI.

The following command creates a custom engine version (CEV) which allows you to maintain your preferred baseline configuration of the OS and database.

aws rds create-custom-db-engine-version \
--engine custom-sqlserver-ee \
--engine-version 15.00.4249.2.my_cevtest \
--image-id <ami-id> \
--kms-key-id <my-kms-key> \
--description "Custom SQL Server EE 15.00.4249.2 cev test"

For more information, refer to create-custom-db-engine-version.

To create CEV using the AWS Console, refer to the following AWS Documentation.

Make sure that the Amazon Machine Image (AMI) is in the same AWS account and Region as your CEV. Otherwise, the process to create a CEV fails. Also, you must prepare an AMI using Sysprep. For more information about prepping an AMI using Sysprep, see Persist your OS-level customization within Amazon RDS Custom for SQL Server using Custom Engine Version (CEV).

Create AWS resources using a CloudFormation template

To create AWS resources using a CloudFormation template, do the following:

  1. Download the CloudFormation Template
  2. On the AWS CloudFormation Console , choose Create stack
  3. Select With new resources (standard)
  4. For Template source, choose Upload a template file
  5. For Choose file, locate and choose the file that you downloaded earlier
  6. Choose Next
  7. Fill in the parameters as mentioned in the CloudFormation stack.
    • CrossReferenceStackName
    • Leave the following parameters blank if you provided the CrossReferenceStackName
      • CustomIAMInstanceProfile
      • KMSKeyId
      • DBSubnetGroupName
      • VPCSecurityGroups
    • Engine
    • EngineVersion
    • DBInstanceClass
    • MultiAZ
    • StorageType
    • Iops(Optional)
    • AllocatedStorage
    • StorageThroughput(Optional)
    • MasterUserName
  8. Choose Next and deploy the stack
  9. For Stack name, enter a name. In this example, we use the name customsqlserver-provisioning
  10. In the Parameters section, either keep the default parameter values or specify the appropriate values as needed
    The following summarizes the parameters for our stack creation.

  11. Choose Next
  12. On the Configure stack options page, choose Next
  13. On the Review page, validate the parameters
  14. Choose Create stack.
    The stack creation process can take approximately 30 to 40 minutes to deploy.
  15. When the stack creation is complete, navigate to the stack (customsqlserver-provisioning) and choose the Resources tab to review all the resources that were created as part of this CloudFormation template.
  16. On the Outputs tab, note the details. The following screenshot shows our sample stack creation outputs.
  17. Now, let’s verify the Amazon RDS Custom SQL Server Database instance in Amazon RDS Service.

Launch an RDS Custom for SQL Server in an existing VPC

If you are having an existing VPC, follow the steps in this section to using your existing VPC to create a custom SQL Server DB instance.

Create AWS resources using a CloudFormation template

To create AWS resources using a CloudFormation template, do the following:

  1. Download the CloudFormation Template
  2. On the AWS CloudFormation Console , choose Create stack
  3. Select With new resources (standard)
  4. For Template source, choose Upload a template file
  5. For Choose file, locate and choose the file that you downloaded earlier
  6. Choose Next
  7. Fill in the parameters as mentioned in the CloudFormation stack.
    • Engine
    • EngineVersion (If not using CEV use the default RDS engine version for eg:15.00.4261.1.v1)
    • DBInstanceClass
    • MultiAZ
    • StorageType
    • Iops(Optional)
    • AllocatedStorage
    • StorageThroughput(Optional)
    • MasterUserName
    • EnableRDPAccessToPrivateVPC
    • PrivateRouteTable
    • PrivateSubnets
    • VPC
    • VPCCidr
  8. Choose Next and deploy the stack
  9. For Stack name, enter a name.
  10. In the Parameters section, either keep the default parameter values or specify the appropriate values as needed
  11. Choose Next
  12. On the Configure stack options page, choose Next
  13. On the Review page, validate the parameters
  14. Choose Create stack. The stack creation process can take approximately 30 to 40 minutes to deploy.
  15. When the stack creation is complete, navigate to the stack and choose the Resources tab to review all the resources that were created as part of this CloudFormation template.

Use Fleet Manager Remote Desktop to connect to Amazon RDS Custom forSQL Server

After you create the Amazon RDS Custom for SQL Server instance, make the RDP connection from the Fleet Manager Remote Desktop and then connect to the RDS Custom for SQL Server instance using SSMS. Complete the following steps to configure an SSMS connection to Amazon RDS Custom for SQL Server from a Windows EC2 instance:

  1. On the Amazon EC2 console, navigate to the Amazon EC2 instance created with the Amazon RDS Custom for SQL Server Database instance creation.
  2. Find the key pair for the Amazon EC2 instance as shown in the following screenshot which is used for connectivity.
  3. Open the AWS Secrets Manager console and search the key pair with RDSInstancePasswordSecret to retrieve the credentials of RDS Custom for SQL Server.
  4. Choose the key name and retrieve the secret value.
  5. Connect to the EC2 instance using the Fleet Manager Remote Desktop.
  6. Choose Fleet Manager Remote Desktop at the bottom which opens a new tab with Fleet manager service page.
  7. Upload the saved .pem file and choose Connect.
  8. Enter the required details in Fleet Manager and connect to RDS Custom for SQL server endpoint noted from the previous step.
  9. You can now successfully connect to RDS Custom for SQL Server Database instance.

Clean up

To avoid incurring ongoing charges, clean up the resources you created as part of this post. You can use the AWS CloudFormation console or AWS CLI to delete the CloudFormation stack that you created earlier and remove the protection override for the RDS Custom instance and delete it.

Conclusion

In this post, we discussed how to create an Amazon RDS Custom for SQL Server instance using a CloudFormation template. We showed you how to configure the networking prerequisites using a CloudFormation template and create a CEV using AWS CLI. We then showed how to create an RDS Custom instance with CloudFormation. In addition, we used Fleet Manager Remote Desktop to connect to Amazon RDS Custom for SQL Server.

We invite you to leave your feedback in the comments sections and try out the solution.

About the authors

Sharath Chandra Kampili is a Database Specialist Solutions Architect with Amazon Web Services. He works with the Amazon RDS team, focusing on commercial database engines like Oracle. Sharath works directly with AWS customers to provide guidance and technical assistance on the database projects, helping them improve the value of their solutions when using AWS.

Bhavya Chugh is a Partner Solutions Architect with Amazon Web Services. She works with AWS partners helping them get validated to join the various programs in AWS Partner Network. She works directly with AWS partners in providing them architectural best practice to keep their application resilient, compliant and secure.