Optimize your SQL Server costs by using bring your own media (BYOM) on Amazon RDS Custom for SQL Server

Organizations are migrating their Microsoft SQL Server workloads to AWS managed database services like Amazon Relational Database Service (Amazon RDS) for SQL Server or Amazon RDS Custom for SQL Server, which makes it easy to set up, operate, and scale SQL Server deployments in the cloud.

Customers often ask us how they can optimize Amazon RDS for SQL Server costs. Now you can bring your own SQL Server media and use it to create an Amazon RDS Custom for SQL Server instance using a custom engine version (CEV).

Amazon RDS Custom for SQL Server automates setup, operation, and scaling of databases in the cloud, while granting access to the database and underlying operating system (OS) to configure settings, install drivers, and enable native features to meet the dependent application’s requirements. A bring your own media (BYOM) approach on Amazon RDS Custom for SQL Server using a CEV helps you run your SQL Server workloads as a managed database service while reducing the operating costs.

In this post, we walk you through the step-by-step process to bring your own media and use it to set up Amazon RDS Custom for SQL Server using a CEV.

Note: With the BYOM option, you can leverage your existing SQL Server licenses with Amazon RDS Custom for SQL Server, subject to Microsoft’s licensing terms. The License Mobility Page offers detailed information on the process. Microsoft requires that customers complete and send a License Mobility verification form.

Solution overview

For our use case, we use an existing SQL Server media to create an RDS Custom for SQL Server instance using a CEV. The following diagram illustrates this workflow architecture.

This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information. We strongly recommend that you set this up in a non-production instance and run the end-to-end validations before you implement this solution in a production environment.

To implement the solution, complete the following high-level setup steps:

Launch an Amazon Elastic Compute Cloud (Amazon EC2) instance.
Locate and copy your SQL Server media to the EC2 instance.
Install SQL Server, Cumulative Update, and SQL Server Management Studio (SSMS) on the EC2 instance.
Verify the major and minor version.
Grant sysadmin access to NT AUTHORITY\SYSTEM login.
Perform Sysprep.
Create a golden AMI.
Create the CEV.
Create an RDS Custom for SQL Server instance using the CEV.
Validate the instance.

Prerequisites

To test this solution, you need the following prerequisites:

An AWS account
SQL Server 2019 installation media along with Cumulative Update
SSMS or any client tool to connect to SQL Server
An understanding of using an Amazon Machine Image (AMI)
The latest version of the AWS Command Line Interface (AWS CLI) 2.11.15 or higher installed and configured on a Windows or Linux host
Network components created by following the steps outlined in: Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup)

Launch an EC2 instance

Before you launch an EC2 instance, you need to check the Region and version availability to make sure the combination of SQL Server editions and versions are enabled for you in the Region where you want to host the RDS Custom for SQL Server instance. Visit License Mobility to learn more about the type of media you can bring on Amazon RDS Custom for SQL Server. In our scenario, we use Microsoft Windows Server 2019 Base as our base O.S. To launch an EC2 instance, follow these steps:

On the Amazon EC2 console, choose Launch Instance.
For Name, enter the name of the instance.
Under Quick Start, choose Windows.
On the drop-down menu, choose Microsoft Windows Server 2019 Base.
To ensure the correct AMI is selected, complete the following steps:
1. In the right pane under Summary, copy the AMI ID of the Software Image (AMI). For our use-case, it’s ami-0b5b075a85f7ba655.
2. In the navigation pane, choose Images, then AMIs.
3. Choose Public imageson the drop-down menu.
4. In the search bar, enter the AMI ID.
5. In the Details section, check the Usage operation It must be RunInstances:0002.
Choose an appropriate instance type on the drop-down menu. For our use-case, we use m5.xlarge.
Create or select an existing key pair.
Remember to save the key pair; you need this to Remote Desktop to this EC2 instance for customization.
Choose the VPC and security group.
Under Configure storage, modify the value of the root volume as needed.
Any additional volumes will not be persisted at the time of instance creation.
Choose Launch instance.

Locate and copy your SQL Server media to the EC2 instance

The next step is to locate your SQL Server installation media including Cumulative Update and SSMS tool and copy it to the EC2 instance you created. Complete the following steps:

