Upgrade Your End of Support Microsoft 2008 R2 Workloads in AWS with Ease

Many IT organizations have run the Microsoft Windows Server 2008 R2 operating system, SQL Server 2008 and SQL Server 2008 R2 database systems for over a decade. End-of-support (EOS) dates are fast approaching (July 2019 for SQL Server and January 2020 for Windows Server). Thus, it’s critical these systems get upgraded to a supported version to avoid security and compliance risks. To jump-start your upgrade, we have paired AWS Systems Manager documents that can assist. These can help with the upgrade of both Windows Server 2008 R2, SQL Server 2008 with Service Pack 4, or SQL Server 2008 R2 with Service Pack 3.

This blog post provides guidance about using AWS Systems Manager to automate the upgrade of your Windows Server 2008 R2, SQL Server 2008 and SQL Server 2008 R2 end of support instances on AWS. The guidance helps you move these to newer supported versions using a low-risk approach.

In addition, Microsoft offers technical support, including security updates, for the duration of the extended support for SQL Server 2008 R2.

The following AWS services and features are involved in this upgrade:

• AWS Systems Manager – A powerful unified interface from AWS that you can use to centrally manage your AWS resources. Core to using AWS Systems Manager is the use of AWS SSM Agent and AWS Systems Manager documents.
• AWS Systems Manager Agent (SSM Agent) – Amazon software that runs on your Amazon EC2 instances. The SSM Agent processes requests from the Systems Manager service in the cloud and configures your machine as specified in the requests. SSM Agent sends status and execution information back to the Systems Manager service by using EC2 messaging.
• AWS Systems Manager documents – This blog uses two AWS Systems Manager automation documents. For details about AWS System Manager documents, see the post Writing your own AWS Systems Manager documents on the AWS Management Tools Blog.

These automation documents provide two upgrade paths. One helps you upgrade Windows Server 2008 R2 to Windows Server 2012 R2. The other helps you upgrade SQL Server 2008 with Service Pack 4 or SQL Server 2008 R2 with Service Pack 3 on Windows Server 2012 R2 to SQL Server 2016. We describe these following.

For further details on the upgrade from Windows Server 2008 R2 to Windows Server 2012 R2, see the AWSEC2-CloneInstanceAndUpgradeWindows page in the System Manager documentation.

For further details on the upgrade from SQL Server 2008 with Service Pack 4 and SQL Server 2008 R2 with Service Pack 3 on Windows Server 2012 R2 to SQL Server 2016, see the AWSEC2-CloneInstanceAndUpgradeSQLServer page in the System Manager documentation.

Upgrade Options

Let’s deep dive into these two AWS System Manager Automation documents:

AWSEC2-CloneInstanceAndUpgradeWindows

The following diagram shows the actions performed by the AWSEC2-CloneInstanceAndUpgradeWindows script.

This script creates an Amazon Machine Image (AMI) from a Windows Server 2008 R2 instance in your account, and then upgrade the AMI to Windows Server 2012 R2. The upgrade operation is a multistep process that can take two hours to complete. The automation creates an AMI from the instance and then launches the newly created AMI in the VPC and subnet you provide. The automation workflow performs an in-place upgrade from SQL Server 2008 or SQL Server 2008 R2 instance to SQL Server 2016. The workflow also updates or installs the AWS drivers required by the upgraded instance. After the upgrade, the workflow creates a new AMI and then terminates the upgraded instance.

AWSEC2-CloneInstanceAndUpgradeSQLServer

This script creates an AMI from Amazon EC2 Windows instance running SQL Server 2008 on SP4 or SQL Server 2008 R2 on SP3 in your account, and then upgrades the AMI to SQL Server 2016 SP2. The upgrade is a multistep process that can take two hours to complete. The automation creates the AMI from the instance, and then launches the new AMI in the subnet that you provide. The automation then performs an in-place upgrade of SQL Server 2008 R2 to SQL Server 2016. After the upgrade, the automation creates a new AMI before terminating the upgraded instance.

You can test application functionality by launching the new AMI in your VPC. After you finish testing, and before you perform another upgrade, schedule application downtime before completely switching over to the upgraded instance.

