Rajendra Gupta
Engine options AWS RDS SQL Server

Deploying an AWS RDS SQL Server using the AWS CLI

June 19, 2020 by

This article deploys a new AWS RDS SQL Server using the AWS CLI command: create-db-instance.

Introduction

Amazon Web Services (AWS) gives a managed database service RDS for Microsoft SQL Server. Previously, we deployed the RDS instance using the AWS console. Suppose you deploy RDS instance frequently, it might be cumbersome for you to go through console RDS wizard, choose required options and deploy it. We can stop RDS service when it is not being used, but it can be stopped for a maximum of seven days. RDS automatically starts the RDS instance for maintenance purposes. In this case, you can create the lambda function to start and stop SQL instances automatically.

In my case, usually, I drop RDS instance after preparing a demo for my article. You can also terminate an RDS instance after completing your testing. In this case, it is a wise decision to use AWS CLI scripts and configure your instances.

The following articles can be helpful in understanding RDS SQL Server:

In this article, we will deploy an AWS RDS SQL Server using the AWS CLI script.

Pre-requisites

  • Install the AWS CLI version 2 and configure the CLI profile: You should install CLI version 2 in your local system or the EC2 instance. You can refer to this article Learn AWS CLI: An Overview of AWS CLI (AWS Command Line Interface) for it.
  • We use the Windows command prompt in Windows OS to execute CLI queries. You can also use AWS Shell to write the CLI queries interactively. You can refer to the AWS blog for more details on the AWS Shell console.

AWS Shell console

Using the AWS CLI to deploy an AWS RDS SQL Server

We will use the CLI command create-db-instance to deploy RDS instances. We can deploy all supported RDS databases using this command. I will go through the option in AWS Web console and its similar argument in the CLI create-db-instance command.

Engine options

In the AWS console RDS wizard, you need to select the engine type and its corresponding edition. For SQL Server, we have the following supported editions.

Engine options AWS RDS SQL Server

In the below table, we can see the CLI argument for the SQL Server edition. For example, if we want to create a new RDS instance for SQL Server express edition, we specify sqlserver-ex.

CLI argument

SQL Server edition

Equivalent CLI option

Engine

Express edition

sqlserver-ex

Web edition

sqlserver-web

Standard edition

sqlserver-se

Enterprise edition

sqlserver-ee

Version

AWS RDS supports different SQL Server versions from 2012 to 2017 along with different levels. You need to specify which version you require in the new AWS RDS SQL Server. We need to select the version depending upon the features. We also need to consider that RDS does not support all SQL Server features. Features such as Data Quality Services, log shipping, file table, maintenance plans are not supported.

RDS version

For my demonstration, I choose SQL Server version 14.00.3281.6.v1. In the CLI, we need to specify the version in the format as 14.00.3281.6.v1.

You can refer to SQL Server Versions Supported in RDS for a complete list of supported SQL versions and their similar RDS argument.

DB instance identifier

It is the name for your DB instance. It is a unique name for all databases in the AWS account.

  • Its name must contain a minimum of 1 and a maximum of 63 letters, numbers or hyphens
  • Its first character must be a letter
  • You cannot use two consecutive hyphens
  • The instance name must not end with a hyphen

DB instance identifier

In the CLI command, we will use argument –db-instance-identifier along with DB instance name.

DB credentials

We need a master username and password for the RDS instance. By default, RDS uses the admin username. This admin user has the maximum permissions for your instance. In the web console, you specify these details in the credential settings page.

DB credentials

In the AWS CLI, we specify the following arguments.

  • –master-username: Specify the master username
  • –master-user-password: Specify the password for the admin user

DB instance size

It is the CPU and memory capacity for your RDS server. AWS provides various DB instance class depending upon the CPU and memory configurations. Your AWS RDS SQL Server cost also varies depending upon the instance class you have selected.

DB instance size

We can use argument –db-instance-class in the CLI command to specify the DB instance class. For a free-tier account, we can use db.t2.micro having 1 vCPU and 1 GiB memory. I will specify value db.t2.micro for the –db-instance-class argument. You can refer to AWS docs to understand all Available DB Instance Classes.

Storage type and size

