Downgrade SQL Server Enterprise edition using AWS Systems Manager Document to reduce cost

In this post, we will show how to downgrade SQL Server from Enterprise edition to Standard edition on Amazon Elastic Compute Cloud(EC2) instances to help you reduce cost. If you are not using any of the features of Enterprise edition, you can downgrade to Standard edition.

Here is the flowchart that can help you identify the most used Enterprise edition features.

Flowchart to identify the most used Enterprise edition features

To determine if your SQL Service instance is using Enterprise edition features, use this script.

Manual Downgrade of SQL Server Enterprise Edition:

You can downgrade the edition of SQL Server manually by following these steps. This is an offline operation and will require downtime.

1. Ensure you have a successful FULL backup of all user and system databases.

2. Make note of current SQL Server minor version including Service pack, Cumulative Updates, GDR.

3. Detach all user databases.

4. Stop SQL Server and copy System database (master, model, msdb) data files and log files to local backup folder.

5. Uninstall SQL Server Enterprise edition including all components.

6. Restart the Server.

7. Install SQL Server accordingly to your requirement SQL Server Standard edition or SQL Server Developer edition media.

8. Install the same Service Packs and Cumulative Updates that you had before the uninstall.

9. Stop SQL Server service and replace the master, model and msdb databases with the backups you made in step 4 above.

10. Start SQL Server service and re-attach the user database mdf and ldf files that were deteched in step 3 above to SQL Server instance.

Automated Downgrade of SQL Server Enterprise Edition:

In this blog, we will walk through how to use an AWS Systems Manager Document (SSM Document) to downgrade your existing SQL Server instance from Enterprise edition to SQL Server Standard edition. SSM Document provides a simple and secure way to remotely execute commands or run scripts against EC2 instances or on-premises servers.

This blog post only applies to bring your own license (BYOL) use case.

Figure 1: SQL Enterprise Downgrade using SSM Document

Prerequisites

• Amazon EC2 windows Instance with SQL Server Enterprise edition installed.
• Amazon Simple Storage Service (S3) bucket for SQL Standard installation media, Cumulative Update and Configuration File to install SQL Server Standard edition.
• SQL Server EC2 instances which you are downgrading needs to have access to S3 bucket for downloading the installation media.
• AWS Systems Manager agent needs to be installed on the Amazon EC2 for SQL Server instance that you are downgrading.
• SQL Server Management Studio with connection to SQL Server EC2 instance that you are downgrading.
• Check SQL Server version by running select @@version command and make sure to upload the exact version of Cumulative Updates/Service Pack to S3 bucket.
• IAM role attached to Amazon EC2 instance with read permission on the AWS Secrets Manager.

When you use Systems Manager in EC2, you’ll need to first work through some prerequisites. The most important prerequisite is that you’ll need the AWS Systems Manager Agent (SSM agent) installed in your instances. The SSM agent is installed by default on Windows Server 2016 EC2 instances and EC2 instances created from Windows Server 2003-2012 R2 Amazon Machine Images (AMIs) published in November 2016 or later.

Another prerequisite 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. IAM role can be assigned to EC2 on launch. You can also add role to the existing instances using AWS Console or AWS CLI.

Walk Through

AWS supports instances running several versions and editions of Microsoft SQL Server.

Standard: This edition enables database management with minimal IT resources, with limited feature offering and lack of some high availability features and few online DDL operations compared to Enterprise edition. Standard edition has a limitation on 24 cores and 128GB of memory.

Enterprise: This is the most complete edition of all. With this edition, you have all features with no limitation of CPU and memory and is used for mission critical workload.

Developer: SQL Server Developer edition includes all functionality of Enterprise edition but it is only to be used for non-production environment.

The following steps show how to downgrade the SQL Server edition while still maintaining the system objects like logins and linked servers in your master database. You need to plan down time, as this is an offline process.

Here are the high-level steps performed by the SSM Document.

1. Take full backup of systems and user databases of existing SQL Server Enterprise edition.
2. Stop SQL Server services.
3. Copy master, model and msdb physical database files to Backup folder.
4. Uninstall Enterprise edition software.
5. Install SQL Server Standard or SQL Server Developer edition.
6. Install Cumulative Update or service pack based on patch files provided in S3 location.
7. Attach SQL Server user databases from Enterprise edition to Standard or Developer edition.
8. Stop SQL Server services.
9. Replace new master, model and msdb database files with the files backup taken in step 3.
10. Start SQL Server services.
    For this exercise, we’ll use a custom SSM Document to perform Enterprise edition downgrade.

Create a Custom SSM Document:

Please find detailed instruction to create custom SSM Document. Download SQLServerEditionDowngrade.ps as source code for the custom document Run Command. The PowerShell script you use to create a custom document will take a backup of all user databases, uninstall Enterprise edition, install new SQL Server edition, attach all user databases, replace master and msdb database using database files that we backed up earlier.

Figure 2: AWS SSM Document Content

For installing SQL Server, Systems Manager needs access to SQL Server Media and ConfigurationFile.Ini which are stored in S3. The example of ConfigurationFile.ini file is uploaded to S3 bucket for your reference. Edit the service accounts, passwords in the example configuration file based on your environment and upload the file to the same S3 bucket.

Let’s sign in to the AWS Management Console. To confirm that your instances are in a state to be managed, make sure we listed them in the Systems Manager console under EC2 Dashboard or Managed Instances. In Figure 3 below you can see Systems Manager managed nodes.