The final result is one AMI, which is the upgraded instance AMI.

Two AMIs are involved in this process:

• First AMI – This is the AMI from current running instance (which is not upgraded). We use this AMI to launch another instance to run the in-place upgrade process. At the end of the process, we delete this AMI from your account, unless you have specifically opted in to keep the original instance up. This setting is handled by the parameter KeepPreUpgradeImageBackUp(default value is False, meaning we delete the AMI)
• Second AMI – This is the end result of the whole automation process. The second AMI is the result of the automation process and includes SQL Server 2016 instead of the original SQL Server 2008 or SQL Server 2008 R2 engine. You can test application functionality by launching the new AMI in your VPC. After you finish testing, and before you perform another upgrade, schedule application downtime before completely switching over to the upgraded instance.

1.     IAM Role Required

Before beginning the automation process, you need an IAM role with the correct IAM policies to allow AWS Systems Manager to conduct automation on Amazon EC2 and AMI instances in your account. For this example, the create role has the same name as the attached AMI policy, AmazonEC2RoleforSSM.

For guidance on creating an IAM role, see Creating a Role to Delegate Permissions to an AWS Service in the IAM documentation.

2.     Other prerequisites

Before you begin the upgrade process, confirm that you meet the prerequisites for AWS Systems Manager. You can find these in Creating a Role to Delegate Permissions to an AWS Service in the Systems Manager documentation.

3.     AWS Systems Manager automation execution options

There are multiple ways to execute the automation; following are some of the options.

Simple execution:

Use this approach when you want to update a single instance and don’t want to step through each automation step and audit the results. This blog post steps through this option in greater detail following.

Rate control:

Use this option when you want to apply the upgrade to multiple instances. You can do this using the following settings.

Parameter (also set in Multi-Account and Region)

This setting defines how your automation branches out.

Targets (also set in Multi-Account and Region)

Parameter Values

For parameter values, use the values defined in the automation document parameters.

Resource Group

In AWS, a resource is an entity that you can work with. Examples include an Amazon EC2 instance, an AWS CloudFormation stack, or an Amazon S3 bucket. If you work with multiple resources, you might find it useful to manage them as a group rather than move from one AWS service to another for each task. In some cases, you might manage large numbers of related resources, such as EC2 instances that make up an application layer. In these cases, you likely need to perform bulk actions on these resources at one time.

Tags

Tags enable you to categorize your AWS resources in different ways, for example by purpose, owner, or environment. This is useful when you have many resources of the same type—you can quickly identify a specific resource based on the tags you assigned to it. For example, you can define a set of tags for your account’s Amazon EC2 instances that helps you track each instance’s cost center and environments.

Rate Control (also set in Multi-Account and Region)

Setting the rate control parameters defines how many of your fleet to apply the automation to, either by target count or percentage of the fleet.

Multi-Account and Region

Accounts and organizational units (OUs)

Here, you can specify multiple accounts that you want to run the automation on.

AWS Regions

Here, you can specify multiple AWS Regions where you want to run the automation.

Manual Execution

This option is similar to Simple execution but enables you to step through each automation step and audit the results.

Upgrade Windows 2008 R2 to 2012 R2

This section focuses on using the script AWSEC2-CloneInstanceAndUpgradeWindows.

For this process to work, a number of prerequisites need to be satisfied. For a list of these, see AWSEC2-CloneInstanceAndUpgradeWindows in the Systems Manager documentation.

For the purposes of this example, I have a Window 2008 R2 instance residing in my AWS account. The image following shows my instance as having SQL Server 2008 R2 SP3 installed also, with a sample database of AdventureWorks. On the completion of this automation, AWS Systems Manager upgrades the OS to Server 2012 R2. SQL Server 2008 R2 will remain as is.

Now, let’s start the upgrade process!

4.     Open AWS Systems Manager in the AWS Management Console

For the purposes of this blog post, I have used the dedicated AWS Systems Manager interface.

From the navigation pane, choose Automation.

Choose Execute Automation to being the automation process.

Filter for the automation document AWSEC2-CloneInstanceAndUpgradeWindows from the search filter provided.

