Microsoft Workloads on AWS

Simplifying SQL Server on Amazon EC2 experience with AWS Launch Wizard, Amazon CloudWatch Application Insights and AWS Systems Manager Application Manager: Part 2

In Part1 of this series, you learned how to configure, deploy SQL Server using AWS Launch Wizard. Next, you also learned how to setup, automate monitoring using Amazon CloudWatch Application Insights.

Although this does simplify the configuration and deployment of SQL Server, the DBAs and SysOps teams still spend considerable time doing, completing day to day operational tasks on SQL Server.

In this post, we’ll demonstrate how DBAs and SysOps teams can use AWS Systems Manager Application Manager to simplify, manage and automate day to day operations of AWS Launch Wizard application resources i.e SQL Server instances.

The following diagram shows the proposed solution using AWS Systems Manager Application manager to manage AWS Launch Wizard application resources.

How to leverage AWS Systems Manager Application Manager to manage AWS Launch Wizard application resources

Pre-requisite

To get started, follow the steps outlined in the previous post to deploy the SQL Server environment using AWS Launch Wizard and follow the steps as indicated.

Step 1: Use AWS Systems Manager Application Manager Runbooks to perform tasks on SQL Server instances

In this step, we will use AWS Systems Manager Application Manager, a capability of AWS Systems Manager, to detect, investigate, automate, and remediate issues with the AWS Launch Wizard resources.

With AWS Systems Manager Application Manager, you can discover applications across multiple AWS services like AWS CloudFormation stacks, AWS Resource Groups, Amazon Elastic Kubernetes Service clusters, and AWS Launch Wizard. After discovering, users can view operational and compliance data like alarms, operational issues, and logs for the application in a single dashboard.

The AWS Systems Manager Application Manager includes a predefined AWS Systems Manager Automation documents(runbooks) for remediating common issues with AWS resources. A Systems Manager Automation document defines actions that AWS Systems Manager can perform on your managed instances and other AWS resources when an automation workflow runs. And a document contains one or more steps that run in sequential order.

In this example, we will show how to back-up a specified database using the AWSSQLServer-Backup document.

From the AWS Management Console, navigate to AWS Systems Manager > Application Manager > Launch Wizard applications.

Next, select the application that was deployed to view operational and compliance data such as alarms, operational issues, and logs for the application.

Next, select Start runbook, to choose the specific runbook document to execute against the SQL Server instances that were launched from the previous steps.

Next, fill in the details for the Database, BackupType and other detail. Select Execute to perform a full, differential, or transactional backup of a database.

In addition to this, AWS Launch Wizard provides the following predefined AWS Launch Wizard Systems Manager Automation documents to perform backups, index maintenance, integrity checks and restore backups for SQL Server.

  1. Use AWSSQLServer-DBCC automation document to create a runbook to perform database integrity checks on a specified database and also refer to this blog Introducing the AWSSQLServer-DBCC Automation document for SQL Server maintenance for more details.
  2. Use AWSSQLServer-Backup automation document to create a runbook to perform a full, differential, or transactional backup of a database and also refer to blog Introducing the AWSSQLServer-Backup Automation document for SQL Server maintenance for more details.
  3. Use AWSSQLServer-Index automation document to perform index maintenance operations on a specified database and also refer to blog Introducing the AWSSQLServer-Index Automation document for SQL Server maintenance for more details.
  4. Use AWSSQLServer-Restore automation document to perform restore operations and also refer to this blog Introducing the AWSSQLServer-Restore Automation document for SQL Server maintenance for more details.

Repeat the steps, as required to perform various tasks on SQL Server instances.

Step 2: Use AWS Systems Manager Automation to perform configuration management of the SQL Server instances

You can use the Automation feature of the AWS Systems Manager to perform various operational tasks on the application resources using pre-defined documents. With AWS Systems Manager Automation, you can configure and manage Amazon EC2 Instances and other AWS resources by using pre-defined runbooks or custom runbooks tailored for your need. In addition, you can also choose to receive notifications on the status of automation tasks to monitor the progress.

In this example, we will show how to re-size the SQL Server Instances using the AWS-ResizeInstance document.

Open the AWS Systems Manager console

In the navigation pane, choose Automation, and then choose Execute automation.

