X
Popular Searches

How to Deploy a MSSQL Database Using Amazon Web Services (AWS) RDS and PowerShell

Relational databases being managed in the cloud.

Amazon Web Services (AWS)’s solution to the relational database is called Amazon Relational Database (RDS). RDS is an AWS service that enables you to manage relational databases in the cloud. RDS provides various kinds of databases, but the one you’re going to learn how to deploy today is MSSQL.

Like many other AWS services, you can manage RDS databases in a few different ways, such as through the AWS Management Console, AWS CLI tool, any number of SDKs, or with PowerShell. The choice of which method is best is yours. (If you’re on a team with PowerShell gurus or already have some AWS automation scripts written in PowerShell, a PowerShell script can get the job done well though.)

In this article, you’re going to learn how to use PowerShell and the AWS PowerShell module to deploy an AWS RDS instance in no time!

Prerequisites

To follow the steps in this article, you must first ensure that you’ve met a few assumed prerequisites. I expect that you already met these requirements:

  1. Created an AWS account
  2. Are using PowerShell Core (I’m using PowerShell 6.2.0.)
  3. Have downloaded and installed the AWS.Tools.RDS PowerShell module  (I’m using v3.3.590.)
  4. Authenticated to your AWS account the root user or an IAM user with rights to create an RDS instance

Once you’ve confirmed that you meet all of these requirements, let’s get started!

Deciding What Kind of RDS Instance to Deploy

Before you begin coding, you must figure out what kind of RDS instance to create. To deploy an RDS instance with PowerShell, you are required to know a few keys attributes:

  • Name of the instance
  • Engine (SQL Server, MariaDB, MySql, and so on)
  • Instance class that assigns what kind of resources the SQL Server database will be run on
  • Master username and password
  • How big the database should be in gigabytes

This article explains how to find each of these attributes to create an RDS instance with PowerShell.

Finding Available Engine Versions

While you are creating an MSSQL RDS instance using this article, it’s essential to know you have other options. Run the Get-RDSDBEngineVersion command and group by engine name to see a list of available database types.

PS> Get-RDSDBEngineVersion | Group-Object -Property Engine

Count Name                      Group
----- ----                      -----
   11 aurora                    {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   11 aurora-mysql              {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   11 aurora-postgresql         {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
    1 docdb                     {Amazon.RDS.Model.DBEngineVersion}
   19 mariadb                   {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   27 mysql                     {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
    1 neptune                   {Amazon.RDS.Model.DBEngineVersion}
   42 oracle-ee                 {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   20 oracle-se                 {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   20 oracle-se1                {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   21 oracle-se2                {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   55 postgres                  {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   18 sqlserver-ee              {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   19 sqlserver-ex              {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   19 sqlserver-se              {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion, Amaz…
   19 sqlserver-web             {Amazon.RDS.Model.DBEngineVersion, Amazon.RDS.Model.DBEngineVersion

Because you have deployed an MSSQL RDS instance, research that version bit more to see all of the available versions.

Get-RDSDBEngineVersion -Engine 'sqlserver-ex' | Format-Table -Property EngineVersion

Finding the Instance Class

An RDS instance needs an instance class, which defines the kind of resources allocated to run the instance. Unfortunately, no way exists to find available instances with PowerShell. Instead, you can go to the Amazon RDS Instance Types page to review your options.

Creating the RDS Instance

By now, you should have the name of the RDS instance you’d like to create, the engine, instance class, and the username/password to assign to it. It’s now time to create it.

To create a new RDS instance with PowerShell, use the New-RDSDBInstance cmdlet. This single cmdlet enables you to pass all of the parameters you need to create an instance.

Below, you can see an example of using the New-RDSDBInstance cmdlet to create an instance with:

  • A name of CloudSavvy
  • The MSSQL database engine (SQL Express latest version)
  • A db.t2.micro instance class
  • An sa user with a password of password
  • 20 GB in size
  • An interface that enables connections over the Internet

In the example, the output from the command is getting assigned to a variable called $instance.

$parameters = @{
    DBInstanceIdentifier = 'ClouddSavvy'
    Engine = 'sqlserver-ex'
    DBInstanceClass = 'db.t2.micro'
    MasterUsername = 'sa'
    MasterUserPassword = 'password' ## Do not to include a forward slash, @ symbol, double quotes or spaces
    AllocatedStorage = 20 ## Gigabytes
    PubliclyAccessible = $true ## to connect over the Internet
}
$instance = New-RDSDBInstance @parameters

When the New-RDSDBInstance command runs, it returns control to the PowerShell console before the instance is created. To monitor the creation process, you can monitor the DBInstanceStatus property returned by Get-RDSDBInstance.

PS62> (Get-RDSDBInstance -DBInstanceIdentifier $instance.DBInstanceIdentifier).DBInstanceStatus
creating

By using a little PowerShell magic with a while loop, you can create code that will wait for the instance to enter the available status before releasing control.

In the following example, PowerShell checks for the status of available every 30 seconds. As soon as the instance enters this state, it releases control.

while ((Get-RDSDBInstance -DBInstanceIdentifier $instance.DBInstanceIdentifier).DBInstanceStatus -ne 'available') {
    Write-Host 'Waiting for instance to be created...'
    Start-Sleep -Seconds 30
}

Once PowerShell releases control, you can then check the AWS Management Console and confirm the RDS instance has been created. To do so, you can either navigate to the RDS service section or follow this link, but be sure to replace it with the region name with which you’re working:

https://console.aws.amazon.com/rds/home?region=#dbinstances:

Summary

By following a step-by-step process, you now know how to deploy an AWS RDS instance with PowerShell. And, by using the code discussed in this article, you are able to use this in larger automation scripts or create an instance via the PowerShell console as you wish.

Adam Bertram Adam Bertram
Adam Bertram is a 20+ year veteran of IT and an experienced online business professional. He’s a consultant, Microsoft MVP, blogger, trainer, published author and content marketer for multiple technology companies. Catch up on Adam’s articles at adamtheautomator.com, connect on LinkedIn, or follow him on Twitter at @adbertram. Read Full Bio »

The above article may contain affiliate links, which help support CloudSavvy IT.