Can CloudWatch Query SQL Servers?

Question

My stack for setting up a SQL-query-based CloudWatch alarm seems ridiculous. I need:

1. A SQL Query returning what I wish to monitor (e.g. SELECT TOP (1) is_server_screwed FROM my_table;).
2. A PowerShell script that sends the result of my query to CloudWatch (e.g. $Metric = boilerplate_here; Write-CWMetricData -Namespace SQL -MetricData $Metric).
3. A SQL Server Agent Job (or equivalent Task Scheduler Task) to fire the script on a schedule.
4. A CloudWatch Alarm to alert me whenever the query's results cross thresholds.

Is there any way to remove steps 2 and 3? I'm surprised that CloudWatch itself cannot query the server.

Answer 1

Can I create a pipeline to get alarms from CloudWatch related to the performance of my Server? Sure.

To expand on Jorge Campos's comment, a solution would be to create a pipeline—a process that effectively bridges this gap (between CloudWatch and SQL Servers).

You could use AWS services (specifically AWS Lambda and Amazon EventBridge) to periodically run a script that queries the SQL Server and pushes the results to CloudWatch Metrics.

For instance, in Python pseudocode:

import pyodbc
import boto3

def lambda_handler(event, context):
    # Connect to SQL Server
    conn = pyodbc.connect('DRIVER={ODBC Driver};SERVER=your_server;DATABASE=your_db;UID=user;PWD=password')
    cursor = conn.cursor()
    
    # Execute SQL Query
    cursor.execute("SELECT TOP (1) is_server_screwed FROM my_table;")
    row = cursor.fetchone()
    
    # Push to CloudWatch
    cloudwatch = boto3.client('cloudwatch')
    cloudwatch.put_metric_data(
        Namespace='SQL',
        MetricData=[
            {
                'MetricName': 'ServerStatus',
                'Value': row[0],
                'Unit': 'Count'
            },
        ]
    )

The Amazon EventBridge (CloudWatch Events) schedules and triggers the Lambda function at regular intervals, making sure that your monitoring pipeline runs automatically without manual intervention.

CloudWatch alarms utilize the custom metrics generated by the Lambda function to configure alarms. These alarms can notify you when performance metrics fall outside predefined thresholds, enabling proactive response to potential issues.

Answer 2

I would personally use zabbix with py-zabbix although you can use EventBridge scheduling to trigger AWS Lambda on a predefined schedule and run SQL query sending results back to CloudWatch - similar to answer provided by VonC.

import boto3
import pyodbc

def lambda_handler(event, context):
    conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_username;PWD=your_password')
    cursor = conn.cursor()

    cursor.execute("SELECT TOP (1) is_server_screwed FROM my_table;")
    row = cursor.fetchone()
    is_screwed = row[0]
    cloudwatch = boto3.client('cloudwatch')
    cloudwatch.put_metric_data(
        Namespace='SQLServer/Health',
        MetricData=[
            {
                'MetricName': 'ServerHealth',
                'Value': is_screwed,
                'Unit': 'None'
            },
        ]
    )