Close Menu

    Perform OS upgrades for Amazon RDS Custom for SQL Server CEV with Multi-AZ

    Amazon Relational Database Service (Amazon RDS) Custom for SQL Server gives you enhanced control through OS shell-level access and database administrator privileges. With this control comes the shared responsibility model, which requires you to manage your own OS and database patching. Operating system (OS) changes made after instance creation aren’t persistent. To maintain OS-level customizations, such as OS settings and custom driver installations, you must create a new custom Amazon Machine Image (AMI), build a new CEV, and modify your existing instance to use the new CEV.

    When working with CEVs, you manage the underlying Amazon Machine Images (AMIs) in your Amazon Elastic Compute Cloud (Amazon EC2) environment. The process of updating your OS requires you to create a patched AMI, build a new CEV, and wait for it to reach Available status before applying it to your instance. This approach ensures that your OS customizations persist while maintaining the security and functionality of your database environment.

    This post demonstrates how to efficiently manage OS updates for your RDS Custom for SQL Server instances using AWS Systems Manager Patch Manager. We’ll guide you through the patching process, compare available options, and share best practices to help you maintain secure and compliant database instances. By following these practices, you can effectively manage OS updates while taking advantage of RDS Custom’s flexibility and control.

    Different Options for OS Patching in RDS Custom for SQL Server

    When performing OS patching for RDS Custom for SQL Server, you have several options that focus on updating the operating system of the EC2 instance that serves as your golden image for RDS Custom instances. The process involves patching the OS on the EC2 instance used to create your golden image AMI, then using that to update your CEV. This approach maintains separation from your production environment, as these updates are not applied directly to the EC2 instances running your production RDS Custom instances. Instead, you apply OS patches only to the EC2 instance created from your golden image—which contains your SQL Server installation, additional software, and customizations—before creating a new CEV. For detailed guidance on CEV deployment steps, see Amazon RDS Custom for SQL Server with CEV.

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

    1. Create a CEV, CEV1, for RDS Custom for SQL Server either by creating an AMI using bring-your-own-media (BYOM) or using the pre-installed SQL Server (LI) AMI. For more information, see Preparing to create a CEV for RDS Custom for SQL Server.
    2. Deploy the RDS Custom instance1 with Multi-AZ using CEV1.
    3. Apply Windows OS updates on the AMI used in Step 1 using AWS Systems Manager and create a new AMI (AMI2) and prepare a new CEV: CEV2.
    4. Validate CEV2 by creating and launching RDS Custom instance2.
    5. Modify the RDS Custom instance1 and choose the CEV with the latest OS upgrades (CEV2).
    6. When the rolling upgrades are in progress, verify the instance is still accessible.

    The following figure shows the workflow described in the preceding steps.

    Option 1: Using Windows Update Agent

    The Windows Update Agent provides a straightforward process to keeping your system secure and up to date. To use this method, first navigate to Update & Security in the Windows Settings app and then select Check for updates. Your EC2 instance must have connectivity to Microsoft patch servers to download and install the updates.

    Once the update search completes, you are presented with a list of available updates, including security patches, feature updates, and driver updates. Select the update and choose Install now to begin the download and installation process. Some updates require a system restart. Make sure you save your work before allowing the restart when prompted. After the system comes back online, verify the successful installation by running another update check.

    Keep in mind that this approach offers limited control over patch selection, as the Windows Update Agent automatically applies all Microsoft-released OS patches. Additionally, there’s a risk that Windows updates might conflict with your existing software or drivers, potentially causing system instability or compatibility issues that require troubleshooting or rollback procedures.

    Option 2: Using an AWS hosted, customer owned WSUS server

    You can use a Windows Server Update Services (WSUS) server with RDS Custom for SQL Server to manage update distribution between Microsoft and your RDS Custom instances. This approach provides full control over the distribution of Microsoft updates to computers on your network.

    While WSUS offers granular control, it’s primarily recommended for customers who manage standalone instances and require a centralized update management solution. Keep in mind that implementing WSUS server on AWS requires dedicated server resources and ongoing effort to manage and maintain the WSUS infrastructure.

    Option 3: Using AWS Systems Manager for patching

    AWS Systems Manager provides an efficient and automated approach to managing patching operations for your RDS Custom for SQL Server EC2 instances, including your golden image mentioned in this post. When a patching operation is initiated, the AWS-RunPatchBaseline document is triggered, which downloads and executes a PowerShell module. This module retrieves a snapshot of the applicable patch baseline for the EC2 managed node, comprising a list of approved patches. This list then is processed by the Windows Update API or by a Windows Server Update Services (WSUS) server if already configured to identify and install applicable patches.

    Key benefits of using Systems Manager for patching include:

    Automated OS patching

    • Eliminates manual AMI updates
    • Maintains instance compliance without rebuilding

    Centralized management

    • Simplifies compliance tracking
    • Ensures consistent patching across multiple regions

    Flexibility

    • Customizable patch baselines which assists in distributing and applying updates to software
    • Scheduling options available

    Prerequisites

    To demonstrate patching with Systems Manager, you must have these prerequisites:

    As a part of the OS patching, you will modify some of the outbound network settings. To help avoid impacting the production network, make sure that you don’t use the same network setup for the production instance as for the EC2 instance where OS patching is performed.

    Network setup

    Before you begin, configure the following network settings to enable the AWS-RunPatchBaseline document execution and ensure connectivity for Windows updates. This section guides you through using an AWS CloudFormation template to configure networking for your EC2 instance (demo-server-ssm).

    • In the EC2 console, choose Instance ID for the EC2 instance created by AMI1 (demo-server-ssm in this example) and then navigate to the Security section and choose the Inbound rules and Outbound rules tabs to view the Inbound and Outbound rules.
    • Add an outbound rule to the security group. Allowing IPV4 ports 80 and 443 open to 0.0.0.0/0. For more information about these ports see the Microsoft documentation.
    • Create a NAT gateway to allow instances in a private subnet to communicate with the internet. Enter a name, and under Subnet select the public subnet from the dropdown.
    • In the VPC console, select the associated route tables with the instance VPC. Choose Edit routes and associate the newly created NAT gateway to the private route table.
    • If using a predefined AWS CloudFormation template, the Public Network ACL created from RDS Custom cloud formation template doesn’t allow inbound and outbound traffic to the Windows server from the public subnet by default. Edit or add a rule to allow 0.0.0.0/0.

    Either edit an existing inbound rule or add a new rule the under Inbound rules section to allow 0.0.0.0/0.

    • Similarly, either edit an existing outbound rule or add a new rule under the Inbound rules section to allow 0.0.0.0/0.
    • If using predefined AWS CloudFormation template By default, the Private Network ACL does not allow outbound traffic to the Windows server from the private subnet. Edit or add rules to allow 0.0.0.0/0 to Outbound traffic to port 80 and 443.

    After the network setup is done, you can use the Systems Manager CLI command ssm-cli get-diagnostics --output table to validate the configuration. For more information see Troubleshooting managed node availability using ssm-cli.

    For more details, see Working with custom engine versions for RDS Custom for SQL Server. This solution involves the creation and use of new AWS resources. Therefore, it will incur costs on your account. See AWS Pricing for more information. We strongly encourage that you set this up in a non-production environment and run end-to-end validations before implementing this solution in your production environment.

    Solution Overview for OS patching using Systems Manager

    This solution shows you how to use AWS Systems Manager to update the Windows operating system on your EC2 instance (demo-server-ssm) before creating a golden AMI. The process consists of scanning your EC2 instance for missing patches, installing required updates, and validating the updates before AMI creation. By following these steps, you’ll ensure your golden AMI includes all necessary security and performance updates.

    Steps for patching using AWS Systems manager:

    1. Go to the AWS Systems Manager console, choose Node Management in the navigation pane and select Patch Manager. On the Patch Manager page, choose Patch now.
    2. On the Patch instances now page, use the following settings.
      • Patching operation: Select Scan and install.
      • Reboot option: Select Reboot if needed.
      • Instances to patch: Select Patch only the target instances I specify.
      • Choose Patch now.

    1. For reference, the following image shows the default patch baseline which is a collection of patches that are approved for installation on a managed node:
    2. After initiating patching, select the Execution history tab to see the associated executions for the respective instances and their execution status. Select an Execution ID to see the details for the associated targets and review the output of their execution.
    3. Choose Output to review the execution details.
    4. The Output page shows the status, details, and other information.

    You can also review the complete command output by selecting the Amazon Simple Storage Service (Amazon S3) tab on the Output page.

    To perform an OS upgrade with AWS CLI using Systems Manager, see CLI patch documentation.

    Options for monitoring patch compliance

    You can validate patch executions using the Systems Manager console.

    In the Systems Manager console, choose Patch Manager in the navigation pane. Then select the Compliance reporting tab to see a summary of the executions. To learn more, see Learn details about Compliance.
    You can access the detailed Compliance report by selecting an execution and choosing View log.

    The output will provide the status and output details for that execution.

    Follow the below steps for the RDS custom instance upgrade:

    1. Run Sysprep on the EC2 instance demo-server-ssm.
    2. Create a new EC2 image named AMI2.
    3. Create a new CEV named cev2 using AMI2.
    4. Validate cev2 by launching an RDS Custom instance.

    For detailed step-by-step guidance for the above mentioned steps, refer to the deployment steps section from Performing a minor version upgrade for Amazon RDS Custom for SQL Server.

    After CEV2 reaches the AVAILABLE state, modify the existing RDS Custom instance using the modify-db-instance command.

    Considerations

    In this section, we discuss considerations around certain key SQL Server features related to OS upgrades for Amazon RDS Custom for SQL Server CEV with Multi-AZ.

    • Group Policy settings for Windows Update remain active because Windows Update API handles patch downloads and installations.
    • Patch Manager does not support Out-of-band (OOB) updates. These updates are released outside the regular monthly schedule to address critical vulnerabilities and are cumulative, replacing previous updates. They supersede prior monthly security updates and optional non-security preview releases.
    • Application patches are code changes that fix bugs, vulnerabilities, or performance issues in software applications. These patches, also known as fixes or bug fixes, are not included in the default patch baseline, which only includes OS patches by default.
    • Potential delays or issues during patching with Patch Manager include patching failures during installation, which might prevent new patches from being installed until the failure issue is resolved. Corresponding instances will report Not compliant on the Dashboard. For more details see Troubleshooting Patch Manager.

    Clean up

    To prevent ongoing charges, clean up your resources in this order:

    1. Delete your RDS Custom for SQL Server DB instance.
    2. Delete the test RDS Custom instance you created after the CEV has been validated. For instructions, see Deleting a DB instance.
    3. Delete your EC2 instance.
    4. If you created the prerequisite resources (network, instance profile, and encryption key) using the provided CloudFormation template, delete the stack to remove the resources.

    Summary

    We’ve shown you how to effectively manage OS updates for Amazon RDS Custom for SQL Server using CEV deployments. You learned about three patching options, with a focus on AWS Systems Manager Patch Manager as an efficient, automated solution. This approach helps you maintain consistent OS patching across your RDS Custom for SQL Server instances, automate patch compliance monitoring and reporting, implement controlled updates using customizable patch baselines, and successfully manage Multi-AZ deployments during the update process.

    To get started, you can use our step-by-step guidance to implement OS patching in your non-production environment. After validating the process, you can confidently deploy this solution in your production environment. If you have questions or need clarification, please use the comments section below.

    About the authors

    Kalyan Banala is an accomplished Database Engineer on the Amazon RDS Custom for SQL Server team at AWS. He enjoys solving complex technical challenges and is passionate about both learning from and sharing knowledge with teammates and AWS customers.

    Kanwar Nain Singh is a Senior Specialist Solutions Architect at Amazon Web Services. He specializes in engineering and architecting data migrations, as well as modernizing both relational and non-relational databases on AWS.

    Bhavani Akundi is a Senior Database Consultant at AWS Professional Services. She leverages her extensive experience across database technologies to help customers migrate and modernize their databases and applications on AWS. Outside of work, Bhavani enjoys spending time with family, traveling, and listening to music.

    Binod Bhandari is a Senior Software Development Engineer, focusing on RDS and commercial databases, particularly SQL Server. He is driven by the opportunity to solve intricate technical problems and create database solutions that elevate the customer experience for RDS and RDS Custom for SQL Server customers.

    Source: Read More

    Related Posts

    Leave A Reply

    For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.