In the Automation document list, choose AWS-ResizeInstance document.

Next, select the SQL Server Instance to resize the instance types to r5b.4xlarge and choose Execute.

Step 3: Use AWS Systems Manager Run Command to issue SQL Commands to the SQL Server

In this example, I’ll show how you can use the PowerShell Module and issue SQL commands using AWS Systems Manager Run Command feature.

From the AWS Systems Manager console, select Run Command, and select the AWS-RunpowerShellScript document.

Next, use the following command, ‘Invoke-SqlCmd -query “select @@version” -ServerInstance “localhost”’ to check the MS SQL version that was deployed from the previous steps by selecting the Run command as shown in the following screenshots.

Step 4: Use AWS Systems Manager Application Manager and AWS Systems Manager Patch Manager to patch SQL Server instances

In this example, I’ll show how to apply patches on-demand to the SQL Server instances launched by AWS Launch Wizard.

From the AWS Systems Manager console, navigate to Application Manager > Launch Wizard applications and select the application that was deployed from the previous steps.

Next, select the Patch button. This will take you the Patch instance now page.

Select scan and install for patching operation,

Select Reboot if needed for reboot operation and

Select Patch only the target Instance I specify to patch a specific instance.

Now, let’s select the passive SQL Node and choose Patch now to run an on-demand patching.

When the Patch now operation runs, it uses the patch baseline that is currently set as the default for the operating system type of your instances. This can be a predefined baseline, or the custom baseline you have set as the default.

Note: The Patch now option lets you run an on-demand patching operation from the console. This bypasses the requirement of creating a schedule to update the compliance status of your instances or install patches on non-compliant instances.

Once you have verified that the patching completed successfully on the passive node, failover the cluster resource to fully patched SQL Node and repeat the same steps for patching another SQL node.

In addition to this, AWS Systems Manager Patch Manager provides the following SSM documents to manage your instance patching operations.

  1. Use AWS-ConfigureWindowsUpdateto configure basic Windows update functions and use them to install updates automatically.
  2. Use AWS-InstallWindowsUpdatesto install a specific update or to install patches with specific classifications but don’t need compliance information.
  3. Use AWS-RunPatchBaselineto install patches on your instances or scan instances to determine whether any qualified patches are missing.
  4. Use AWS-RunPatchBaselineAssociationto install patches on your instances or scan instances to determine whether any qualified patches are missing.
  5. Use AWS-RunPatchBaselineWithHooksto install patches on your instances or scan instances to determine whether any qualified patches are missing.
  6. Also, with optional hooks, you can run SSM documents such as before ‘Install with NoReboot’, ‘Install with NoReboot’ and ‘after the reboot’ of the instance.

To learn more about Patching instances on demand, refer to Patching instances on demand for more details.

And finally, if you want to schedule patching, Choose ‘Configure patching’ to schedule it using Maintenance Windows, and refer to this blog post Introducing Maintenance Windows for scheduling Automation documents against your SQL Server instances for additional details.

Cleanup

To avoid ongoing charges, delete the resources you created.

Use the AWS Launch Wizard console to delete the SQL Server deployment. Choose Deployments > MS SQL Server, and delete the sqlha application by selecting the application and choose Delete as shown in the following screenshots.

Summary

In this blog, we demonstrated, how you can use AWS Systems Manager Application Manager to simplify, manage and automate day to day operations of AWS Launch Wizard application resources i.e SQL Server instances that comply with AWS best practices.

Additional resources

AWS Launch Wizard

Accelerate SQL Server Always On Deployments with AWS Launch Wizard

About SSM documents for patching instances – AWS Systems Manager

Amazon EBS volume types – Amazon Elastic Compute Cloud

Instantly monitor serverless applications with AWS Resource Groups

Storage for I/O-intensive SQL Server using Amazon EBS io2 Block Express

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.

Praveen Panati

Praveen Panati

Praveen Panati is a Senior Solutions Architect at Amazon Web Services. He is passionate about cloud computing and works with AWS enterprise customers to architect, build, and scale cloud-based applications to achieve their business goals. Praveen’s area of expertise includes cloud computing, big data, streaming analytics, and software engineering.

Sudhir Amin

Sudhir Amin

Sudhir Amin is a Sr. 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.