On the Amazon EC2 console, choose Instances in the navigation pane.
Verify that the instance state is Running.
Retrieve the password using the key pair and Remote Desktop to the EC2 instance.
Copy the SQL Server installation media, Cumulative Update, and SSMS to the EC2 instance.

Install SQL Server 2019, Cumulative Update and SSMS on the EC2 instance

Complete the following steps:

Start installing the SQL Server on an EC2 instance.
Keep the directory structure with default settings.
Choose Next.
Any changes to default installation are not persisted in the golden image. These changes will result in failure to create an RDS Custom for SQL Server instance.
Keep Default instance selected and choose Next.
On the Database Engine Configuration page, for Server Configuration, choose Mixed Mode (SQL Server and Windows Authentication) and provide a strong password.
Choose Add Current User to grant sysadmin access to the SQL Server.
Choose Next and install SQL Server.
Reboot the server after SQL Server installation.
Install Cumulative Update. In our case, we install CU17 for SQL Server 2019.
Install SSMS.
Validate both SQL Server and Cumulative Update has been installed successfully.

Verify the major and minor version

To verify the major and minor versions of the SQL Server installed on the EC2 instance, complete the following steps:

Retrieve the password using the key pair and Remote Desktop to the EC2 instance.
Open SSMS and run the following command to check the SQL Server minor version:
```
SELECT @@VERSION
```

Grant sysadmin access to NT AUTHORITY\SYSTEM login

After validating the SQL Server and Cumulative Update installation successfully, perform the following steps to grant sysadmin access to the NT AUTHORITY\SYSTEM login:

Remote Desktop to the Amazon EC2 host.
Open SSMS and connect to the SQL Server endpoint using the system administrator password.

Run the following script:

USE master
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM' , @rolename = N'sysadmin'
GO

Perform Sysprep

Before we perform Sysprep, follow these steps to verify that Shutdown behavior option is not set to Terminate:

On the Amazon EC2 console, choose Instances in the navigation pane.
Select the EC2 instance and on the Actions menu, choose Instance settings.
Choose Change shutdown behavior.
Confirm that the Terminate option isn’t specified.
Remote Desktop to the EC2 instance.
On the Start menu, choose Ec2LaunchSettings.
In the Ec2 Launch Settings window, select Random (Retrieve from console) in the Administrator Password
Choose Shutdown with Sysprep.
Choose Apply and then Ok to proceed.

Create a golden AMI

To create the golden AMI, perform the following steps:

On the EC2 console, verify that the EC2 instance is in the Stopped
Select the EC2 instance and on the Actions menu, choose Images and templates.
Choose Create image.
Enter an image name and choose Create image.
On the Amazon EC2 console, choose AMIs in the navigation pane under Images.
Choose Owned by me on the drop-down menu and wait for the status to change from Pending to Available.
Take note of the AMI ID. In our testing, it’s ami-0cf770cd2ca6a9e8b.

Create the CEV

On the AWS CloudFormation console, identify the stack created in the prerequisites, and go to the Outputs tab and take a note of the RDSCustomKMSKey value. To create the CEV from the golden AMI, use the following AWS CLI command on Linux OS or AWS CloudShell. You can replace the parameters according to your requirements.

On Windows OS, use the line break character ^ instead of \ in the following code. For PowerShell use `.

aws rds create-custom-db-engine-version \
--engine custom-sqlserver-se \
--engine-version 15.00.4249.2.cev.se.byom \
--description "Custom SQL Server 15.00.4249.2 cev se byom" \
--kms-key-id <key id> \
--image-id ami-0cf770cd2ca6a9e8b \
--region us-west-2

The –engine-version uses the following naming standards:

The CEV naming pattern must be a combination of major_engine_version.minor_engine_version.CUSTOMIZED_STRING
The CUSTOMIZED_STRING name can include only lowercase letters, hyphens, underscores, and periods

To verify that the CEV is created with the right attributes, run the following AWS CLI command on Linux OS or AWS CloudShell. On Windows OS, use the line break character ^ instead of \ in the following code. For PowerShell use `.

aws rds describe-db-engine-versions \
--engine custom-sqlserver-se \
--engine-version 15.00.4249.2.cev.se.byom \
--region us-west-2 \
--include-all

