AWS CLI Syntax and Examples to Create Amazon RDS SQL Server Instance

By:   |   Updated: 2022-11-23   |   Comments   |   Related: > Amazon AWS RDS


Problem

Amazon Web Services provides a Relational Database Service (AWS RDS) for hosting databases in the cloud. The prior tip, Steps to Quickly Configure an AWS RDS SQL Server instance, uses Amazon Web Portal for deploying the RDS instance.

In addition to using the web portal, you can also deploy RDS for SQL Server using Command Line Interface, which we will cover in this tutorial.

Solution

The AWS Command Line Interface (CLI) is an Amazon command-line shell for deploying various AWS services in Windows and Linux environments. In the last tutorial, Configure AWS Command Line Interface utility, we learned the following:

  • What is AWS CLI?
  • How to install AWS CLI on Ubuntu?
  • Configure AWS CLI using access and secret keys
  • Demo script

This tutorial will use the AWS CLI script for deploying the RDS instance. Therefore, before proceeding, you must install and configure AWS CLI 2.0 in your environment.

Additionally, I would recommend reading the following tutorial:

Use AWS CLI to Deploy RDS SQL Server

To create the RDS instance using AWS CLI, we use the sub-command create-db-instance. The create-db-instance requires the following parameters:

--db-name

Enter the database engine we want to deploy in RDS. It accepts the following values:

  • SQL Server
  • MySQL
  • MariaDB
  • PostgreSQL
  • Oracle
  • Amazon RDS Custom for SQL Server
  • Amazon RDS Custom for SQL Server
  • Amazon Aurora PostgreSQL
  • Amazon Aurora MySQL

--engine

The next section is to choose the type of database engine to be used for this instance. For SQL Server, it has the following values:

  • Express edition: sqlserver-ex
  • Web edition: sqlserver-web
  • Standard edition: sqlserver-se
  • Enterprise edition: sqlserver-ee

--engine-version

It is the database engine version, including Major versions such as SQL Server 2019, 2017, 2016 and Minor versions such as Service packs and cumulative packs. You can refer to Microsoft SQL Server versions on the Amazon RDS article.

For this tip, I will use SQL Server 2019 CU15, i.e., 15.00.4198.2. The equivalent CLI RDS version is 15.00.4198.2.v.

--db-instance-identifier

This parameter is to specify the RDS instance name.

  • Use lowercase string
  • Always use the first character as a letter
  • It can contain 1 to 63 letters, hyphens, or numbers.

--master-username

The master user name for connecting to the RDS instance. RDS uses the default user name as admin.

--master-user-password

The password for the master user is specified above. We cannot use / " @ characters in the password.

--db-instance-class

The db instance class defines the compute and memory capacity of the AWS RDS instance. AWS supports general-purpose, memory-optimized, and burstable performance instance types. Refer to the DB instance classes document for a list of supported RDS for the SQL server.

--storage-type

We need to specify the storage type associated with the RDS DB instance. The supported values are:

  • General Purpose SSD (gp2)
  • Provisioned IOPS SSD (io1)
  • Magnetic

--allocated-storage

The storage size you want to assign to the RDS instance.

  • Minimum size: 20 GiB
  • Maximum size: 16384 GiB

--vpc-security-group-ids

The VPC refers to the networking component in AWS services. You can specify a VC group or use the default EC2 VPC security group.

--db-subnet-group-name

VPC is associated with a subnet group. We can specify the subnet group name using the db-subnet-group-name parameter in the AWS CLI command.

--publicly-accessible and --no-publicly-accessible

The RDS SQL Server allows connection to the RDS instance from the EC2 instance and services inside the CPV. However, you can assign the public IP address to enable connections outside VPC or over the internet.

  • Use --publicly-accessible to allow connection over the internet
  • Use --no-publicly-accessible to restrict connections within VPC

The default public access is set to No. Click on Yes for public access in the web portal.

--DBParameterGroups

The parameter group in the RDS instance defines the SQL instance configurations. You cannot make modifications to the default parameter group. Therefore, if you want to change any configurations, create a new parameter group and assign it to the RDS.

If you have a custom parameter group, enter the parameter group name here.

The RDS portal shows the default parameter group based on SQL Server version and edition. You can choose the custom parameter group (created before) from the drop-down.

--option-group-name

AWS RDS instance uses an options group to enable features such as integration service or reporting service. Like the DB parameter group, you can create a new option group and assign it to the RDS.

--backup-retention-period

AWS uses automatic backups, and the retention period can be between 1 to 35 days. The default backup retention period is 7 days. The value 0 disables the automatic backups. Specify the duration with the parameter if you want a backup retention period other than the default value.

--PreferredMaintenanceWindow

You can specify the time range for the system maintenance. Here, set the time in Universal Coordinated Time (UTC).

--auto-minor-version-upgrade

By default, RDS installs the minor SQL version upgrades automatically. If you do not want to use the automatic minor version upgrade, use the parameter -no-auto-minor-version-upgrade.

Examples to Create RDS Instance Using AWS CLI

The AWS portal allows you to choose a maintenance window, as shown below.

Let's build the AWS CLI script to create a new RDS SQL Server with the above parameters.

Example 1: Deploy AWS RDS Instance

Deploy the AWS RDS instance with the following requirements:

  • SQL Server Express with 14.00.3281.6.v1 edition
  • RDS instance name: sqlserverrds
  • Allocated storage: 20GB
  • Db instance class: db.t3.small
  • Master username: admin
  • Master user password: admin1234
  • Backup retention period: 0 (disable backups)
  • Port: 1433
  • Instance should be publicly accessible

Use the below script to pre-configure the AWS CLI terminal:

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.t3.small --master-username admin --master-user-password admin1234 --backup-retention-period 0 --storage-type standard --port 1433 --publicly-accessible

It deploys the RDS instance in your default AWS region with the specified configuration. The AWS CLI returns the output in JSON format, as shown below.

Example 1 output in JSON

Example 2: Deploy AWS RDS Instance

Deploy the AWS RDS instance with the following requirements:

  • SQL Server Express with 14.00.3281.6.v1 edition
  • RDS instance name: sqlserverrds
  • Allocated storage: 20GB
  • Db instance class: db.t3.small
  • Master username: admin
  • Master user password: admin1234
  • Backup retention period: 15 days
  • Port: 1433
  • Instance should not be publicly accessible
  • PreferredMaintenanceWindow: Tue:04:00-Tue:04:30
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.t3.small --master-username admin --master-user-password admin1234 --backup-retention-period 0 --storage-type standard --port 1433 --no-publicly-accessible --preferred-maintenance-window Tue:04:00-Tue:04:30

Example 3: Specify VPC Security Group and Subnet Group in RDS

We can specify the VPC security group using the vpc-security-group-ids and db-subnet-group, as shown below.

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.t3.small --master-username admin --master-user-password admin1234 --backup-retention-period 0 --storage-type standard --port 1433 --no-publicly-accessible --preferred-maintenance-window Tue:04:00-Tue:04:30 --vpc-security-group-ids mysecuritygroup --db-subnet-group mydbsubnetgroup

Example 4: Disable Auto Version Upgrade Feature of RDS Deployment

In the example 4 script, we can specify the parameter --no-auto-minor-version-upgrade for disabling auto version upgrade in the 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.t3.small --master-username admin --master-user-password admin1234 --backup-retention-period 0 --storage-type standard --port 1433 --no-publicly-accessible --preferred-maintenance-window Tue:04:00-Tue:04:30 --vpc-security-group-ids mysecuritygroup --db-subnet-group mydbsubnetgroup --no-auto-minor-version-upgrade
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-11-23

Comments For This Article