Upgrading SQL Server Using EC2 Systems Manager
This post was written by Alan Cranfield, Systems Engineer at Amazon Web Services
This is the first in a series of blog posts aimed at the enterprise SQL Server DBA. I’ll demonstrate how to administer your SQL Server workloads on Amazon EC2 using practical examples and best practices.
Using Run Command
Microsoft SQL Server is a popular workload on Amazon EC2. Keeping your SQL Server instances up to date with the latest service pack is important for the stability and security of your critical data. If you need to support multiple versions and editions of SQL Server keeping track of all the latest service packs can be cumbersome.
Run Command provides a simple and secure way to remotely execute commands or run scripts against EC2 instances or on-premises servers. With Run Command, you can perform commands that make it easy to accomplish common administrative tasks like upgrading SQL service packs!
When you use EC2 Systems Manager you’ll need to first work through some prerequisites. The most important prerequisite is that you’ll need the SSM agent installed on your instances. The SSM agent is installed by default on Windows Server 2016 instances and instances created from Windows Server 2003-2012 R2 Amazon Machine Images (AMIs) published in November 2016 or later.
Another pre-requisite is that your instances need to be assigned an AWS Identity and Access Management (IAM) role. The IAM role is used to secure the permission policies needed to communicate with the Systems Manager API. Instances are usually added to an IAM role on launch, but you can also add existing instances using the AWS CLI.
Using PowerShell modules
For this exercise we’ll use the Run Command native support for PowerShell modules to download and import a PowerShell module from an Amazon S3 bucket. This module will be called to identify the version of SQL that is running and then download and install the latest service pack. I’ll walk you through updating the SQL service pack by using the AWS Management Console and by using AWS Tools for PowerShell.
Updating the SQL service pack from the EC2 console
- Sign In to the AWS Management Console. To confirm that your instances are in a state to be managed, make sure they are listed in the EC2 console under EC2 Dashboard\Managed Instances.
- Navigate to the Run Command and choose Run a command. Then select the AWS-InstallPowershellModule document, and the servers you’d like to upgrade.
- For Source enter the location of the S3 bucket that holds the PowerShell module: https://s3.amazonaws.com/sql-service-pack/InstallSqlServicePack.zip
- Paste the following PowerShell script into the Commands Window
- Choose the Run button and check the Status column for the instance progress.
- Choose a specific Instance ID in the top pane, and then in the bottom pane choose the Output tab and then choose View Output.
- The results of the service pack upgrade are shown in the Output results window.
Updating the SQL service pack using the AWS Tools for PowerShell
For those who prefer a scripted solution you can call Run Command using the AWS Tools for Windows PowerShell.
- Download and install the latest AWS Tools for Windows PowerShell.
- Set your credentials and AWS Region
- Confirm that your instances are managed by SSM
- Run a Command against your instances to upgrade the SQL service pack. (Tagging can also be used to group servers.)
- Check the progress.
- Check the results.