By: Sadequl Hussain | Comments | Related: 1 | 2 | 3 | > Amazon AWS
Problem
In the first part of this series, we talked about AWS CloudWatch and its potential to become an integrated monitoring solution for SQL Server. In this part, we will build our infrastructure and configure CloudWatch.
Solution
To start with, we will assume we have a SQL Server 2014 instance running on a Windows Server 2012 EC2 machine. In our tests, we created the instance from one of the AWS provided AMIs (Amazon Machine Image). It's running in a non-default VPC (Virtual Private Cloud) network and its Security Group allows it to receive SQL traffic and RDP connections. We will also assume the EC2 instance is not running with an IAM role. If you are not familiar with EC2s, VPCs, Security Groups, AMIs or IAM, please refer to AWS documentation. It's fairly extensive.
AWS IAM Users and Roles
Typically, you would have existing SQL Server instances you would like to start monitoring. AWS services follow a very strict security model. Each service is stand-alone and needs explicit permission to access other services. What this means is, our SQL Server EC2 instance will need permissions to send its Windows performance counters to CloudWatch.
There are two ways to do this. The ideal and AWS recommended way is to assign an IAM role to the EC2 instance when it's first created. IAM is AWS' user and access management service. With IAM, it's possible to grant multiple users access to resources from the same AWS account. This is made possible by creating individual IAM users. The users can be grouped under IAM groups. Permissions can be set at individual user or group level. For example, an IAM group called "Developers" can contain a number of IAM user accounts. The group permission will apply to each of those accounts. The permissions assigned to the Developers group may be less than the permissions assigned to the "Administrators" group.
IAM users can identify themselves with either a username and password combination or with a set of API keys. The first option can be used to grant access to the AWS console only. With API keys, IAM users can access AWS resources programmatically. The API keys are a combination of an Access Key ID and a Secret Access Key. Both the credentials usually consist of a long series of alphanumeric characters.
IAM has a separate permission model for servers (EC2 instances). EC2 instances can run under an IAM Role and assume that role's permissions. A role is nothing but a collection of access rights and permissions. When an IAM role is assigned to an EC2 instance, applications running in that instance will assume the permissions attached to that role. There are a number of pre-built roles already available with AWS. You can also create your own custom roles.
Unfortunately roles can be assigned to an EC2 instance only when it's being launched, not after that. That means once the EC2 instance is running, there's no way but to terminate and re-launch it to get the role assigned. For most SQL Server instances that's not an option. However, if you already launched your SQL Server EC2 instance with an IAM role, all you need is to ensure the role's permissions allow accessing CloudWatch and sending performance counters to it.
Assuming your SQL Server EC2 instance doesn't have an IAM role assigned to it, the only option without re-launching it would be creating an IAM user with appropriate permissions and using that user's credentials in the application configuration file. This is not recommended because:
- The credentials are exposed as plain text in the configuration file, which means anyone who has access to the machine can access those credentials. This is clearly a security risk.
- If you have to terminate and recreate the instance, you have to reconfigure the app's configuration with the credentials.
In our test case, we have tried to minimize the risk. We have created an IAM user with only minimal permissions to write to CloudWatch.
And this is how we are doing it. As the following image shows, we are creating an IAM user called EC2ConfigUser. We have checked the option "Generate an access key for each user". This will automatically create a pair of keys for the user (Access Key ID and Secret Access Key).
Once the user is created, the credentials will be displayed in the next screen.
We will need to save the security credentials somewhere safe. That's because this is the only time the Secret Access Key will be displayed for us. We can find the Access Key ID from the user's properties any time, but not its Secret Access key. We will use these credentials in a configuration file later.
Once the IAM user is created, we will have to attach a set of permissions for the user. To do that, first, we created a new IAM policy with only minimal permissions to write performance counter values to CloudWatch. We have named this policy "EC2CloudWatchMetric".
Finally, the policy is attached to the IAM user account.
Configuring the EC2Config Service
AWS Windows AMIs come with a custom Windows service called EC2Config. This service runs in each Windows EC2 instance created from the AMIs and starts when the instance is booted. EC2Config has multiple purposes which include:
- Controlling tasks running at instance start-up or shutdown
- Generating and installing host certificates for RDP connections to the server
- Executing user-data scripts (user data is a set of commands the instance runs when it's first initialized)
- Mounting all attached EBS volumes and instances store volumes and assigning drive letters to those volumes
- Checking Windows activation status
- Displaying system information in the desktop wallpaper
- Sending Windows event logs, IIS logs or custom application logs to CloudWatch
- Sending Windows performance counters to CloudWatch
EC2Config uses a number of configuration files to control these activities. We are interested about the task that sends local Windows performance counters to CloudWatch. This is task is controlled by a file named AWS.EC2.Windows.CloudWatch.json. By default, the file is located under C:\Program Files\Amazon\Ec2ConfigService\Settings directory.
From the file extension, we can see it's a JSON file. We will talk about it in a minute, but before that, let's start another application.
From the Windows Server desktop, let's search for "EC2Config". An application icon will be displayed as the application is found. We can start it as an Administrator.
The EC2ConfigService Settings is basically a dialog box. The options chosen here will affect how the EC2Config service will run after it's restarted. If you look closely under each tab, you will see how some of the server's runtime behavior is controlled by EC2Config. The option we are interested about is in the General tab: it's the checkbox "Enable CloudWatch Logs integration". By default it's not checked on. We need to ensure the checkbox is selected and the dialog box is closed by clicking Apply and then OK.
Ideally you would think about restarting the service at this point, but we will do this after we have modified the JSON file.
Understanding the AWS.EC2.Windows.CloudWatch.json File
Before modifying the file, let's make a copy of it for backup purposes and then open the file in a text editor. You can download the file and open in your favorite editor or make the changes while logged into the server. First, let's have a look at it and understand its different sections. In the code block below, we are showing only certain sections of the file.
{
"EngineConfiguration": {
"PollInterval": "00:00:15",
"Components": [
{
"Id": "ApplicationEventLog",
"FullName": "AWS.EC2.Windows.CloudWatch.EventLog.EventLogInputComponent,AWS.EC2.Windows.CloudWatch",
"Parameters": {
"LogName": "Application",
"Levels": "1"
}
},
{
"Id": "SystemEventLog",
"FullName": "AWS.EC2.Windows.CloudWatch.EventLog.EventLogInputComponent,AWS.EC2.Windows.CloudWatch",
"Parameters": {
"LogName": "System",
"Levels": "7"
}
},
{
"Id": "SecurityEventLog",
"FullName": "AWS.EC2.Windows.CloudWatch.EventLog.EventLogInputComponent,AWS.EC2.Windows.CloudWatch",
"Parameters": {
"LogName": "Security",
"Levels": "7"
}
},
...
...
{
"Id": "PerformanceCounter",
"FullName": "AWS.EC2.Windows.CloudWatch.PerformanceCounterComponent.PerformanceCounterInputComponent,AWS.EC2.Windows.CloudWatch",
"Parameters": {
"CategoryName": "Memory",
"CounterName": "Available MBytes",
"InstanceName": "",
"MetricName": "Memory",
"Unit": "Megabytes",
"DimensionName": "",
"DimensionValue": ""
}
},
{
"Id": "CloudWatchLogs",
"FullName": "AWS.EC2.Windows.CloudWatch.CloudWatchLogsOutput,AWS.EC2.Windows.CloudWatch",
"Parameters": {
"AccessKey": "",
"SecretKey": "",
"Region": "us-east-1",
"LogGroup": "Default-Log-Group",
"LogStream": "{instance_id}"
}
},
{
"Id": "CloudWatch",
"FullName": "AWS.EC2.Windows.CloudWatch.CloudWatch.CloudWatchOutputComponent,AWS.EC2.Windows.CloudWatch",
"Parameters":
{
"AccessKey": "",
"SecretKey": "",
"Region": "us-east-1",
"NameSpace": "Windows/Default"
}
}
],
"Flows": {
"Flows":
[
"(ApplicationEventLog,SystemEventLog),CloudWatchLogs"
]
}
}
}
If you are not familiar with JSON, it's a simple way of managing data through key value pairs, separated by colons. Functionally, it's similar to what XML does in representing data. Usually each section in a JSON document is separated by curly brackets. A key can have a simple value, or it can have a nested section with its own key-value pairs. A key can also have an array of values, each of those values can be simple or nested key value pairs. NoSQL database systems like MongoDB use JSON documents as its data.
This particular file consists of different sections called components. It's basically a set of configuration instructions for EC2Config service to send Windows logs and performance counters to AWS CloudWatch. You can see the polling interval for the data to be published is 15 seconds.
Every section has a unique ID. This can be anything you choose, but has to be unique within the file. The components that make up this file have different parameters as keys. For example, we can see the first three components represent Windows Application, System and Security Logs. Within Security Log component, only certain events above a level are captured.
There is a component section for the Windows performance counter "Available MBytes". Within the component section, note how the "CounterName" key is exactly the same as the PerfMon counter name. The other important keys here are "CategoryName", "MetricName" and "Unit".
The component with ID "CloudWatchLogs" defines how the machine can access AWS CloudWatch with a pair of IAM credentials. Remember that each AWS service needs explicit permissions to be accessed. Here, "AccessKey" and "SecretKey" represent the API credentials of an IAM user. When specified, the EC2Config service will assume the rights of that IAM user at run time. If the instance was launched with a role, these fields can be left blank. The "Regions" key specifies which region the CloudWatch log data will be sent to. The last two parameters are used to specify the CloudWatch Log Group and Log Stream names. We won't be covering how to stream Windows logs to CloudWatch here; what we are interested about is the next component.
The "CloudWatch" component defines how Windows performance counter values can be sent to AWS CloudWatch. Once again it has two keys for IAM user credentials. Since we have assumed our SQL Server was launched without an IAM role, we have to populate these two fields with the credentials we saved before.
Finally, the "Flows" section defines how the component data should be aggregated. It does this by mapping different component IDs with either the "CloudWatchLogs" or "CloudWatch" ID. In this case, EC2Config is being asked to send Windows Application and System Event Logs to CloudWatch Logs. It's done by listing the IDs of the data sources (within parentheses) followed by the ID of the CloudWatch Logs component. Note how the security log component is not published, even though it was defined.
Conclusion
We have covered some good ground in this part of the series as some new concepts have been introduced. We have already partially configured the EC2Config service; in the next and final installment, we will finish editing the JSON file. From there, we will start building our performance monitor dashboard.
Next Steps
- If you are unfamiliar with JSON formatted data, learn more about it
- Understand the concepts behind IAM users, roles, policies and permissions and how EC2 instances can make use of IAM roles
- Learn more about EC2Config service and its various configuration files
- Read the first part of this series
About the author
Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