Figure 3: AWS SSM Managed Nodes

There are 7 input parameters required, as shown in Figure 4.

InstanceId: Target EC2 instance on which SQL Server edition downgrade is required. We can select this using an interactive instance picker from all the instances managed by Systems Manager.

BackupLocation: This is the location where backups of databases reside e.g. D:\MSSQL\Backup.

editionDowngradeUser: AWS Secrets Manager Secret name that stores Windows AD user and password that has administrator permissions on the EC2 instance and system administrator role for SQL Server. E.g domain\sqladmin .

S3BucketName: It’s the S3 bucket name where we kept our SQL Standard installation media and ConfigurationFile.Ini which will install the SQL Server. e.g. sqlbackup.

S3CUName: Name of the Cumulative Update file.

saPwdSecret : SQL Server “sa ” account password if it is Mixed Authentication mode installation.

Figure 4: AWS SSM Document parameters

SSM document parameters are stored in AWS Secrets Manager and AWS Parameter Store.

As shown in Figure 5, Windows AD User password and SA password are stored in the AWS Secrets Manager. Read AWS Secrets Manager to create secrets for detailed instructions.

Figure 5: Secrets stored in AWS Secrets Manager

As shown in Figure 6, remaining Document parameters are stored in AWS Systems Manager Parameter Store. Refer to the detailed instructions for creating parameters in Parameter Store.

 Figure 6: Parameters stored in AWS Systems Manager Parameter Store

In the S3 bucket, SQL Server Standard edition binaries need to be stored in the mounted format to a folder SQLInstall/ and a Cumulative Updates/Service Packs in a folder CU. Please make sure to follow the same folder structure as shown in Figure 7.

Figure 7: S3 bucket folder structure

We can execute the SSM Document from AWS Management Console or through CLI.

Figure 8: Execute SSM Document

The automation will create 3 log files on C:\Windows\temp folder on the SQL Server EC2 instance. You can see the output of the process in each step.

Here is the list of checks the SSM Document will validate before uninstalling and report an error. This test must pass to run the downgrade successfully.

1. SQL Server is installed.
2. Only one instance of SQL Server is installed. If there are multiple instances of SQL Server installed on the EC2 instance, it will not continue.
3. SQL Server edition is Enterprise.
4. SQL Server is running.
5. No pending reboot or pending file rename operation.
6. SQL Server is standalone. If SQL Server is part of clustering or part of Always on Availability groups. If it’s part of SQL Server Clustering or HADR setup, the script will throw an error.

Once all the above-mentioned checks are completed, automation will start backing up all the user and system databases to the BackupLocation based on input parameter. Make sure you have enough space available for all full database backups. You will require having free space equivalent to the total size of all databases. If the backup fails for any database, the SSM Document will stop executing, return and error and quit without downgrading.

Once all the databases backup is completed, it will generate a script file that has TSQL commands to attach the user databases after the completion of downgrade. It will store this file in the same location where backups are stored. As a next step, the script will stop the SQL Server service, copy the physical master and msdb files to the backup folder.

As shown in Figure 9, the backup folder stores User Database backups, system database files, and attach scripts.

Figure 9: Backup folder contents

Next step, automation script will start uninstalling SQL Server, including other SQL Server components which are installed on the server like SSIS, SSAS, SSRS etc. Once uninstall is complete, the next step is to install SQL Server Standard or Developer edition.

For installing SQL Server Standard edition, we need access to SQL Server installation Media. For this demo we already kept our SQL media in S3 bucket where our EC2 instance has access to download the media. SQL installation will be done using the ConfigurationFile.ini. As mentioned ConfigurationFile.ini has the information about parameters and features required for the SQL Server installation.

The script automatically downloads the files from S3 and starts installing SQL Server. Once SQL Server is installed successfully, it will look for Cumulative Updates to install. On completion of the Cumulative update, the script continues to attach user databases, master, and msdb. As shown in Figure 10, we can find the status of this automation in AWS Systems Manager >Automation.

Figure 10: SSM Document Execution Status

Remote desktop into your SQL Server EC2 instance to check SQL Server edition and log files. The log files can be found in C:\Windows\Temp.

Figure 11 shows a sample log file.

Figure 11: SQL Downgrade Automation Log

Next, Let’s validate that SQL services is running and we can connect to SQL Server Instance through SQL Server Management Studio Execute select @@version to see updated edition. As shown in Figure 12, we should see the version as Standard Edition.

Figure 12: SQL Server version validation

Once you have verified everything is fine, you can delete files downloaded from S3, log files generated by SSM Documents, and renamed SQL Server system database files, as shown in Figure 13.

Figure 13: Obsolete system database files

Conclusion:

In this post, we showed you how to use the AWS SSM Document to easily downgrade SQL Server from Enterprise Edition to Standard Edition. Many of our customers would like to change from Enterprise Edition of SQL Server to Standard Edition to save costs. You can use the same document to downgrade from SQL Server Enterprise Edition to any lower edition by providing the right SQL Server media in the S3 bucket. For AWS license included instances, changing the SQL Server edition on the instance will not change the billing associated with the AMI. You must migrate your databases to a new EC2 instance with an AMI running the new edition of SQL Server and do side by side downgrade.

For more information, see Best practices for deploying Microsoft SQL Server on Amazon EC2.

About the authors: