By: Edwin Sarmiento | Updated: 2023-07-12 | Comments | Related: 1 | 2 | > Amazon AWS
Problem
Our organization is looking to deploy virtual machines on AWS for both testing and DR environments. I want to create and administer AWS virtual machines running SQL Server using Windows PowerShell. How do I get started?
Solution
In a previous tip, Introduction to Azure PowerShell Modules for the SQL Server DBA Part 1, you looked at how to use the Azure PowerShell modules to start working with Microsoft Azure. If you have been using Windows PowerShell to manage your SQL Server databases and other products with PowerShell modules support, you already have the skills to start using PowerShell on AWS. If you are new to Windows PowerShell, check out this tip to get started.
Like the other products with PowerShell modules support, you need to install the PowerShell modules for AWS on your workstation to use them properly. Installing the AWS PowerShell modules is tricky. You can install the monolith AWSPowerShell modules that contain all the legacy Windows-specific AWSTools for PowerShell, which supports all AWS services. For security reasons, I'm a big fan of keeping my systems lean with minimal surface area. So, I only install the ones that I need. For this tip, you will use the AWS.Tools.Installer module to download and install specific PowerShell modules for the services that you need.
Use the Install-Module PowerShell cmdlet to download and install the AWS.Tools.Installer module. This PowerShell module makes it easier to install, update and uninstall all the AWS.Tools modules.
NOTE: Choose your favorite PowerShell scripting environment for writing scripts. I prefer using the PowerShell Integrated Scripting Environment (ISE) since it comes with the Windows operating system, and no additional installation is necessary. Azure Data Studio supports writing PowerShell scripts if you work on a Mac or Linux.
#MSSQLTips Install-Module -Name AWS.Tools.Installer
If prompted with the "Untrusted repository" message, click Yes to allow the installation. This will install the AWS PowerShell modules, allowing you to download and install specific AWS service-related PowerShell modules.
You can now install the AWS service-specific PowerShell modules you want to use using the Install-AWSToolsModule cmdlet. Since you will be deploying an EC2 instance running Windows Server and SQL Server, you will need the AWS.Tools.EC2 PowerShell module. Run the PowerShell command below to install the latest version of the AWS.Tools.EC2 PowerShell module. The -Cleanup parameter will uninstall older versions of the modules after a successful installation.
Install-AWSToolsModule AWS.Tools.EC2 -CleanUp
The good thing about installing the AWS service-specific PowerShell modules is that it also installs any dependent modules required for them to work. For example, when you downloaded and installed the AWS.Tools service module, it also installs the AWS.Tools.Common module, which is a shared module required by all AWS service modules. You don't have to worry about specific dependencies.
Exploring AWS Tools for PowerShell
Self-discovery is what makes PowerShell easy to use. You can start exploring the AWS Tools for PowerShell immediately after installation—no need to memorize cmdlet names and usage.
Run the PowerShell command below to display all of the AWS PowerShell cmdlets that have been installed. We are using the Group-Object PowerShell cmdlet to group the cmdlets according to their Source property, which happens to be the names of the AWS PowerShell modules.
Get-Command -Module AWS* | Group-Object -Property Source
Note that there are 605 PowerShell cmdlets available for working with EC2 instances in the current version of the module – v4.1.348. We can explore them in more detail by running the PowerShell command below.
Get-Command -Module AWS.Tools.EC2
You can also use the Get-AWSPowerShellVersion PowerShell cmdlet with the -ListServiceVersionInfo parameter to display all the supported services and APIs.
Get-AWSPowerShellVersion -ListServiceVersionInfo
Identifying and Setting the Default AWS Region While Working with Scripting Environment
Part of working with AWS services is knowing which region to which you want to deploy your workloads. Refer to this AWS documentation for guidance on choosing an AWS region for your workloads. Run the PowerShell command below to get a list of available AWS regions.
Get-AWSRegion
You want to ensure that you're only working with one region within your scripting environment so you don't end up with different AWS services and objects all over the world. The last thing you want is to have your AWS EC2 instances in the US and your EBS volumes in South America. Use the Set-DefaultAWSRegion PowerShell cmdlet to set the AWS region for the current session on your scripting environment. I'm using the US East (Ohio) region in the example below.
Set-DefaultAWSRegion -Region us-east-2
Don't be alarmed; you won't see any results after running the command. To verify that you set the proper AWS region, run the Get-DefaultAWSRegion PowerShell cmdlet.
Setting Credentials for Working with AWS
Much like working with your on-premises network resources, you need valid credentials – a local Windows account or an Active Directory domain account - to run PowerShell commands. Whenever you run the AWS Tools for PowerShell, an AWS credential is used to encrypt and secure the web service calls. Running AWS service-specific PowerShell cmdlets without valid credentials would return an error like the one below.
But to set the right credentials, you need to have an existing one first. Talk to your AWS administrator to provide you with an AWS account with the corresponding access keys. Refer to the AWS documentation AWS Account and Access Keys when you ask your AWS administrator for credentials.
Once you have your AWS credentials and access keys, run the Set-AWSCredential cmdlet below to set the credentials while working in your scripting environment, replacing the -AccessKey and -SecretKey parameter values as appropriate. The -StoreAs parameter is used to store the credentials as the profile named emsarmientoAWS.
Set-AWSCredential -AccessKey QKIMTX4DTYTOXSNAN5FGQ -SecretKey NRx943A9wKFuQ5O9il3WZchuyAqtis//tsWsxklf -StoreAs emsarmientoAWS
Similar to running the Set-DefaultAWSRegion PowerShell cmdlet, you won't see any results after running the Set-AWSCredential cmdlet. To verify that you assigned the AWS credentials to the specific profile name, run the Get-AWSCredential PowerShell cmdlet, passing the -ListProfileDetail parameter.
By default, the profile is created at C:\Users\<username>\AppData\Local\AWSToolkit. You can write the file to a specific location using the -ProfilesLocation parameter with the desired path. The credential file is stored with the filename RegisteredAccounts.json.
You can store multiple credentials to your scripting environment by assigning them to their own profiles. Doing so lets you switch security context to test role-based access control (RBAC) and access control lists (ACL). You can even pass specific profiles while running AWS service-specific cmdlets.
Run the Initialize-AWSDefaultConfiguration cmdlet below to set the default profile and region while working in your scripting environment. In this example, I'm using the profile emsarmientoAWS for the AWS credential and us-east-2 for the AWS region. Doing this allows you to use the profile with its credentials and stay within the US East (Ohio) region throughout your work without thinking about it.
Initialize-AWSDefaultConfiguration -ProfileName emsarmientoAWS -Region us-east-2
Once you've set your AWS credentials, you can now start working with different AWS services that you have access to. In the next tip in this series, we will explore the different AWS EC2 images running Windows Server and SQL Server so you can start deploying one for testing and DR purposes.
Next Steps
- Refer to the following AWS documentation
- Review the tip on Introduction to Windows PowerShell for the SQL Server DBA Part 1
- Check out the Windows PowerShell Tips
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-07-12
