SQL Server patching for hybrid cloud with AWS Systems Manager

In most organizations, monthly patching is a critical part of their security management process, and a requirement to maintain a secure and stable platform. Microsoft SQL Server is widely used and is usually categorized as a sensitive workload due to the data it stores. In most cases, patching requires planning, testing, and careful execution. It is often performed manually by DBAs or systems administrators.

AWS Systems Manager Patch Manager can help alleviate the heavy lifting that goes into SQL Server patching. Patch Manager can patch Microsoft applications in hybrid environments, like Amazon EC2 or on-premises resources, with a workflow that includes patch testing. In this blog post, we outline the process for patching SQL Server using Patch Manager.

Prerequisites:

• Administrator access to an AWS account
• Systems Manager managed instances
• AWS CLI configured to access the appropriate AWS account

Solution

AWS Systems Manager Patch Manager can scan and install missing patches to one or many instances, using EC2 resource tags and via Patch Groups. Patch Manager uses patch baselines, which include rules for patch approval and a list of approved and rejected patches. Another component of Systems Manager, Maintenance Window, is used to schedule the patch operations during non-disruptive times for the business.

The patching solution outlined in this blog uses Patch Manager and Maintenance Window to patch Microsoft SQL Server. We define a patch baseline that includes SQL Server updates and targets EC2 instances running Windows and Microsoft SQL Server 2019. These instances use patch groups defined as development and production to map the appropriate environments so that patches can be tested in the development environment, then installed in production once testing has deemed them safe.

Pre-requisites

Make sure the instances you are targeting are managed by Systems Manager. Reference the Setting up AWS Systems Manager documentation to get started with the prerequisites.

Tutorial

In this example, we are patching an EC2 instance with Microsoft SQL Server 2019 Standard running on Windows 2016 Datacenter edition. We create the patch baseline via the command line, and define auto-approval application rules to include auto-approval delay, per environment as needed. This delay is the number of days to wait after the patch was released, before the patch is automatically approved for patching. For example, if you create a rule using the Critical Updates classification and configure it for five days’ auto-approval delay, then a new critical patch released on January 1 will automatically be approved on January 6.

Our recommendation is to target resources using tags, if they are all going to be patched with the same patch baseline. If a patch group is used, it can only be registered with one patch baseline. Use separate patch groups for different patch baselines.

To create the patch baseline described above from the AWS CLI, we execute the following command with the appropriate JSON as an input (additional examples of patch baseline configuration can be found in the create-patch-baseline documentation):

aws ssm create-patch-baseline --cli-input-json file://SQLServer2019AutoPatch.JSON

SQLServer2019AutoPatch.JSON

{
    "Name": "SQLServer2019Patching",
    "Description": "Auto Patch the Critical updates in SQL Server(s) after 5 days from release",
    "OperatingSystem": "WINDOWS",
    "ApprovalRules": {
        "PatchRules": [{
            "ApproveAfterDays": 5,
            "ComplianceLevel": "CRITICAL",
            "PatchFilterGroup": {
                "PatchFilters": [{
                        "Key": "CLASSIFICATION",
                        "Values": [
                            "CriticalUpdates"
                        ]
                    },
                    {
                        "Key": "MSRC_SEVERITY",
                        "Values": [
                            "Critical"
                        ]
                    },
                    {
                        "Key": "PRODUCT",
                        "Values": [
                            "Microsoft SQL Server 2019"
                        ]
                    },
                    {
                        "Key": "PRODUCT_FAMILY",
                        "Values": [
                            "SQL Server"
                        ]
                    },
                    {
                        "Key": "PATCH_SET",
                        "Values": [
                            "APPLICATION"
                        ]
                    }
                ]
            }
        }]
    }
}

Setting a baseline as a default

Systems Manager comes with default patch baselines, which most customers customize. We set the patch baseline we just created as default for the operating system and application combination.

The following command is used to set a patch baseline as a default:

aws ssm register-default-patch-baseline --baseline-id "pb-04dfe06b6da0e6fec"

If you use multiple patch baselines with different auto-approval delays, you can perform patching and testing in the development environment before patching production.

 Patch groups

In order to apply the patch promotion logic we described, we use patch groups, which are alternative way of targeting resources during patching. We create patch groups for development (dev) and production (prod). In order to do this, we must make sure that the EC2 instances have the appropriate tags associated with them. For example, the production servers should have the reserved key “Patch Group,” set to the appropriate value, “Prod.” In a real world example, we would also make sure the “Dev” patch group and appropriate settings were defined to include enough time for patching and testing before the patching that is performed in production.

We can then associate a Patch Group and Patch baseline using the following command:

aws ssm register-patch-baseline-for-patch-group --baseline-id pb-04dfe06b6da0e6fec --patch-group "Prod"

Maintenance Window

AWS Systems Manager Maintenance Window is the mechanism used to schedule patching for a time when there is the least impact to your organization. Each Maintenance Window has a schedule, a duration, a set of registered targets, and a set of registered tasks. When creating the maintenance window schedules, development environments should be patched before production.

We use the following command to create the maintenance window for the production environment:

aws ssm create-maintenance-window--name "Patch-Prod" --schedule "cron(0 16 ? * TUE *)"--duration 4 --cutoff 1 --allow-unassociated-targets

We will then target the maintenance window using the Patch Group we defined earlier:

aws ssm register-target-with-maintenance-window--window-id mw-03ea4b76f5bb08c02--targets "Key=tag:Patch Group,Values=Prod"--owner-information "Production servers"--resource-type "INSTANCE"

We will then register the task and the maintenance window using the following command:

aws ssm register-task-with-maintenance-window --window-id mw-03ea4b76f5bb08c02  --task-arn "AWS-RunPatchBaseline" --service-role-arn "arn:aws:iam::[AWS_ACCOUNT_ID]:role/MW-Role" --task-type "RUN_COMMAND" --max-concurrency 2 --max-errors 1 --priority 1 --task-parameters "{\"Operation\":{\"Values\":[\"Install\"]}}" --targets Key=WindowTargetIds,Values=55372e9d-82e4-4242-a2dd-37d747c5bd17

Monitoring patch compliance

Once the patching is performed, you can use the Compliance dashboard or Systems Manager Explorer to determine the status of patch compliance. You can also view the patches installed by going to the Managed Instances console and clicking on the SQL Server instance that was patched. After that, select the Inventory tab and enter a filter to show the SQL Server components. We used Name: Begin with: Microsoft SQL Server, as our filter.

Conclusion

In this blog post, we covered how to use Systems Manager Patch Manager to patch SQL Server. We started by showing you how to create a patch baseline that defines what patches to install. We then used the patch groups to discuss how a patch promotion workflow can be created for development and production environments. Finally, Maintenance Window was used to roll out patches, to support the patch promotion process. To learn more about patching Microsoft applications, visit the About patching applications on Windows Server documentation page.

Additional references

(a) Learn more about patching Windows using AWS Systems Manager:
https://aws.amazon.com/blogs/mt/patching-your-windows-ec2-instances-using-aws-systems-manager-patch-manager/

(b) Version and edition history of Microsoft SQL Server and its components: https://support.microsoft.com/en-us/help/4518398/sql-server-2019-build-versions

(c) AWS Systems Manager command line interface reference: https://docs.aws.amazon.com/cli/latest/reference/ssm/index.html#cli-aws-ssm

About the authors

Shree Chinnasamy is a technical account manager at AWS. He focuses on driving operational excellence for his customers on AWS cloud. He is an avid reader and a 2019 Chicago Marathon finisher.

Garry Singh is a solutions architect at AWS. He provides guidance and technical assistance to help customers achieve the best outcomes for Microsoft workloads on AWS.