SQL Server RDS services through AWS PowerShell

Hello Friends!

Querying SQL Server RDS Instances through PowerShell

It’s been a while I’m working with AWS RDS (Relations Database Services) and playing around with SQL Server. Hopefully you enjoyed my previous blog about parameters to keep in mind while setting up SQL Server instance in AWS (Amazon Web Services) cloud environment.

While playing around AWS RDS for SQL Server found very limited set of documentation available on web. Either is terms of how to connect SQL Server RDS from PowerShell (spend most of my time there) or work smartly with SQL Server RDS with the help of PowerShell. Being honest to you, into industry over a decade and PowerShell is my favorite tool till now. So my day in day out activities are relying of it.

Anyways, this blog will helps how to find SQL Server RDS instances hosted in large AWS environment and filter respective SQL instances and their configuration parameters to list down inventory per choice.

But before that need to understand few pieces.

  • How to use AWS Login Credentials in PowerShell Environment
  • How to use AWS Login Credentials to Map a particular region where SQL Server RDS are hosted.
  • How to use AWS Login Credentials for all sessions.

In simple words, how most of us usually connect a SQL (RDS) instance in GUI (SSMS – favorite tool) and perform day to day tasks, going to do pretty much same with help of PowerShell (CUI).

Tried to put forwards whole analogy with the help of a diagram. Explaining how things are connect to each other and make an environment to work nicely with SQL Server RDS (or others) through PowerShell.


List of Steps

  • Create AWS PowerShell profile.
Set-AWSCredentials -AccessKey ABCDEFGHJ7YV8765VCJK -SecretKey BhYTTnnj/opsBtNTyUUiK3458J347/to+2ABCc -StoreAs AWSRDS_AvanishPanchal

1 - create profile
Note: Aforesaid key(s) are sample.

-AccessKey & -SecretKey – probably needs a request from your company’s support group performs IAM roles.
Length of –AccessKey = 20 letters (alphanumeric w/o special characters)
Length of –SecretKey = 40 letters (alphanumeric w/ only 1 special character i.e. /)

Map profile to default region (where SQL Server RDS are hosted)

Initialize-AWSDefaults -ProfileName AWSRDS_AvanishPanchal -Region us-east-1

2 - Set default Region

  • Set your current PowerShell session to run commands under particular region.
Set-AWSCredentials -ProfileName AWSRDS_AvanishPanchal

3 - profile

  • Environment is now setup and let’s proceed to query how my SQL Server RDS instances are hosted in AWS RDS environment.
Get-RDSDBInstance | Select DBInstanceIdentifier,Engine,EngineVersion | where-object {$_.Engine -like "*sqlserver*"}

4 - Query RDS instances

  • Result contains 4 SQL instances running with SQL Server 2014 version.

4 - Query RDS instances 2

There are many more parameters. Ease to include and query more detailed information about SQL Server RDS Instance hosted in AWS environment. I’m more than willing to continue writing under this space. Stay tuned, many more to come.
Happy Learning!

Avanish Panchal
Regional Head – DataPlatformGeeks & DPS2017 Core Team Member
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Avanish Panchal on Twitter | Follow Avanish Panchal on FaceBook


About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published.