Create a SQL Server Developer Edition instance using 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 SQL Server Licensing costs on Amazon RDS Custom for SQL Server for development and test systems. One of the most straightforward ways to do this is to use SQL Server Developer Edition, which you can use with Amazon RDS Custom for SQL Server. SQL Server Developer Edition 2019 includes all the functionality of Enterprise Edition, but is licensed for use as a development and test system, not as a production server.

In this post, we explain the step-by-step process to bring your own media (BYOM) and use SQL Server Developer Edition on Amazon RDS Custom for SQL Server.

Solution overview

As of writing this post, Developer Edition is supported on SQL Server 2019 CU 17, 18, 20, and 22. To use Developer Edition on Amazon RDS Custom for SQL Server, you need to create a custom engine version (CEV). A CEV is an Amazon Machine Image (AMI) that includes SQL Server (in this case, SQL Server Developer Edition specifically). For this post, we use SQL Server 2019 with CU22 installed.

The following are the high-level steps for creating an RDS Custom for SQL Server instance using Developer Edition:

Launch an Amazon Elastic Compute Cloud (Amazon EC2) instance.
Download, install, and configure SQL Server Developer Edition, including the supported Cumulative Update (CU) on the EC2 instance. Optionally, install SQL Server Management Studio (SSMS) on the EC2 instance.
Shut down the EC2 instance with Sysprep and create an AMI.
Create an RDS Custom for SQL Server CEV using the AMI with the AWS Management Console or the AWS Command Line Interface (AWS CLI).
Create an RDS Custom for SQL Server instance using the CEV with the console or AWS CLI.

Prerequisites

The following are the prerequisites to use Developer Edition on Amazon RDS Custom for SQL Server:

An AWS account
An understanding of AMIs

Launch an EC2 instance

Complete the following steps to launch an EC2 instance:

On the Amazon EC2 console choose on Launch instance.
For Name, enter the name of the instance.
Under Application and OS Images (Amazon Machine Image), choose Windows.
For the Amazon Machine Image, choose Microsoft Windows Server 2019 Base.
Choose an appropriate instance type
Create or select an existing key pair. If you are creating a new key pair, remember to save the key pair. You will need this later.
Choose the VPC and security group.
Under Configure storage, modify the value of the root volume as needed. For SQL Server Developer Edition, we recommend configuring at least 50 GB of space.
Choose Launch instance.

Download, install, and configure SQL Server Developer Edition

In this section, we detail the steps to download, install, and configure SQL Server Developer Edition.

Download SQL Server installation media to the EC2 instance

Complete the following steps to download the installation media:

Retrieve the EC2 instance password using the key pair you used or created earlier.
Remote Desktop to the EC2 instance and downloadSQL Server 2019 Developer Edition or locate it via your Visual Studio subscription.
Download the required SQL Server Cumulative Update (CU). In this example, we will install CU22.
Optionally, download the SSMS tool.

Install SQL Server 2019 Developer Edition, CU, and SSMS on the EC2 instance

Complete the following steps to install the installation media you downloaded:

Run the downloaded executable to install SQL Server.
Use the default directory structure as shown in the following screenshot, then choose Next.
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 on Next and install SQL Server.
Install the CU by running the executable. In our case, we install CU22 for SQL Server 2019.
Optionally, install SSMS.

Grant sysadmin access to the NT AUTHORITY\SYSTEM login

After you validate the SQL Server and Cumulative Update installation successfully, run the following PowerShell command to grant sysadmin access to the NT AUTHORITY\SYSTEM login:

Invoke-Sqlcmd -Query "EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM' , @rolename = N'sysadmin';" -ServerInstance .

Shut down EC2 the instance with Sysprep

Complete the following steps to shut down the EC2 instance:

On the Windows Start menu, choose Ec2LaunchSettings.
In the Ec2 Launch Settings window, select Random (Retrieve from console)for Administrator Password.
Choose Shutdown with Sysprep.
Choose Apply and then Ok to proceed.

Create an AMI

To create the AMI using the console, 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.
Note down the AMI ID. You need this to create the CEV.

Create an RDS Custom for SQL Server CEV and database instance

Complete the following steps to create a CEV and db instance using the console:

On the Amazon RDS console, create a CEV using the AMI you created.
Create an RDS Custom for SQL Server instance using the CEV you created as the Engine Version. The quickest way is to use this getting started guide, which creates your RDS Custom dependencies with a CloudFormation template. For a more in-depth, comprehensive guide on setting up RDS Custom and its dependencies, refer to Explore the prerequisites required to create an Amazon RDS Custom for SQL Server instance.

Validate the instance

To validate that the RDS Custom for SQL Server CEV instance is created with Developer Edition, perform the following steps:

Remote Desktop to the RDS Custom instance EC2 host.
Open SSMS and connect to the RDS Custom for SQL Server instance endpoint.
Run the following command and verify the SQL Server engine version and edition information for the RDS Custom for SQL Server CEV instance:
```
Invoke-Sqlcmd -Query "SELECT @@VERSION AS 'Version'" -ServerInstance .
```

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 can bring your own media and create an RDS Custom for SQL Server Developer Edition CEV to optimize your operating costs in cloud. Try out Amazon RDS Custom for SQL Server Developer Edition using the CEV BYOM method 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

Sidney Grace is a Software Development Engineer on the RDS team at Amazon Web Services, focusing on commercial database engines and SQL Server. She enjoys working on technical challenges and is passionate about designing and developing database solutions that improve customer experience.

Chandra Shekar Ramavath is a Software Engineering Manager on the RDS team at Amazon Web Services, focusing on commercial database engines. He finds great satisfaction in playing a role in the delivery of simple, secure, and adaptable database solutions that meet the needs of AWS customers who use RDS/RDS Custom databases.

AWS Database Blog