We get the following output:

{
            "Engine": "custom-sqlserver-se",
            "EngineVersion": "15.00.4249.2.cev.se.byom",
            "DBParameterGroupFamily": "custom-sqlserver-se-15.0",
            "DBEngineDescription": "Microsoft SQL Server Standard Edition for custom RDS",
            "DBEngineVersionDescription": "Custom SQL Server 15.00.4249.2.cev se byom",
            "Image": {
                "ImageId": "ami-0cf770cd2ca6a9e8b",
                "Status": "pending-validation"
            },
            "DBEngineMediaType": "Customer Provided",
            "ValidUpgradeTarget": [],
            "SupportsLogExportsToCloudwatchLogs": false,
            "SupportsReadReplica": false,
            "SupportedFeatureNames": [],
            "Status": "pending-validation",
            "SupportsParallelQuery": false,
            "SupportsGlobalDatabases": false,
            "MajorEngineVersion": "15.00",
            "DBEngineVersionArn": "arn:aws:rds:us-west-2:<Account_ID>:cev:custom-sqlserver-se/15.00.4249.2.cev.se.byom/705ef739-c145-4aae-8c79-ac28f099b94f",
            "CreateTime": "2023-04-26T14:20:15.076000+00:00",
            "TagList": [],
            "SupportsBabelfish": false
        }

Create an RDS Custom for SQL Server instance using the CEV

On the AWS CloudFormation console, identify the stack you created as a prerequisite. Go to the Outputs tab and take note of the RDSCustomDBSubnetGroup, RDSCustomIAMInstanceProfile, RDSCustomKMSKey, and RDSCustomSecurityGroup values. To create the RDS Custom for SQL Server instance with the CEV, use the following AWS CLI command on Linux OS or AWS CloudShell. You can replace the parameters according to your requirements.

On Windows OS, use the line break character ^ instead of \ in the following code. For PowerShell use `.

aws rds create-db-instance \
--engine custom-sqlserver-se \
--engine-version 15.00.4249.2.cev.se.byom \
--db-instance-identifier rds-custom-sql-server-cev-instance \
--db-instance-class db.r5.xlarge \
--allocated-storage 1000 \
--master-username admin \
--master-user-password ******* \
--kms-key-id <key id> \
--custom-iam-instance-profile AWSRDSCustom-rds-custom-sql-cev-us-west-2 \
--db-subnet-group-name rds-custom-sql-cev-db-subnet-group \
--vpc-security-group-ids sg-0ec22fee6b5f6182e \
--region us-west-2

Wait for the successful instance creation and take note of the endpoint.

Validate the instance

To validate that the RDS Custom for SQL Server CEV instance is created with the OS-level customization, perform the following steps:

Remote Desktop to the EC2 host created as a prerequisite.
Open SSMS and connect to the RDS Custom for SQL Server CEV instance endpoint.
Run the following command and take note of the SQL Server version and EC2 host name for the RDS Custom for SQL Server CEV instance:
```
SELECT @@VERSION AS 'Version'

SELECT @@SERVERNAME AS 'Host Name'
```

Clean up

To avoid future charges and remove the components created while testing this use case, complete the following steps:

On the Amazon RDS console, select the database you set up, and on the Actions menu, choose Delete.
On the Amazon EC2 console, select the EC2 instance that you used, and on the Actions menu, choose Terminate.

Summary

In this post, we demonstrated how you are able to bring your own media (BYOM) and create an RDS Custom for SQL Server CEV to optimize your operating costs in cloud. Try out Amazon RDS Custom for SQL Server CEV BYOM and continue to save costs on SQL Server workloads.

If you have any comments or feedback, leave them in the comments section.

About the authors

Minesh Chande is Senior Database Specialist Solutions Architect at Amazon Web Services. He helps customers across different industry verticals design, migrate, and optimize their SQL Server workloads to a managed database platform like Amazon RDS and Amazon RDS Custom.

Rajib Sadhu is Senior Database Specialist Solutions Architect with over 15 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect and migrate their database solutions to AWS. Prior to joining AWS, he supported production and mission-critical database implementation across financial and travel and hospitality industry segments.

AWS Database Blog