In today’s digital landscape, monthly patching is vital for robust security management and ensuring a stable platform. Microsoft SQL Server, a popular choice among organizations, is often regarded as a sensitive workload due to the critical data it handles. Patching typically necessitates meticulous planning, testing, and execution, often falling to the shoulders of DBAs or systems administrators.
AWS Systems Manager Patch Manager simplifies the patching process for SQL Server, efficiently managing the patching of Microsoft applications across hybrid environments, whether on Amazon EC2 or on-premises resources. This blog post details how to utilize Patch Manager for SQL Server patching.
Prerequisites:
- Administrator access to an AWS account
- Systems Manager managed instances
- AWS CLI configured to access the relevant AWS account
Solution Overview
AWS Systems Manager Patch Manager can efficiently scan for and install missing patches on multiple instances simultaneously, utilizing EC2 resource tags and Patch Groups. Patch Manager operates with patch baselines that define rules for patch approval along with lists of approved and rejected patches. Another feature, Maintenance Window, allows scheduling of patch operations during non-disruptive timeframes for business continuity.
In this guide, we employ Patch Manager and Maintenance Window to patch Microsoft SQL Server. A patch baseline is created to include SQL Server updates, specifically targeting EC2 instances running Windows and Microsoft SQL Server 2019. We establish patch groups categorized as development and production, enabling safe testing in the development environment before deploying patches to production systems.
Pre-requisites
Ensure that the instances targeted are managed by Systems Manager. Refer to the documentation on Setting up AWS Systems Manager for guidance on prerequisites.
Tutorial
In our example, we will patch an EC2 instance running Microsoft SQL Server 2019 Standard on Windows Server 2016 Datacenter edition. The patch baseline is created through the command line, setting auto-approval application rules with a delay as needed for each environment. This delay indicates how many days to wait after a patch release before it is automatically approved for installation. For instance, if a rule is configured for five days, a critical patch released on January 1 will receive automatic approval on January 6.
It is advisable to target resources by tags when applying the same patch baseline across multiple instances. If a patch group is used, it may only be linked with a single patch baseline, necessitating separate groups for different baselines.
To create the patch baseline from the AWS CLI, execute the following command with the appropriate JSON as input (additional examples can be found in the create-patch-baseline documentation):
aws ssm create-patch-baseline --cli-input-json file://SQLServer2019AutoPatch.JSONSQLServer2019AutoPatch.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 Default
AWS Systems Manager comes equipped with default patch baselines that customers typically customize. We will set the patch baseline we created as the default for the relevant operating system and application combination. The following command accomplishes this:
aws ssm register-default-patch-baseline --baseline-id "pb-04dfe06b6da0e6fec"For those using multiple baselines with distinct auto-approval delays, this allows for patching and testing in the development environment before applying changes to production.
Patch Groups
To implement the previously described patch promotion logic, we utilize patch groups—an alternate method for targeting resources during patching. Development (dev) and production (prod) patch groups are created, ensuring EC2 instances are tagged appropriately. For example, production servers should have the tag “Patch Group” set to “Prod.” In a practical scenario, the “Dev” patch group and settings must allow sufficient time for patching and testing prior to production updates.
To associate a Patch Group with a Patch baseline, use the command:
aws ssm register-patch-baseline-for-patch-group --baseline-id pb-04dfe06b6da0e6fec --patch-group "Prod"Maintenance Window
AWS Systems Manager Maintenance Window serves as the scheduling mechanism for patching at times that minimize impact on your organization. Each Maintenance Window has a defined schedule, duration, set of registered targets, and associated tasks. When scheduling maintenance windows, development environments should be patched prior to production.
To create a maintenance window for production, use the command:
aws ssm create-maintenance-window --name "Patch-Prod" --schedule "cron(0 16 ? * TUE *)" --duration 4 --cutoff 1 --allow-unassociated-targetsWe then target the maintenance window with the previously defined Patch Group:
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"Finally, we register the task within the maintenance window using:
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-37d747c5bd17Monitoring Patch Compliance
After patching is complete, you can use the compliance feature to track the status of your patches and ensure that all instances are up to date. For a deeper dive into this topic, check out this blog post here. For more authoritative insights, this is a great resource. Also, don’t miss this excellent overview on Amazon’s onboarding experience here.
Leave a Reply