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:
- Introduction to AWS RDS SQL Server Features
- Steps to Quickly Configure an AWS RDS SQL Server instance
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 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
- Connect to the RDS instance and start running your SQL queries.
- Read the AWS CLI documentation for more details.
- Read SQL Server Amazon AWS Tips on MSSQLTips.