The Description field for the document provides an overview of the automation document.

5.     Step-by-step implementation of simple execution

InstanceID

This is the ID you provide for the instance to apply automation to.

InstanceProfile

This is the IAM role used to perform the AWS Systems Manager automation against the Amazon EC2 and AWS AMIs. For more information, see Task 2: Create an Instance Profile for Systems Manager in the Systems Manager documentation.

SubnetId

This is the subnet for the upgrade process and where your source EC2 instance resides. Verify that the subnet has outbound connectivity to AWS services including Amazon S3, and also to Microsoft (to download patches).

KeepPreUpgradedBackUp

(Optional) If this parameter is set to True, the automation retains the image created from the instance. The default is False.

RebootInstanceBeforeTakingImage

(Optional) The default is False (no reboot). If this parameter is set True, AWS Systems Manager reboots the instance before creating an AMI for the upgrade.

Enter the following parameters to run the automation.

Shareable execution link:

After the automation begins, you can monitor its progress.

Automation Completion

After the automation completes, the output that you can see is the AMI ID (getUpgradeImageDetails.ImageId : ami-01f63*******). You can launch the AMI to see that Windows is upgraded.

It’s not necessary for the automation to run all steps. Steps are conditional based on the behavior of the automation and instance. Thus, AWS Systems Manager might skip some steps that are not required.

Also, some steps might time out. AWS Systems Manager attempts to upgrade and install all latest patches. However, sometimes patches time out based on a definable timeout setting for the given step. In that case, the AWS Systems Manager automation continues to the next step ensuring the internal OS is upgraded to 2012 R2.

Outputs

In the case of this automation, you receive as outputs the name and ImageID for the newly created AWS AMI. From here you can launch an Amazon EC2 instance and review your upgrade as shown following. To learn how to create an Amazon EC2 instance from an AWS AMI, see the Knowledge Center article How do I launch an EC2 instance from a custom Amazon Machine Image (AMI)?

Upgrade SQL Server 2008 R2 to SQL Server 2016

This section focuses on using the script AWSEC2-CloneInstanceAndUpgradeSQLServer.

The following diagram shows the actions performed by the AWSEC2-CloneInstanceAndUpgradeSQLServer script.

For this process to work, a number of prerequisites need to be satisfied. For a list of these, see AWSEC2-CloneInstanceAndUpgradeSQLServer in the Systems Manager documentation.

After you’ve met these prerequisites, use the following steps to upgrade your SQL Server 2008 R2 database engine to SQL Server 2016. In this example, there’s a SQL Server 2008 R2 source server named SQLServer2008r2-Source. It’s running SQL Server 2008 R2 as the database engine and Windows 2012 R2 as the OS. This server has its database engine upgraded by using the automation document on AWS Systems Manager.

If you haven’t already, download the SQL Server 2016 .iso file and mount it onto the source server. After this file is mounted, copy all the component files and place them on any volume of your choice. After that’s complete, take an Amazon EBS snapshot of the volume and copy the snapshot ID onto a clipboard for later use. To review the steps involved to create an EBS snapshot, see Creating an Amazon EBS Snapshot in the EBS documentation.

This specific example has a role named, SSM-EC2-Profile-Role that has the AmazonEc2RoleSSM policy attached to the role. In turn, that role is attached to the instance. Having this allows the Systems Manager service to communicate with the EC2 instance and run commands on it after it’s added in the AWS Systems Manager service.

After that instance profile is attached to the EC2 instance, confirm that you can see it under Managed Instances for the AWS Systems Manager service. If you don’t see any managed instances, wait a few minutes and it’ll show. Otherwise, reconfirm that the AWS Systems Manager prerequisites have been met.

As you can see following, the instance has now shown up as a managed instance (which means you can run commands against it).

After your Managed Instance list has been confirmed, navigate to the Automation tab within AWS Systems Manager and choose Execute Automation:

To search for the AWSEC2-CloneInstanceAndUpgradeSQLServer document, go to the search bar, choose Document name prefix, choose Equal, and then paste in the document name (AWSEC2-CloneInstanceAndUpgradeSQLServer). It’s important to note that this name is case-sensitive, so make sure that you have it correct.