AWS RDS SQL Server supports two storage types.

  • General-purpose (SSD): It is useful for most of the workloads and provides up to 3000 IOPS. It is default storage provided for RDS and is cost-effective storage
  • Provisioned storage (SSD): it is suitable for IO intensive workloads

We can use a maximum of 16TiB of storage in any of the storage classes specified above. It uses a minimum of 20GiB of storage.

DB storage

In the CLI command, we use the following arguments for storage class and size.

  • –storage-type: specify Standard or GP2 for storage class
  • –allocated-storage: specify your storage size as per your database size. We specify the size in GiB unit

Virtual Private Cloud (VPC)

VPC is a networking component of the AWS. It is a crucial parameter while creating the RDS instance. In the below screenshot, we can see the default VPC for the new RDS instance.

  • Note: We cannot modify VPC after a database is created in AWS

Virtual Private Cloud (VPC)

We will use argument –vpc-security-group-ids for the RDS instance VPC.

Subnet group

Each VPC is associated with the subnet group. Specify the subnet group for your RDS instance using CLI argument –db-subnet-group-name

Subnet group

Publicly Accessible

By default, AWS RDS SQL Server allows connection to your database from the Amazon EC2 and other services inside your VPC. We can allow connections from outside VPC to allocate a public IP address to it. I want to connect my RDS from the laptop outside the VPC network, therefore change the configuration using the –publicly-accessible. If you do not want public access, you can use argument –no- publicly-accessible

Publicly Accessible

DB parameter group

We can define a parameter group for the RDS instance. A parameter group is a set of SQL instance configurations. By default, RDS creates a default parameter group and assigns it to your instance. We cannot modify the default parameter group. You can create a new custom group with configurations and apply them to the DB parameter group.

If you use a custom parameter group, you can use the argument –- DBParameterGroups to specify it.

DB parameter group

DB option group

AWS RDS uses the option group to enable certain features such as integration service, reporting service. We can create a custom option group and assign it to the RDS instance using CLI argument –option-group-name.

DB option group

Automatic backups and backup retention period

AWS RDS SQL Server automatically takes the database backup and maintains for you as per defined backup retention period.

Automatic back and backup retention period

We can choose the backup retention period as per our requirement. By default, RDS uses 7 days retention period. In the AWS CLI, we will use –backup-retention-period argument to specify the number of days (positive number only). For my demo RDS instance, I do not want any backups, so I specified the value as zero. The zero value in this argument tells RDS to disable automatic backups.

TimeZone

If you need a specific requirement for the time zone and collation, we can specify them while creating the RDS instance. By default, it uses Universal Coordinated Time (UTC).

You can refer to the article, Local Time Zone for Microsoft SQL Server DB Instances for supported time zones and use –timezone to specify required time zone other than a default time zone.

TimeZone

We can combine the arguments for the CLI command rds create-db-instance and execute them to create a new AWS RDS SQL Server instance.

>aws rds create-db-instance –engine sqlserver-ex –engine-version 14.00.3281.6.v1 –db-instance-identifier sqlserverrds –allocated-storage 20 –db-instance-class db.t2.micro –db-subnet-group default-vpc-9c710fe6 –master-username admin –master-user-password admin1234 –backup-retention-period 0 –storage-type standard –port 1433 –publicly-accessible –profile production

This command gives the output in a JSON format and returns all configurations with default and custom values for your RDS instance.

AWS console output

Similarly, in the below screenshot we get configurations like MultiAZ, Engine version, read replica, option group.

AWS console output details

You can connect to the AWS web console and refresh the RDS dashboard. You can see that the new instance is being created.

Refresh the RDS dashboard

If you do not need this instance, you can select it and delete the instance from the Actions menu.

delete the instance

It asks you a confirmation, and you need to enter delete me keyword as a token of confirmation.

Confirmation prompt

It starts deleting the AWS RDS SQL Server. It takes a few minutes, and after that, you can refresh the RDS dashboard. It removes the entry for deleted RDS.

deleted instance

Conclusion

In this article, we deployed a new AWS RDS SQL Server using AWS CLI commands. We simulated the web console RDS wizard with a similar argument of CLI. It is an excellent way to use the CLI command to be efficient, productive by saving time and deployment efforts.

Rajendra Gupta
AWS RDS

About Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features. While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs. He can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

95 Views