AWS Database Blog

Use AWS CloudFormation for Amazon RDS for SQL Server deployments

Amazon Relational Database Service (Amazon RDS) for SQL Server makes it easy to set up, operate, and scale SQL Server deployments in the AWS cloud. It’s a fully managed, durable database built for enterprise workloads at scale and frees you up to focus on application development.

Amazon RDS is integrated with AWS CloudFormation which supports creating, provisioning, and configuring Amazon RDS resources. With CloudFormation templates, you can manage RDS for SQL Server instance lifecycles in a repeatable, predictable, and safe way, while allowing for automatic rollbacks, automated state management, and management of instances across accounts and Regions.

In this post, we show you how to create Amazon RDS for SQL Server instances using a AWS CloudFormation stack.

CloudFormation templates for SQL Server

Before we get started, let’s dive deep into understanding AWS CloudFormation resource properties for Amazon RDS for SQL Server.

The AWS::RDS::DBInstance resource is where you define properties of the host on which the database runs, for example the instance size. This resource creates a new RDS for SQL server instance.

In this section, we explore some of the key properties for this resource.

EngineVersion

EngineVersion is the version number of the database engine to use. The documentation provides the list of supported versions in RDS for SQL Server.  The following code example sets the engine version to SQL Server Enterprise Edition 2019:

Engine: sqlserver-ee

EngineVersion: 15.00.4236.7.v1

DBInstanceClass

Use the DBInstanceClass property to specify the compute and memory capacity of the database host, for example db.m6i.xlarge. This is helpful to deploy different instance classes during deployment, and in the future, it allows you to make instance class changes (with AWS CloudFormation template updates) independent of each other. The following code example sets the instance class to db.m6i.xlarge:

DBInstanceClass: ‘db.m6i.xlarge’

DBInstanceIdentifier

DBInstanceIdentifier is the name of the database instance. If you don’t specify a name, AWS CloudFormation generates a unique physical ID and uses it as the name.

Be aware that if you use this property to specify a custom name for an instance, you can’t perform updates (with AWS CloudFormation) that require replacing the instance. You can use this property as a protection mechanism. If you attempt to update the CloudFormation template and didn’t realize that one of the properties requires instance replacement, the update fails because you used a custom name for the instance.

AllocatedStorage

The amount of storage size in gibibytes (GiB) to be initially allocated for the database instance.

PrimaryDBInstance:
Type: "AWS::RDS::DBInstance"
Properties:
AllocatedStorage: 20

StorageType

Specifies the storage type to be associated with the DB instance. The default value is gp3.

StorageType:
Type: String
Description: Instance storage type for the RDS SQL Server instance.
Default: gp3

EnvType

Specifies the Environment to create the stack. Example production(default) or test. The following code snippet provides an option for customers to launch the stack.

EnvType:
Default: prod
Type: String
AllowedValues:
- prod
- dev

DatabaseVpcId

You can optionally request an IPv6 CIDR block for the VPC. You can request an Amazon provided IPv6 CIDR block from Amazon’s pool of IPv6 addresses, or an IPv6 CIDR block from an IPv6 address pool that you provisioned through bring your own IP addresses (BYOIP). The
following link provides more details regarding ec2 VPC.

VPCIDCIDR

Adds an inbound rule to a security group. An inbound rule permits instances to receive traffic from the specified IPv4 or IPv6 CIDR address range, or from the instances associated with the specified security group.

VPCIDCIDR:
Type: String
Description: VPC CIDR, used for DB security group

Enhanced Monitoring

Property MonitoringInterval: '60' automatically enables Enhanced Monitoring for production type of environment with 60 seconds granularity so that you can view real time OS metrics and troubleshoot database performance issues.

Performance insights

Property EnablePerformanceInsights: true automatically enables Performance Insight and configures performance insight data encryption using a customer-managed AWS Key Management Service (AWS KMS )encryption key. The retention setting in the free tier is Default (7 days). To retain your performance data for longer, specify 1–24 months. For more information about retention periods, see Pricing and data retention for Performance Insights. Performance insights helps to quickly detect database performance problems and determine when and where to take action.

The following code example enables the Enhanced Monitoring and Performance on the RDS for SQL Server Instance and this template will create a monitoring role for the enhanced monitoring.

MonitoringInterval: '60'
MonitoringRoleArn: !GetAtt SampleCloudFormationDBMonitorRole.Arn
EnablePerformanceInsights: true

AWS Secrets Manager

AWS Secrets Manager helps you protect access to your applications, services, and IT resources. This service enables you to rotate, manage, and retrieve database credentials, API keys, and other secrets throughout their lifecycle.

The following code example automatically creates an AWS Secret on the RDS for SQL server Instance.

MyRDSSecrets:
Type: AWS::SecretsManager::Secret
Properties:
Description: 'This is the secret for my RDS instance'
GenerateSecretString:
SecretStringTemplate: '{"username": "admin"}'
GenerateStringKey: 'password'
PasswordLength: 16
ExcludeCharacters: '"@/\'

You can find more details on how to retrieve the plain text for AWS Secret in the section

Connect to the RDS for SQL Server DB instance

Parameters

Use the optional Parameters section to customize your templates. The following snippet shows an example to input custom values to your template each time you create or update a stack.