After you found the document, choose it, and then choose Next.

You need following parameters for this upgrade process to succeed:

• InstanceId

Type: String

Description: (Required) The instance running Windows Server 2012 R2 (or later), SQL Server 2008 with SP4 or SQL Server 2008 R2 with SP3 (or later).

• IamInstanceProfile

Type: String

Description: (Required) The IAM instance profile.

• SQL Server SnapshotId

Type: String

Description: (Conditional) SnapshotId for SQL Server 2016 installation media. This parameter is only required if the instance has a Bring-Your-Own-License (BYOL) SQL Server license. This parameter is not required for Instances launched using an AWS-provided Amazon Machine Image (AMI) for Windows Server with Microsoft SQL Server.

• SubnetId

Type: String

Description: (Required) Provide a subnet for the upgrade process. Verify that the subnet has outbound connectivity to AWS services including Amazon S3, and to Microsoft (to download patches).

• KeepPreUpgradeImageBackUp

Type: String

Description: (Optional) If this parameter is set to True, the automation doesn’t delete the AMI created from the instance before the upgrade. If this is set to True, you must delete the AMI. By default, the AMI is deleted.

• RebootInstanceBeforeTakingImage

Type: String

Description: (Optional) If this parameter is set to True, the automation reboots the instance before creating a pre-upgrade AMI. By default, the automation doesn’t reboot before upgrade.

Once you have the above parameters, you can move forward and populate the section and click Execute:

After the execution process begins, monitor the progress of the document through each step by scrolling down and looking at the progress pages.

After you see Execution Status read Success, choose the Outputs list to view AMI information. Here you see the AMI ID. You can now use that to launch your SQL Server 2016 instance for the VPC of your choice.

Navigate back to the EC2 console and choose the AMIs tab. Here, you see the new AMI, which you can later launch to verify SQL Server 2016 has been successfully installed.

To launch the new AMI, choose it and choose Launch.

Choose the type of instance that you want for the AMI, choose which VPC and subnet that you want to deploy the machine to, and choose the storage you want. Because you’re launching this new EC2 instance from an AMI, the volumes present from the AMI are presented to you as an option to include within the new EC2 instance you launch. You can remove any of those volumes for the new instance or add any to it.

After you’ve selected what you want for storage, add a tag, add the security group or groups to the instance, and then finally launch your instance.

The tag name for the newly launched server is SQLServer2016. Choose that and make a connection to it so that you can verify SQL Server 2016 is now the new database engine on the instance.

After you’ve launched SQL Server Management Studio, connect to the default instance (or named instance) for the given server. Here, you can verify that the engine is indeed upgraded by running the following query in the query window.

SELECT @@VERSION

There are two ways you can confirm that the engine is upgraded. One is the specific build number within the Object Explorer (build number 13.0.5026). The other is receiving an output from the query above reading “Microsoft SQL Server 2016…”

You have now successfully worked through the AWSEC2-CloneInstanceAndUpgradeSQLServer automation document within AWS Systems Manager. Hope this was an informative process, and found it helpful with your upgrade process.

Conclusion

In this blog post, we use two AWS Systems Manager documents, AWSEC2-CloneInstanceAndUpgradeWindows and AWSEC2 -CloneInstanceAndUpgradeSQLServer, to help you automate the upgrade process for your fleet of Windows Server 2008 R2 and SQL Server 2008 with SP4 or SQL Server 2008 R2 instances in your AWS environments. When the automation steps are complete, an AWS AMI is created. This approach gives you flexibility on how you can move forward with your upgraded servers.

We welcome your feedback or questions in the comments.

About the Authors

Stefan Minhas is a Sr. Consultant at Amazon Web Services. He is a Microsoft subject matter expert in the Application Delivery Practice for the Americas, and he provides guidance and technical assistance in the development and growth of specialty skills across the broader Professional Services community.

Bini Berhe is a Sr. Solutions Architect at Amazon Web Services. He works with AWS customers to provide guidance and technical assistance on running their Microsoft workloads on AWS.