Tutorial: Set up monitors for .NET applications using SQL Server - Amazon CloudWatch
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Tutorial: Set up monitors for .NET applications using SQL Server

This tutorial demonstrates how to configure CloudWatch Application Insights to monitor an example solution and then simulate problem scenarios to test the solution. In this example, a load balanced web application using SQL Server on the backend is deployed. The web application and SQL Server are hosted on separate EC2 instances.

Use case scenario

In this scenario, a .NET application using SQL Server on the backend runs on an Amazon EC2 instance. The deployment is configured with two load-balanced EC2 instances hosting the Barley Adventure Works application. Both instances access SQL Server, which is hosted on a separate EC2 instance. Monitors are set up with CloudWatch Application Insights to quickly identify, isolate, and resolve application issues.


                    Solution component diagram

Prerequisites

To complete the steps in this tutorial, you must have an Amazon account.

If you do not have an Amazon Web Services account, use the following procedure to create one.

To sign up for Amazon Web Services

  1. Open http://www.amazonaws.cn/ and choose Sign Up.

  2. Follow the on-screen instructions.

Deploy resources for example scenario

A CloudFormation template is provided to automate the deployment scenario for testing. The template deploys the following instances:

  • An EC2 instance that hosts the Microsoft SQL Server database.

  • Two load-balanced EC2 instances. Each load-balanced instance hosts the Barley Adventure Works web application.

The template deployment takes approximately 10 minutes to complete.

Steps to deploy the CloudFormation stack

  1. Choose Create Stack>With new resources (standard) from the Amazon CloudFormation landing page at https://console.amazonaws.cn/cloudformation to launch a CloudFormation stack in your account in the US East (N. Virginia) Region. This template is available only in the us-east-1 Region.

  2. Select Template is ready on the Create Stack page.

  3. Under Specify template, select Amazon S3 URL and enter the following S3 URL path: https://application-insights-demo-resources.s3.amazonaws.com.cn/SampleApp.yml. Choose Next.

    Note

    This CloudFormation template can also be found in the aws-samples GitHub repo at the following location: https://github.com/aws-samples/application-insights-sample-application/blob/master/SampleApp.yml.

  4. On the Specify stack details page, enter a name for the stack, such as ApplicationInsightsTest.

  5. Review the default parameters under Parameters and modify the values to your preferences. Enter a password for SQLServer. Enter an existing EC2 key pair or create a new one in the EC2 console. Choose Next.

  6. On the Configure stack options page, under Tags, optionally add tags to help you identify your stack. Select Next.

  7. Review and confirm the settings on the Review page. Select the box acknowledging that the template may create Amazon Identity and Access Management (IAM) resources.

  8. Choose Create stack to deploy the stack.

  9. Monitor the status of the stack deployment from the Events tab of the Cloud Formation stack page. When the stack is successfully deployed, continue to the next section.

Set up monitoring with Amazon CloudWatch Application Insights

This section demonstrates how to create a resource group from the resources deployed by the CloudFormation template, and how to add the resource group to CloudWatch Application Insights for monitoring.

Create resource group

  1. Navigate to the Amazon Resource Groups console and choose Create resource group.

  2. On the Create query-based group page, under Group type, select CloudFormation stack based.

  3. Under Grouping criteria, select the CloudFormation stack you created in the previous section (ApplicationInsightsTest) from the dropdown list. Keep resource types as All supported resource types.

  4. Under Group details, enter a Group name, such as application-insights-resource-group, and an optional description of the resource group. Then, choose Create group.