Parameters:
SqlServerInstanceName:
NoEcho: 'false'
Description: RDS SQL Server Instance Name
Default: SQLServerCFNPrimaryInstance
Type: String
MinLength: '1'
MaxLength: '63'
AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
InboundIpAddr:
Type: String
Description: IP range to allow access to the RDS SQL Server instance.
InstanceType:
Type: String
Default: db.m6i.xlarge
Description: Instance type for the RDS SQL Server instance.
StorageType:
Type: String
Description: Instance storage type for the RDS SQL Server instance.
Default: gp3
AllocatedStorage:
Type: String
Description: Instance allocated storage type for the RDS SQL Server instance.
Default: 20

SQLServerSecurityGroup

SQLServerSecurityGroup is the name of parameter used to create or map security group while launching RDS for SQL Server instance through CloudFormation. The following code example creates the SQL Server Security Group on the RDS for SQL Server Instance that allows traffic on port 1433.

SQLServerSecurityGroup:
Type: AWS::EC2::SecurityGroup
Properties:
GroupDescription: SQL Server Security Group
SecurityGroupIngress:
- IpProtocol: tcp
FromPort: '1433'
ToPort: '1433'
CidrIp: !Ref InboundIpAddr

Note: The CloudFormation template generates a new security group whenever the stack is deployed. As an alternative, you can specify an existing security group by name to reuse it. Grant the user deploying the CloudFormation stack with ability to describe VPC. Refer to Granting Users Permission to Work in the Amazon EC2 Console to learn more.

Solution overview

To implement the solution, we complete the following steps:

  1. Launch a CloudFormation stack, which creates the RDS for SQL Server instance.
  2. Connect to the new RDS for SQL Server DB instance.

Prerequisites

Before we begin, we assume that you have the following prerequisites are met:

  • Existing AWS account with VPC configured
  • Basic knowledge of CloudFormation templates
  • Ensure user has minimal privileges to launch an RDS instance.
  • Basic knowledge of SQL Server Management Studio (SSMS)

For more details, refer to Creating an Amazon RDS DB instance and Connecting to a DB instance running the Microsoft SQL Server database engine.

This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

We strongly recommend that you set up this in a non-production instance and run the end-to-end validations before you implement this solution in a production environment.

Create the CloudFormation stack

To provision your resources with AWS CloudFormation, complete the following steps:

1. Choose Launch Stack. This button automatically launches the AWS CloudFormation service in your AWS account with a template to launch.

  1. Choose Next.
  2. For Stack name, enter a name. In this example, we use the name SQLCFNTemplate.
  3. For SqlServerInstanceName, enter the RDS for SQL Server instance name (the default is SQLCFNDBInstance).
  4. For DatabaseUsername, enter the database admin account user name.
  5. For DatabasePassword, enter the database admin account password. Alternatively you can use AWS Secrets Manager to generate a random password.
  6. Choose Next.

  1. On the Configure stack options page, for Tags, specify any optional tags.
  2. Choose Next.

  1. Choose Next again.
  2. On the review page, check to confirm all the settings are correct.
  3. Choose Create stack. The AWS CloudFormation Stack launch would approximately take 1 hour.
  4. When the stack creation is complete, navigate to the stack (SqLCFNTemplate) and choose the Resources tab to review all the resources that were created as part of this AWS CloudFormation template.

Connect to the RDS for SQL Server DB instance

To connect to your DB instance, complete the following steps:

  1. On your Windows EC2 instance, open SSMS, then choose Connect, and choose Database Engine. You can refer to link for more details on how to connect from EC2 instance to your RDS instance.
  2. For Server name, you can obtain from the Amazon RDS console, login, and password details from the AWS Secrets Manager console. Refer to Retrieve secrets from AWS Secrets Manager for the steps on how to retrieve Secrets from AWS Secrets Manager.
  3. Copy the plaintext value of the secret and provide it in the SQL Server password section. Choose Connect

After the connection is successful, you can see the RDS instance, as shown in the following screenshot.

Clean up

If you no longer require this setup and want to avoid future charges, you can delete the resources that you created as part of this setup, namely the RDS for SQL Server and Windows EC2 instances.

To delete all other resources that were launched as part of the AWS CloudFormation stack, complete the following steps:

  1. On the AWS CloudFormation console, choose Stacks in the navigation pane.
  2. Choose the stack you created, then choose Delete.
  3. Choose Delete stack when prompted.

For more information, refer to Deleting a stack on the AWS CloudFormation console.

Summary

In this post, we explained how to launch Amazon RDS for SQL Server using a CloudFormation template. We performed an RDP connection to the Windows EC2 instance, and from there we connected to Amazon RDS for SQL Server using SSMS.

Try out the solution and if you have any comments or questions, leave them in the comments section.

About the Authors

Kiran Mulupuru is a Database Specialist Technical Account Manager at Amazon Web Services. She focuses on Amazon RDS and Amazon Aurora databases. She works with enterprise customers, providing technical assistance on database operational performance and sharing database best practices.

Sapna Thakur is a Sr. Technical Account Manager at AWS. She works with AWS customers to help understand their business and technical needs, align technical solutions, and achieve the greatest value from AWS.

Harish Bannai is a Sr. Technical Account Manager at AWS. He works with enterprise customers providing technical assistance on RDS, Database Migration services operational performance and sharing database best practices.