02 - Datadog Agent: SQL VM

6 min read

Datadog Agent: SQL VM

Quote

“Without monitoring, you are just guessing. With monitoring, you are making informed decisions.”

— Tom Wilkie, co-creator of Grafana Loki

Automated Setup (via Startup Script)

The startup script (infra/scripts/sql-startup.sh) installs the agent on first boot:

Install the Datadog Agent 7

DD_API_KEY="${DD_API_KEY}" DD_SITE="datadoghq.eu" \
  bash -c "$(curl -fsSL https://s3.amazonaws.com/dd-agent/scripts/install_script_agent7.sh)"

Write the main agent config

# /etc/datadog-agent/datadog.yaml
api_key: <API_KEY>
site: datadoghq.eu
hostname: data-pipeline-sql
tags:
  - env:prod
  - service:data-pipeline-sql
logs_enabled: true
process_config:
  process_collection:
    enabled: true

Create the dd_agent SQL Server login with read-only permissions

CREATE LOGIN dd_agent WITH PASSWORD = 'Dd@g3nt!Monitor';
GRANT VIEW SERVER STATE TO dd_agent;
GRANT VIEW ANY DEFINITION TO dd_agent;

Minimal Permissions

VIEW SERVER STATE grants access to DMVs like sys.dm_exec_sessions, sys.dm_os_performance_counters, and sys.dm_os_wait_stats. VIEW ANY DEFINITION allows reading object metadata. No write permissions are granted.

Write the SQL Server integration config to

/etc/datadog-agent/conf.d/sqlserver.d/conf.yaml — see datadog-sql-server-integration for the full config.

Datadog Agent Manual Install on SQL VM

If the Datadog Agent was not installed during VM bootstrap (e.g., dd-api-key metadata was not set at first boot), install it manually:

# 1. SSH into the SQL VM
gcloud compute ssh data-pipeline-sql --zone=europe-west1-b --tunnel-through-iap
 
# 2. Set your Datadog API key
export DD_API_KEY="<your-datadog-api-key>"
 
# 3. Install the agent
DD_API_KEY="$DD_API_KEY" DD_SITE="datadoghq.eu" \
  bash -c "$(curl -fsSL https://s3.amazonaws.com/dd-agent/scripts/install_script_agent7.sh)"
 
# 4. Write agent config
sudo tee /etc/datadog-agent/datadog.yaml <<EOF
api_key: ${DD_API_KEY}
site: datadoghq.eu
hostname: data-pipeline-sql
tags:
  - env:prod
  - service:data-pipeline-sql
logs_enabled: true
process_config:
  process_collection:
    enabled: true
EOF
 
# 5. Create dd_agent SQL login
SA_PWD=$(curl -s -H "Metadata-Flavor: Google" \
  "http://metadata.google.internal/computeMetadata/v1/instance/attributes/sa-password")
 
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$SA_PWD" -C -Q "
  IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'dd_agent')
  BEGIN
    CREATE LOGIN dd_agent WITH PASSWORD = 'Dd@g3nt!Monitor';
    GRANT VIEW SERVER STATE TO dd_agent;
    GRANT VIEW ANY DEFINITION TO dd_agent;
  END
"
 
# 6. Configure SQL Server integration
sudo mkdir -p /etc/datadog-agent/conf.d/sqlserver.d
sudo tee /etc/datadog-agent/conf.d/sqlserver.d/conf.yaml <<EOF
init_config:
 
instances:
  - host: localhost,1433
    username: dd_agent
    password: 'Dd@g3nt!Monitor'
    connector: odbc
    driver: '{ODBC Driver 18 for SQL Server}'
    connection_string: 'TrustServerCertificate=yes'
    tags:
      - env:prod
      - service:data-pipeline-sql
EOF
 
# 7. Fix permissions and start
sudo usermod -aG root dd-agent
sudo systemctl enable datadog-agent
sudo systemctl restart datadog-agent
 
# 8. Verify
sudo datadog-agent status

Persist API Key in Metadata

After manual install, run terraform apply to persist dd-api-key in the VM metadata for future reboots. Without this, the key won’t be available on next boot and the agent won’t auto-configure.

Datadog Agent Config File Locations on SQL VM

FilePurpose
/etc/datadog-agent/datadog.yamlMain agent config (API key, hostname, tags)
/etc/datadog-agent/conf.d/sqlserver.d/conf.yamlSQL Server integration (connection, custom queries)
/etc/datadog-agent/conf.d/sqlserver.d/logs.yamlSQL Server log collection

Datadog Agent Management Commands on SQL VM

SSH into the VM first:

gcloud compute ssh data-pipeline-sql --zone=europe-west1-b --tunnel-through-iap
TaskCommand
Full agent statussudo datadog-agent status
Check SQL Server integrationsudo datadog-agent check sqlserver
View agent logssudo journalctl -u datadog-agent --no-pager -n 50
Restart agentsudo systemctl restart datadog-agent
Stop agentsudo systemctl stop datadog-agent
Start agentsudo systemctl start datadog-agent

Graph View

Backlinks