Persist your OS-level customization within Amazon RDS Custom for SQL Server using Custom Engine Version (CEV)

Customers running their Microsoft SQL Server workloads on Amazon Relational Database Service (Amazon RDS) Custom for SQL Server ask us how they can persist changes at the operating system (OS) level even if Amazon RDS chooses to perform a host replacement. In the context of RDS Custom for SQL Server, a host replacement is when the OS disk (C:\ drive) is replaced with a new OS image.

Customers with commercial-of-the-shelf (COTS) applications often require administrative access to the SQL Server instance and its underlying OS. Amazon RDS Custom is a managed database service for legacy, custom, and packaged applications that require access to the underlying OS and database environment. Amazon RDS Custom for SQL Server automates the setup, operation, and backup of databases in the AWS cloud while granting you access to the database and underlying OS. With Amazon RDS Custom, you get the automation of Amazon RDS with flexibility similar to Amazon Elastic Compute Cloud (Amazon EC2).

In this post, we examine a use case in which you make changes at the OS level of the RDS Custom for SQL Server instance by installing software. We also demonstrate how you can persist the changes even when Amazon RDS chooses to do a host replacement using a custom engine version (CEV).

Overview of CEV

A custom engine version (CEV) for Amazon RDS Custom for SQL Server is, essentially, an Amazon Machine Image (AMI) with Microsoft SQL Server pre-installed. You choose an AMI from the Amazon EC2 Windows AMI list to use as a base image. You can then install other third-party applications on the OS or customize the configuration of the OS and SQL Server to meet your enterprise needs. You then save the AMI as a golden image to create a CEV and create Amazon RDS Custom for SQL Server instances by choosing the CEV.

Solution overview

For our use case, we take a scenario in which our company mandates the following:

Install a PostgreSQL driver and other software at the OS level of the RDS Custom for SQL Server instance for a simple integration with other data sources through the linked server feature
Use a custom image to launch the RDS instance, and achieve consistency and efficiency while creating RDS instances at scale
Be able to persist the OS-level customization even when Amazon RDS chooses to perform a host replacement

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:

Prepare to create a CEV.
Locate AWS provided media and launch an EC2 instance.
Verify the major and minor version.
Customize at the OS and SQL level.
Perform Sysprep.
Create a golden AMI.
Create the CEV.
Create an RDS Custom for SQL Server instance using CEV.

Prerequisites

To test this solution, you need the following prerequisites:

An AWS account
An understanding of using an Amazon Machine Image (AMI)
The latest version of the AWS Command Line Interface (AWS CLI) installed and configured on a Linux host
An RDS for PostgreSQL instance created with a sample database to test the linked server.
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)

Prepare to create a CEV

Before you create a CEV, 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 instance. In our testing, we use SQL Server 2019 CU17, supported in the us-west-2 region for SQL Server Standard Edition (SE) on a Windows Server 2019 OS.

Locate AWS-provided media and launch an EC2 instance

The RDS Custom for SQL Server CEV supports the license included (LI) option only, so we use the AWS-provided media. The media we pick in our testing, CU17, must match the SQL Server major and minor version with one of the engine versions listed in the region and version availability section. To identify the AMI and launch an EC2 instance, follow these steps:

Navigate to AWS Windows AMI version history.
Search for CU17 and take a note of the Release number. In our case, it’s 2022.09.14.
Navigate to the AWS Management Console and log in with your AWS account.
Navigate to the EC2 Dashboard.
Choose AMIs in the navigation pane under Images.
Choose Public images on the drop-down menu and search with 2022.09.14 and Windows_Server-2019-English-Full-SQL_2019.
Select the AMI for the Standard Edition and choose Launch instance from AMI.
For Name, enter a name for the instance.
Choose Instance type on the drop-down menu.Choose an appropriate EC2 instance type to run the SQL Server Engine as well as additional software you will install.
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.
Configure additional EBS volumes if needed to download software. Additional volumes you add at this step are not persisted in the Golden image.
Choose Launch instance.

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 SQL Server Management Studio (SSMS) and run the following command to check the SQL Server minor version:

SELECT @@VERSION

In our case, we find the engine minor version matches exactly with one of the supported CEV versions. If you find the instance you have created from the AMI has a different minor version, you can use the uninstall guidance to match the CEV supported minor version.

Customize at the OS and SQL level

In our testing, we install a PostgreSQL driver on the EC2 instance. To complete the install, follow these steps:

Remote Desktop to the EC2 instance.
Mount the additional 240 GB EBS volume as D:\ drive to use as the software repository.
Download the pSQLODBC_x64 driver.
Locate the msi directory and choose the latest 64-bit version, which in our case is psqlodbc_13_02_0000-x64.zip.
Copy the downloaded psqlodbc_13_02_0000-x64.zip file to the D:\ drive.
Extract all of the content in the same D:\ location.You will see psqlodbc-setup.exe and README.txt in the extracted folder location D:\psqlodbc_13_02_0000-x64.
Accept the terms in the License Agreement and start the install.
When the installation is complete, search for ODBC Data Source Administrator (64-bit).
Navigate to the System DSN tab and choose Add.
Choose PostgreSQL Unicode(x64) from the driver list and enter the PostgreSQL connection details.
Choose Test and on a successful result, save it.
Now open SSMS and connect to the SQL instance, then choose New Query.

