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
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
- Set your current PowerShell session to run commands under particular region.
Set-AWSCredentials -ProfileName AWSRDS_AvanishPanchal
- 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*"}
- Result contains 4 SQL instances running with SQL Server 2014 version.
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
