Using Amazon Web Services (AWS), we can use their Relational Database Service (RDS) to deploy an MS-SQL database quickly and easily using PowerShell. Let's jump in and see how!

Before we get too far, make sure you have the AWSPowerShell module installed by running Install-Module -Name AWSPowerShell. Also make sure you have already authenticated to your AWS account.

The first task we'll need to do is figure out which "engine" we need to use. An "engine" in RDS terms is simply the server type, so think of SQL Server, Postgres, etc. The way to get all the available engines is by using the Get-RDSDBEngineVersion command. Since this command returns lots of output, and right now, I'm only interested in the engine names, I can group the output by Engine.

Get-RDSDBEngineVersion | Group-Object -Property Engine
Viewing engine types

Viewing engine types

For our purposes, I want just to use the SQL Server Express Engine since it's free! We're not going to be doing anything crazy at this point. If you look down the list of engines returned from the command above, you'll notice sqlserver-ee, -ex, -se, and -web. These designate Enterprise, SQL Server Express, Standard Edition, and Web Edition; -ex is SQL Server Express Edition, so this will be our engine.

Once we figure out the engine to use, we'll then need to figure out the engine version. To do this, we can use the Get-RDSDBEngineVersion command again. This time, we're going to use the Engine parameter and pass the engine name sqlserver-ex. This returns a list of all the engine versions we can use.

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

By default, the new New-RDSDBInstance command will create a database using the latest version, but if you need an older version, you can choose from the list returned. At the time of this writing, this version is 14.00.3035.2.v1.

Next, we'll need to know the instance class to create. Unfortunately, this list isn't available via PowerShell, but you can refer to the RDS instance types page at AWS for the complete list. For our scenario, I'll be using db.t2.micro.

Once we've got all of these parameters together, we're now ready to create the database instance using the New-RDSDBInstance command. To do this, we pass all the parameters we just figured out along with the username, password, and size of the database to create. You can see below I'm also optionally making the instance accessible over the internet, but this isn't mandatory.

$parameters = @{
    DBInstanceIdentifier = 'TechSnips2'
    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

Once this command finishes, you'll see a lot of output. One of the properties is the DBInstanceStatus property. At this time, it's in a creating state.

Instance in a creating state

Instance in a creating state

Before using the instance, we're going to need to wait for it to finish. To do this, we can use the Get-RDSDBInstance command using the DBInstanceIdentifier parameter and inspecting the DBInstanceStatus property. When running this, you'll find this returns a string of creating if you're following along.

I'd rather not run this command over and over waiting forever, so I'll just create a PowerShell while loop that will run continuously until the state shows up as available.

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

When the above code runs, it will halt the console or script (if you're running this in a script) until the instance status goes to available.

Subscribe to 4sysops newsletter!

After this while loops returns control, you've deployed your MS-SQL RDS instance!


Leave a reply

Your email address will not be published.


© 4sysops 2006 - 2022


Please ask IT administration questions in the forums. Any other messages are welcome.


Log in with your credentials


Forgot your details?

Create Account