Out of the box, Datadog isn’t able to run custom queries against a local or cloud-hosted SQL database and report metrics. In my case, I needed to check the row count of a table and alert if it ever increased above 0 rows. This was a temporary table that should never get full!

To start, create a SQL stored procedure that will run your custom query and produce Datadog readable metrics. Thanks to the DataDog documentation for this bit…

-- Create a stored procedure with the name GetMetrics
CREATE PROCEDURE [dbo].[GetMetrics]
AS
BEGIN
  -- Create a temporary table per integration instructions
  CREATE TABLE #DataDog
  (
    [metric] varchar(255) NOT NULL,
    [type] varchar(50) NOT NULL,
    [value] float NOT NULL,
    [tags] varchar(255)
  )
  -- Remove row counts from result sets
  SET NOCOUNT ON;
  -- Create variable count and set it equal to the number of current number of items in the table
  DECLARE @count float;
  SET @count = (SELECT COUNT(*) FROM [dbo].[TableName] WITH (NOLOCK);
  -- Insert custom metrics into the table #Datadog
  INSERT INTO #Datadog (metric, type, value, tags)
  VALUES ('table.count', 'gauge', @count, 'tablename')

  SELECT * from #DataDog
END
GO

Since Datadog had no access to the SQL server a new user with appropriate permissions needing creating. The below script works on an Azure Hosted SQL Contained Database.

CREATE USER [DataDogMetricsUser] WITH PASSWORD = 'xxxxxxxxxx'
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
EXEC sp_addrolemember N'db_executor', N'DataDogMetricsUser'
EXEC sp_addrolemember N'db_datareader', N'DataDogMetricsUser'

Finally, I set up the SQL integration in the Datadog agent configuration file.

init_config: null
instances:
  - host: 'xxxxxxx.database.windows.net,1433'
    username: 'DataDogMetricsUser'
    password: 'xxxxxxxx'
    database: 'DatabaseName'
    stored_procedure: GetMetrics
    include_instance_metrics: false