Run the following SQL command to create the linked server. You can replace the parameters according to your requirements:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL-LINK', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'PostgreSQL13'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'POSTGRESQL-LINK', @locallogin = NULL , @useself = N'False', @rmtuser = N'postgres', @rmtpassword = N'*************'
GO

Navigate to the linked server in SSMS Object Explorer and browse under Catalogs to confirm it’s configured.

Perform Sysprep

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

Log in to the console and navigate to the EC2 Dashboard.
Select the EC2 instance and on the Actions menu, choose Instance settings.
Choose Change Shutdown behavior.
Confirm that the Terminate option isn’t selected.
Remote Desktop to the EC2 instance.
On the Start menu, choose Ec2LaunchSettings.
In the Ec2 Launch Settings window, choose Random (Retrieve from console) in the Administrator Password section.
Choose Shutdown with Sysprep.
Choose Apply and then Yes to proceed.

Create a golden AMI

To create the golden AMI, perform the following steps:

On the EC2 Dashboard, verify that the EC2 instance is in the Stopped state.
Select the EC2 instance and on the Actions menu, choose Images and templates.
Choose Create image.
Enter an image name and choose Create image.
Navigate to the EC2 Dashboard.
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. You can replace the parameters according to your requirements.

aws rds create-custom-db-engine-version \
--engine custom-sqlserver-se \
--engine-version 15.00.4249.2.cev.postgres.linkedserver \
--description "Custom SQL Server 15.00.4249.2 cev postgre linked server" \
--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:

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

We get the following output:

{
            "Engine": "custom-sqlserver-se", 
            "Status": "pending-validation", 
            "DBParameterGroupFamily": "custom-sqlserver-se-15.0", 
            "DBEngineVersionArn": "arn:aws:rds:us-west-2:<account id>:cev:custom-sqlserver-se/15.00.4249.2.cev.postgres.linkedserver/e5238b64-12a8-40ef-96ae-0cb8c309a655", 
            "MajorEngineVersion": "15.00", 
            "SupportsReadReplica": false, 
            "Image": {
                "Status": "pending-validation", 
                "ImageId": "ami-0cf770cd2ca6a9e8b"
            }, 
            "SupportsBabelfish": false, 
            "TagList": [], 
            "DBEngineDescription": "Microsoft SQL Server Standard Edition for custom RDS", 
            "SupportedFeatureNames": [], 
            "SupportsLogExportsToCloudwatchLogs": false, 
            "SupportsGlobalDatabases": false, 
            "SupportsParallelQuery": false, 
            "EngineVersion": "15.00.4249.2.cev.postgres.linkedserver", 
            "DBEngineVersionDescription": "Custom SQL Server 15.00.4249.2 cev postgre linked server", 
            "ValidUpgradeTarget": [], 
            "CreateTime": "2022-11-25T03:00:35.077Z", 
            "DBEngineMediaType": "AWS Provided"
        }

Create an RDS Custom for SQL Server using 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 CEV, use the following AWS CLI command. You can replace the parameters according to your requirements.

aws rds create-db-instance \
--engine custom-sqlserver-se \
--engine-version 15.00.4249.2.cev.postgres.linkedserver \
--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.
Navigate to the linked server in SSMS Object Explorer and browse under Catalogs to confirm its configured.
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'

Perform a host replacement test

We perform a host replacement test by updating the compute size using the following AWS CLI command:

aws rds modify-db-instance \
--db-instance-identifier rds-custom-sql-server-cev-instance \
--db-instance-class db.r5.2xlarge \
--apply-immediately

When the RDS instance is available, perform the following steps to revalidate:

Remote Desktop to the EC2 host created as a prerequisite.
Open SSMS and connect to the RDS Custom for SQL Server CEV instance endpoint.
Navigate to the linked server in SSMS Object Explorer and browse under Catalogs to confirm it’s configured.

Reenter the linked server security credential using the following SQL command:

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'POSTGRESQL-LINK', @locallogin = NULL , @useself = N'False', @rmtuser = N'postgres', @rmtpassword = N'*************'
GO

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'
```

We find the PostgreSQL linked server configuration is persisted even with host replacement.

Clean up

To avoid future charges and to 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.

Other applications

Amazon RDS Custom for SQL Server’s CEV feature can be leveraged in the following use cases:

Reduce impact for your Amazon RDS Custom for SQL Server instance while applying Cumulative Updates by modifying the RDS instance with a new CEV. For more information check Modifying an RDS Custom for SQL Server DB instance to use a new CEV.
Add or remove software from your Amazon RDS Custom for SQL Server instance to support new application functionality or adhere to your corporate policies by modifying the RDS instance with a new CEV. For more information check Modifying an RDS Custom for SQL Server DB instance to use a new CEV.

Summary

In this post, we demonstrated how you can use an RDS Custom for SQL Server CEV to persist OS-level changes even when Amazon RDS chooses to perform a host replacement. Try out RDS Custom for SQL Server CEVs to persist your operating system customizations.

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

About the author

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