Automate downgrading SQL Server to Developer edition on Amazon EC2

In this blog post, we will show you how to automate the process of downgrading from Microsoft SQL Server Enterprise or Standard edition to SQL Server Developer edition to save cost.

Introduction

Every day, customers benefit from the cost savings, scalability, performance, and ease of management of running SQL Server on AWS. Deploying SQL Server Developer edition is a great option for reducing the costs of running non-production workloads on AWS. SQL Server Developer edition is a free, fully featured edition available for download on the Microsoft website. It is recommended for use in non-production environments like dev, testing, and staging.

In a previous blog post, we showed you how to manually deploy SQL Server Developer edition and downgrade from SQL Server Enterprise or Standard edition to reduce cost. In this post, we provide a solution that automates the entire process at scale. If you have SQL Server Enterprise or Standard Edition running on Amazon Elastic Compute Cloud (Amazon EC2), this solution automatically converts your non-production SQL Server environments to run on SQL Server Developer edition. For Amazon EC2 instances running AWS license-included SQL Server Enterprise or Standard edition, the solution also changes the license type of the Amazon EC2 instance to ensure correct billing. For more information on license type conversions , refer to the license type conversion documentation.

Solution overview

This simple solution uses a combination of AWS CloudFormation and Automation, a capability of AWS Systems Manager (SSM), to automatically deploy a duplicate of your non-production database(s) on SQL Server Developer edition. The solution runs a series of PowerShell scripts that are delivered via a SSM Run Command to downgrade to SQL Server Developer edition. The automation creates an Amazon Machine Image (AMI) of the existing Amazon EC2 instance and runs the scripts on a new Amazon EC2 instance launched from that AMI. Figure 1 shows the architecture of the solution.

Figure 1: Solution architecture

Automation workflow

The automation performs the following actions:

1. Create an Amazon Machine Image (AMI) of the original Amazon EC2 instance.
2. Launch a new Amazon EC2 instance from that AMI.
3. Retrieve the SQL Server credentials from AWS Secrets Manager .
4. Run the downgrade operation, which:
   • Detaches all databases on the original SQL Server instance
   • Uninstalls the SQL Server Enterprise or Standard edition
   • Installs SQL Server Developer edition.
   • Reattaches the databases to the new SQL Server instance running SQL Server Developer edition
   • Converts the license type (if applicable)
   • Stops the new Amazon EC2 instance

Supported configurations

The automation supports the following configurations for source and target SQL Server editions. The source is always SQL Server Standard or Enterprise edition. The target is always SQL Server Developer edition.

The automation downgrades the SQL Server to Developer RTM edition. If applicable, note the SQL Cumulative Update on the source instance and install same on the target SQL Server instance at the end of the process.

Prerequisites

This solution assumes the following prerequisites:

• The source Amazon EC2 instance is running SQL Server Enterprise edition or SQL Server Standard edition.
• SSM agent is installed and running on the source Amazon EC2 instance.
• The source SQL Server is not clustered. Only standalone source SQL Server deployments are supported.
• You have credentials for SQL Server with the sysadmin role.
• SQL Server is configured with SQL Server authentication mode enabled.
• Your IAM user/role has permissions to deploy AWS resources.
• AWS License Manager has been onboarded.

Walkthrough

Store the SQL Server credentials in AWS Secrets Manager

The automation requires valid credentials to connect to your SQL Server databases on the source instance during the downgrade process. These credentials are retrieved from AWS Secrets Manager. Therefore, you will need to create a Secrets Manager secret with the credentials that will be referenced during the automation.

Complete the following steps to create the SQL Server credentials in AWS Secrets Manager (Figure 2):

1. Navigate to the Secrets Manager console.
2. Choose Store a new secret and select Other type of secret.
3. In the Key/value pairs section, enter “Username” as the key and enter the SQL Server username as the value.
4. Select Add row. Enter “Password” for the first field, and the password for the user in the second field.
5. Leave Encryption Key as the default value (“aws/secretsmanager”).
6. Choose Next and for the Secret name, specify a name and choose
7. On the Configure rotation – optional page, choose
8. On the Review page, choose Next, and then choose Store.

Figure 2: Create AWS Secrets Manager secret

Create Systems Manager Automation document

Next, you will deploy a SSM Automation runbook that will downgrade SQL Server.
Complete the following steps to deploy the CloudFormation stack that will create the automation runbook:

1. Download the CloudFormation template.
2. Navigate to the CloudFormation console.
3. In the Create stack menu, choose With new resources (standard).
4. Select upload a template file, choose the file you downloaded, and choose
5. On the Specify stack details page, enter a value for Stack name and enter the name of the AWS Secrets Manager secret you created earlier. This secret will be referenced when the SSM Automation document runs the SQL Server downgrade process. Choose
6. On the Configure stack options page, choose Next.
7. On the Review and create page, check the box next to I acknowledge that AWS CloudFormation might create IAM resources.
8. Choose Submit and wait for the stack creation to complete.
9. To locate the name of the SSM document created by CloudFormation, select your stack from the list, and select Outputs. Locate the value next to the SystemsManagerAutomationDocumentforSqlDev The name of the document will typically begin with the name of the AWS CloudFormation stack, as shown in Figure 3. You will need this in the next step.

Figure 3: Locate the name of the AWS Systems Manager automation document

Run the automation

Now, you will run the SSM Automation document created in the previous section to downgrade the SQL Server edition running on an Amazon EC2 instance.

In this example, you will run the automation against a single Amazon EC2 instance. However, you could also run it against multiple Amazon EC2 instances if desired.

Complete the following steps to run the automation:

1. Navigate to the Systems Manager
2. In the side menu, choose Automation.
3. On the Automation page, choose Execute automation.
4. On the Choose runbook page, select Owned by me.
5. In the search box, enter the name of the Systems Manager document you obtained from the CloudFormation stack previously and press enter.
6. Select the document and choose Next.
7. In the Input parameters section, select the Amazon EC2 instance running SQL Server Enterprise or Standard edition.
8. Enter the Subnet ID for the subnet in which you want the test instance to be created during the automation.
9. Enter the security group to associate with the test Amazon EC2 instance, as shown in figure 4:

Figure 4: AWS Systems Manager Automation parameters

Note: The specified subnet ID and security group must be in the same network. The name of the AWS Secrets Manager secret that you created previously, and other parameters will be automatically populated. Verify all the parameters and make changes as desired. The automation reboots the source instance before creating the AMI, by default. You can change this setting to “true” but note that if you choose to enable No reboot, AWS cannot guarantee the file system integrity of the created image. For more information, refer to the Create a Windows AMI from a running instance.

10. Choose execute.

As part of the process, the automation resets the local Windows administrator account on the test instance. The local administrator credentials are stored in AWS Secrets Manager, which can be retrieved to logon to the new Amazon EC2 instance after the downgrade is complete.

11. When the automation completes, navigate to the AWS Secrets Manager console.
12. Select the secret that has a name similar to “LocalAdministratorSecret-xxxxxxxx”. The description will read: “Local administrator password for new instance with SQL Server downgraded to Developer Edition. Generated by the CFN for stack:<Stack name>”

Figure 5: Retrieve local administrator from AWS Secrets Manager

13. On the Secret details page, select Retrieve secret value to reveal the local administrator credentials.
14. Navigate to the Amazon EC2 console.
15. In the side menu, choose Instances.
16. Search for “SQL_Dev_Edition_<GUID>” to locate and start the test instance.
17. Log into the instance using the credentials you retrieved from AWS Secrets Manager previously. Verify that SQL Server has been downgraded to Developer edition and that your databases are intact.
18. If the original Amazon EC2 instance was running AWS license-included SQL Server Enterprise or Standard edition, you can also verify that the license type has changed so you are billed correctly. The Usage operation should display as “RunInstances:0002.”

You have now successfully downgraded your SQL Server to Developer edition.

Cleanup

To delete the resources created by the CloudFormation template, go to the CloudFormation console. Select the stack you created, and then choose Delete.

Conclusion

Businesses realize substantial cost savings by leveraging SQL Server Developer edition for their test, staging, and development environments. In this blog post, we showed you how to downgrade from SQL Server Enterprise or Standard edition to SQL Server Developer edition using Systems Manager automation. By leveraging this solution, you can downgrade to SQL Server Developer at scale across your non-production environments to save on SQL Server licensing costs.

Are you considering running your SQL Server workloads in the cloud? Learn why AWS is the best cloud to run Microsoft Windows Server and SQL Server workloads.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.