AWS Database Blog

Manage Amazon RDS Custom for SQL Server CEV AMIs using EC2 Image Builder

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server allows you to use a custom engine version (CEV) by providing an Amazon Machine Image (AMI), which includes specific customizations and database media installed on it. CEV allows you to maintain your preferred baseline configuration of the OS and database to deploy fleets of RDS Custom for SQL Server DB instances. CEV ensures the host configuration such as any third-party applications or agents are persisted. To summarize, once you identified your requirement to install software such as database drivers, agent software or support Bring your own Media (BYOM) to leverage your existing SQL Server licenses, you will need to manage the lifecycle of the AMI and the CEV associated with it to persist those configuration changes.

Managing the RDS Custom CEV is the customer’s responsibility and this requires you to prepare, build, and distribute the AMI across different AWS accounts and Regions to create your RDS Custom instances.

In this post we provide you guidance and best practices to build, test, and distribute AMIs using an EC2 Image Builder pipeline.

Solution overview

The following diagram illustrates our solution architecture.

In this post, we show how you can use Image Builder to create a custom AMI and use it for your CI/CD deployment.

The following is a graphical representation that outlines the process.

The process includes the following steps:

  1. The user runs an Image Builder pipeline that provides an automation framework for the image creation process. The pipeline is associated with a recipe that includes the source image. For this post, we use a SQL Server AMI provided by AWS in Preparing to create a CEV for RDS Custom for SQL Server.
  2. Build components configure the source image with several custom scripts that were defined for this project. Most of them install and configure database tools that DBAs can use to connect and manage their purpose-built databases (more about these components in the next section).
  3. Image Builder, based on the OS of the source image, performs actions to secure the output image (such as making sure security patches are applied, enforcing strong passwords, or turning on full disk encryption).
  4. Optionally, test components are run against the output image.
  5. The output image, once configured and tested, is ready to be distributed based on the distribution settings specified.

In the following sections, we go through the steps to create an image pipeline. For more information, refer to Create an image pipeline using the EC2 Image Builder console wizard.

Prerequisites

Before we get started, let’s create our build and test components to use in our pipeline.

Create a build component

In the build component, we change SQL max memory settings and enable XP_cmdShell.

First, Open the EC2 Image Builder console and complete the following steps:

  1. Choose Create component and select Build as the component type.
  2. Under Component details, choose Windows as the operating system.
  3. Select Microsoft Windows Server 2019 under Compatible OS Versions.
  4. For Component name, enter SQLConfigure.
  5. Specify Component version as 1.0.1.
  6. Copy the following YAML code, which changes SQL MaxMemorySettings and enables the command shell in your SQL Server 
    name: Run_SQL_Config
description: 'This document will make changes to SQL Server configuration. It will then validate the changes before an AMI is created. Then after AMI creation, it will test all the changes.'
schemaVersion: 1.0
phases:
  - name: build
    steps:
      - name: ChangeSQLConfigurationSettings
        action: ExecutePowerShell
        onFailure: Abort
        timeoutSeconds: 60
        inputs:
          commands:
            - 
              |
              Install-PackageProvider -Name "Nuget" -RequiredVersion "2.8.5.201" -Force
              Import-PackageProvider -Name "Nuget" -RequiredVersion "2.8.5.201" -Force
              Install-Module SQLServer -Force
              Import-Module SQLServer -Force

              $sqlquery = @"
              sp_configure 'show advanced options', 1
              reconfigure
              GO
              sp_configure 'max server memory (MB)', 12388
              reconfigure
              GO
              EXECUTE sp_configure 'xp_cmdshell', 1;
              GO
              RECONFIGURE;
              GO
              "@
              Invoke-SqlCmd -Query $sqlquery -ServerInstance . -Database master
  7. Choose Create component.

This creates a build component to use in our image pipeline.

Create a test component

