Permalink
Share
In the first part of this series, we explored how to set up and deploy SQL Server using AWS Launch Wizard. We also covered how to automate monitoring through Amazon CloudWatch Application Insights. While these tools simplify SQL Server configuration and deployment, database administrators (DBAs) and SysOps teams still invest significant time in managing daily operational tasks. In this article, we will illustrate how these teams can utilize AWS Systems Manager Application Manager to streamline, manage, and automate routine operations for SQL Server instances created with AWS Launch Wizard.
The diagram below illustrates the proposed solution using AWS Systems Manager Application Manager to oversee AWS Launch Wizard application resources.
Leveraging AWS Systems Manager Application Manager for AWS Launch Wizard Resources
Prerequisites:
To begin, ensure you have followed the previous post’s steps to deploy the SQL Server environment using AWS Launch Wizard.
Step 1: Employ AWS Systems Manager Application Manager Runbooks for SQL Server Tasks
In this step, we use AWS Systems Manager Application Manager, a feature of AWS Systems Manager, to detect, investigate, automate, and address issues with AWS Launch Wizard resources.
AWS Systems Manager Application Manager enables you to discover applications across various AWS services such as AWS CloudFormation stacks, AWS Resource Groups, Amazon Elastic Kubernetes Service clusters, and AWS Launch Wizard. After discovery, users can view operational and compliance data—like alarms, operational issues, and logs—on a single dashboard.
The AWS Systems Manager Application Manager includes predefined Automation documents (runbooks) for resolving common issues with AWS resources. An Automation document outlines actions that AWS Systems Manager can take on your managed instances and other AWS resources when an automation workflow is executed. Each document comprises multiple steps that run in sequence.
In this example, we will demonstrate backing up a specific database using the AWSSQLServer-Backup document.
From the AWS Management Console, navigate to AWS Systems Manager > Application Manager > Launch Wizard applications.
Select the deployed application to view operational and compliance data, including alarms and operational issues.
Then, click Start runbook to choose the specific runbook document to execute against the SQL Server instances created earlier.
Next, provide the details for the Database, BackupType, and other parameters. Click Execute to perform a full, differential, or transactional backup of the database.
Additionally, AWS Launch Wizard offers several predefined Systems Manager Automation documents for tasks such as backups, index maintenance, integrity checks, and database restores for SQL Server.
- Use the AWSSQLServer-DBCC document for creating a runbook to conduct integrity checks on a database; for more information, refer to this blog post for further details.
- The AWSSQLServer-Backup document allows for full, differential, or transactional backups—check this blog for more insights.
- You can also use the AWSSQLServer-Index document for index maintenance operations—find out more in this resource.
- Lastly, utilize the AWSSQLServer-Restore document for restoring database backups; this blog offers additional explanations.
Repeat these steps as necessary for various tasks on SQL Server instances.
Step 2: Use AWS Systems Manager Automation for SQL Server Configuration Management
The Automation feature of AWS Systems Manager enables you to perform operational tasks on application resources using predefined documents. You can configure and manage Amazon EC2 instances and other AWS resources using predefined or custom runbooks tailored to your requirements. Notifications on automation task statuses can also be set up to monitor progress.
In this example, we will demonstrate how to resize SQL Server Instances with the AWS-ResizeInstance document.
Open the AWS Systems Manager console.
In the navigation pane, select Automation, then choose Execute automation.
From the Automation document list, select the AWS-ResizeInstance document.
Next, specify the SQL Server Instance to resize to r5b.4xlarge and click Execute.
Step 3: Utilize AWS Systems Manager Run Command for SQL Commands
Here, we will show how to use the PowerShell Module to issue SQL commands using the AWS Systems Manager Run Command feature.
From the AWS Systems Manager console, select Run Command and choose the AWS-RunPowerShellScript document.
Then, execute the command: ‘Invoke-SqlCmd -query “select @@version” -ServerInstance “localhost”’ to verify the MS SQL version deployed in the earlier steps.
Step 4: Apply Patches to SQL Server Instances Using AWS Systems Manager Application Manager and Patch Manager
We will demonstrate how to apply on-demand patches to the SQL Server instances created by AWS Launch Wizard.
From the AWS Systems Manager console, go to Application Manager > Launch Wizard applications and select the deployed application.
Next, select the Patch button to access the patch instance page.
Choose scan and install for the patching operation, select Reboot if needed for reboot operations, and specify Patch only the target Instance I specify for targeting specific instances.
Now, let’s select the passive SQL Node and click Patch now to initiate on-demand patching.
When the Patch now operation runs, it utilizes the currently set patch baseline for your instances’ OS type, whether it’s a predefined baseline or a custom one.
Note: The Patch now option allows for immediate patching from the console, bypassing the need for a schedule to update compliance status or install patches on non-compliant instances.
After confirming successful patching on the passive node, failover the cluster resource to the fully patched SQL Node and repeat the patching steps for the other SQL node.
To delve deeper into the topic, check out another insightful blog post here. Additionally, for authoritative insights, refer to this resource.
SEO Metadata
“`
Please replace `URL_TO_BLOG_POST`, `URL_TO_BLOG`, `URL_TO_RESOURCE`, and `URL_TO_ANOTHER_BLOG_POST` with the actual URLs as needed.
Leave a Reply