Set up resource group monitoring on CloudWatch Application Insights

  1. Navigate to the Amazon CloudWatch console and choose Application Insights under Insights on the left navigation pane.

  2. Choose View applications next to Application Insights.

  3. On the Applications monitored page, choose Add an application.

  4. Under Resource Group selection, select the resource group you created in the previous procedure (application-insights-resource-group) and choose Add application.

  5. On the Overview page, refresh your browser to display the application components in your resource group.

  6. Select the Application Load Balancer group and choose Manage monitoring.

  7. On the Manage monitoring page, select Enable monitoring.

  8. Choose Save.

  9. To enable monitoring for the SQL Server instance, select the SQL Server EC2 instance in the Application components section and repeat the previous steps for enabling monitoring from the Manage monitoring page.

    The following metrics are monitored for the SQL Server instance:

    • CPUUtilization

    • StatusCheckFailed

    • Memory % Committed Bytes in Use

    • Memory Available Mbytes

    • Network Interface Bytes Total/sec

    • Paging File % Usage

    • Physical Disk % Disk Time

    • Processor % Processor Time

    • SQLServer:Buffer Manager cache hit ratio

    • SQLServer:Buffer Manager life expectancy

    • SQLServer:General Statistics Processes blocked

    • SQLServer:General Statistics User Connections

    • SQLServer:Locks Number of Deadlocks/sec

    • SQLServer:SQL Statistics Batch Requests/sec

    • System Processor Queue Length

    The following metrics are monitored for the volumes attached to the SQL Server instance:

    • VolumeReadBytes

    • VolumeWriteBytes

    • VolumeReadOps

    • VolumeWriteOps

    • VolumeTotalReadTime

    • VolumeTotalWriteTime

    • VolumeIdleTime

    • VolumeQueueLength

    • VolumeThroughputPercentage

    • VolumeConsumedReadWriteOps

    • BurstBalance

  10. When monitoring is enabled for both the load balancer and the SQL Server instance, the resource group to which they belong displays a status of Enabled on the resource group Overview page.

Simulate problem scenarios and view insights

This section describes how to create a SQL login failure, a SQL memory pressure event, and an HTTP 500 error so that you can view the error details on the CloudWatch Application Insights dashboard.

Simulate SQL login failure

To simulate a SQL login failure and view the problem from the CloudWatch dashboard, perform the following steps.

  1. Log in to the EC2 instance provisioned for your SQL Server instance (M4 instance type) using the key pair you selected when you created the CloudFormation stack.

  2. From the Start menu, launch SQL Management Studio.

  3. Enter a username and an incorrect password and choose Connect. A message appears indicating that the login has failed. Repeat this step a few more times.

  4. From the CloudWatch Application Insights Problems detected page (at the bottom of the CloudWatch console landing page), the error should appear under the problem summary as SQL: Login Failure. To see more details about the problem, select the problem link.

Simulate high memory pressure

To simulate a high memory pressure event, which can lead to application performance degradation and timeout errors on the web servers and load balancers, perform the following steps.

  1. Launch SQL Management Studio from the SQL server instance and login using the Windows Administrator account.

  2. Right-click on the database server, choose Properties, and select Memory.

  3. Under the Server memory options, reduce the Maximum server memory to 256 KB.

  4. In a new query window, run the following SQL query:

    SELECT COUNT(*) FROM [AdventureWorks2016].[Sales].[Customer]

    A message appears indicating that there is insufficient memory to run the query.

  5. From the CloudWatch Application Insights Problems detected page (at the bottom of the CloudWatch console landing page), the error should appear under the problem summary as SQL: Memory Pressure. To see more details about the problem, select the problem link.

Simulate an HTTP 500 error

HTTP requests for an unhandled HTTP request to a web application results in an HTTP 500 error. To simulate an HTTP 500 error, perform the following steps.

  1. From the Amazon Web Services Management Console, navigate to the Amazon CloudFormation console.

  2. Choose the Outputs tab of the AppInsightsTest CloudFormation stack that you previously launched.

  3. Open the URL displayed under Value for the AdventureWorks application in a web browser.

  4. Navigate to the customer details page of the Barley Adventure Works web application by suffixing the previously mentioned URL with barleyapp/SalesOrderDetails/edit/5.

  5. Refresh the compiled URL request several times. Your URL should look something like this: http://<YourURL>.us-east-1.elb.amazonaws.com.cn/barleyapp/SalesOrderDetails/edit/5.

    An error message appears indicating that the file or directory cannot be found.

  6. From the CloudWatch Application Insights Problems detected page (at the bottom of the CloudWatch console landing page), the error should appear under the problem summary as ALB: Backend 5XX errors. To see more details about the problem, select the problem link.