We also create a test component to see the changes that have been persisted after creating an AMI. Open the EC2 Image Builder console and complete the following steps:

  1. Choose Create component and choose Test as the component type.
  2. Under Component details, choose Windows as the operating system.
  3. Select Microsoft Windows Server 2019 under Compatible OS Versions.
  4. For Component name, enter SQLTestConfigurations.
  5. Specify Component version as 1.0.1.
  6. Copy the following YAML code, which changes SQL MaxMemorySettings and enables the command shell in your SQL Server 
    name: Test_SQL_Config
description: 'This document will make changes to SQL Server configuration. It will then validate the changes before an AMI is created. Then after AMI creation, it will test all the changes.'
schemaVersion: 1.0
phases:
  - name: test
    steps:
      - name: CheckChangedSQLConfigurationSettings
        action: ExecutePowerShell
        onFailure: Abort
        timeoutSeconds: 60
        inputs:
          commands:
            - 
              |
              $desiredmemory=16385
              $exitWithFailure = 1
              $sqlquery = @"
              EXECUTE sp_configure 'max server memory (MB)'
              GO
              EXECUTE sp_configure 'xp_cmdshell'
              GO
              "@
              
              
              $configSettings= @(Invoke-SqlCmd -Query $sqlquery -ServerInstance . -Database master) 
              $result = foreach ($item in $configSettings) {
                  [PSCustomObject]@{
                      Name = $item.name;
                      Value = $item.run_value
                  }
              }
              
              $value = ($result | Where-Object { $_.name -eq "max server memory (MB)" }).Value
              
              
              if ($value -ne $desiredmemory) {
                  Write-Host "$value is not the desired memory $desiredmemory"
                  [System.Environment]::Exit($exitWithFailure)
              } else {
                  Write-Host "$value is the desired memory $desiredmemory"
                  
              }
  7. Choose Create component.

This creates the test component to use in our image pipeline.

Specify the pipeline details

Now we’re ready to build the image pipeline. Complete the following steps:

  1. On the Image Builder console, choose Create image pipeline.
  2. For Pipeline name, enter a name.
  3. For Description, enter a description.
  4. For Schedule options, select Manual to run the initialization manually.
  5. Choose Next to proceed to the next step.

Choose a recipe

Image Builder defaults to using an existing recipe. To create a new recipe, Open the EC2 Image Builder console and complete the following steps:

  1. Choose Create new recipe.
  2. In the Image type section, choose Amazon Machine Image (AMI) to create an image pipeline that will produce and distribute an AMI.
  3. For Name, enter your recipe name.
  4. For Version, enter your recipe version (use the format <major>.<minor>.<patch>, where major, minor, and patch are integer values). New recipes generally start with 1.0.0.
  5. In the Source image section, choose Select managed images and under Image Operating System, select Windows.
  6. For Image name, choose an image that is supported for RDS Custom for SQL Server based on your Region.
    Note: To know the supported SQL Server cumulative update (CU) supported by RDS Custom for SQL Server, refer to the documentation. Always make sure you are building AMI with supported SQL Server CU installed to avoid mismatch error between SQL Server CU and CEV version.
  7. Keep the default for Auto-versioning options as Use latest available OS version.
  8. Keep User data blank for this tutorial.

You can use this area at other times to provide commands, or specify a command script to run when you launch your build instance. However, it replaces any commands that Image Builder might have added to ensure that AWS Systems Manager is installed. When you do use it, make sure that the Systems Manager agent is preinstalled on your base image, or that you include the install in your user data.

In the Build components – Windows section, change the drop-down menu to Owned by me to filter your build components.

You must choose as least one build component.

  1. Select the SQLConfigure build component that you built earlier.
  2. Under Selected components, choose Expand all.
  3. Keep the default for Versioning options as Use latest available component version.
  4. For Select tests to verify the output AMI (post-build), select the test component you created.
  5. Repeat these steps to select the build component.
  6. In the Storage (volumes) section, leave the default options.
  7. Choose Next to define the infrastructure configuration.

Define the infrastructure configuration (optional)

Image Builder launches EC2 instances in your account to customize images and run validation tests. The infrastructure configuration settings specify infrastructure details for the instances that will run in your AWS account during the build process. To perform this action, Open the EC2 Image Builder console and complete the following steps:

  1. In the Infrastructure configuration section, leave Configuration options at the default Create infrastructure configuration using service defaults.

This creates an AWS Identity and Access Management (IAM) role and associated instance profile for the EC2 build and test instances that are used to configure your image. For more information about infrastructure configuration settings, refer to CreateInfrastructureConfiguration. For this post, we use the default settings.

  1. To specify a subnet to use for a private VPC, you can create your own custom infrastructure configuration or use settings that you have already created.
  2. Choose Next to proceed to the next step.

Define distribution settings (optional)

After you create distribution settings with Image Builder, you can manage them using the Image Builder console. So open the EC2 Image Builder console and complete the following steps:

  1. In the Distribution settings section, leave Configuration options at the default Create distribution settings using service defaults.

This option distributes the output AMI to the current Region. For more information about configuring your distribution settings, see Manage EC2 Image Builder distribution settings. For this tutorial, we use the default settings.

  1. Choose Next to proceed to the next step.

Review your settings (Optional)

The Review section displays all the settings you have configured. So, Open the EC2 Image Builder console and complete the following steps:

  1. To edit information in any given section, choose Edit in the relevant section.

For example, to change your pipeline name, choose Edit in the Step 1: Pipeline details section.

  1. After you have reviewed your settings, choose Create pipeline to create your pipeline.

You can see success or failure messages at the top of the page as your resources are created for distribution settings, infrastructure configuration, your new recipe, and the pipeline.

  1. To see the details for a resource, including the resource identifier, choose View details.

After you have viewed the details for a resource, you can view details about other resources by choosing the resource type from the navigation pane. For example, to see details for your new pipeline, choose Image pipelines from the navigation pane. If your build was successful, your new pipeline is displayed in the Image pipelines list.

Run the pipeline manually

To run the pipeline, complete the following steps:

  1. Choose Image pipelines in the navigation pane.
  2. Select you pipeline.
  3. On the Actions menu, choose Run pipeline.

After the pipeline runs successfully, you can see the image getting created in your list of EC2 AMIs.

You can track the current build status and image build steps for their image pipelines directly in EC2 Image Builder. All image build logs are consolidated in the workflow section in the EC2 Image Builder Console. This capability makes it easier for you to track image builds and troubleshoot build failures.

Clean up

It’s a best practice to delete resources that you’re no longer using so you don’t incur unintended charges. Complete the following steps to clean up the resources you created for this post:

  1. Delete the EC2 instance.
  2. Delete the Amazon EBS volume.
  3. Delete the CEV for RDS Custom for SQL Server.

Summary

The advantage of using CEV is that it can persist the configuration changes made to your EC2 instance. In this post, we showed how to use the AMI from the EC2 instance as an RDS Custom for SQL Server CEV. Image Builder significantly reduces the effort of keeping images up to date and secure by providing a simple graphical interface, built-in automation, and AWS-provided security settings. Image Builder automates the process of updating images without the need to build your own automation pipeline. We hope this post helps you create your own custom SQL AMIs with Image Builder in your development or production environments and streamline the process.

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

About the authors

Vikas Babu Gali is a Sr. Specialist Solutions Architect, focusing on Microsoft Workloads at Amazon Web Services. As a native of India, Vikas enjoys playing Cricket and spending time with his family and friends outdoors.

Sudhir Amin is a Database Specialist Solutions Architect at Amazon Web Services. In his role based out of New York, he provides architectural guidance and technical assistance to enterprise customers across different industry verticals, accelerating their cloud adoption. He is a big fan of snooker, combat sports such as boxing and UFC, and loves traveling to countries with rich wildlife reserves where he gets to see world’s most majestic animals up close.

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.