Azure SQL Database Properties Collection - SQL Server Input Plugin

[chuckbarkertech]

Relevant telegraf.conf: inputs.sqlserver

"Server=<hostname>;Port=1433;User Id=<user>;Password=<pw>;app name=telegraf;log=1;"

System info:

Latest master branch as of 12/11/2019 has this bug.
Telegraf 1.12.6
Ubuntu 18.04

Steps to reproduce:

1. Create 2 Azure SQL Databases on the same server.
2. Setup failovergroup for second database
3. Failover to secondary
4. Setup telegraf collection for secondary
5. Telegraf collection no longer works for sqlServerPropertiesV2
   

   telegraf/plugins/inputs/sqlserver/sqlserver.go

   Line 435 in 05cefe6

   JOIN sys.database_service_objectives slo

This is caused by the database_id column in sys.databases becoming out of sync with DB_ID() within Azure SQL.

Expected behavior:

This query from code line referenced above should return the property information for the server.

	SELECT 	TOP(1)
			(SELECT count(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS cpu_count,
			(SELECT process_memory_limit_mb FROM sys.dm_os_job_object) AS server_memory,
			slo.edition as sku,
			cast(SERVERPROPERTY('EngineEdition') as smallint)  AS engine_edition,
			slo.service_objective AS hardware_type,
                        cast(DATABASEPROPERTYEX(DB_NAME(),'MaxSizeInBytes') as bigint)/(1024*1024)  AS total_storage_mb,
			NULL AS available_storage_mb,  -- Can we find out storage?
			NULL as uptime
	FROM	 sys.databases d   
			JOIN sys.database_service_objectives slo    
			ON d.database_id = slo.database_id

Actual behavior:

This query works in most cases until a failover group is used to move databases to another server instance within Azure SQL Database. When a failover group is used, the database_id in master.sys.databases can become out of sync with the database_id used in most of the dynamic management views within the actual Azure SQL DB. This causes the above query to exclude results that should be included.

Additional info:

The below fix will resolve this issue and has been tested heavily.

Adjusting this part of the query:

			JOIN sys.database_service_objectives slo    
			ON d.database_id = slo.database_id

to this:

			-- sys.databases.database_id may not match current DB_ID on Azure SQL DB
			CROSS JOIN sys.database_service_objectives slo
			WHERE d.name = DB_NAME() AND slo.database_id = DB_ID()

results in this query:

	SELECT 	TOP(1)
			(SELECT count(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS cpu_count,
			(SELECT process_memory_limit_mb FROM sys.dm_os_job_object) AS server_memory,
			slo.edition as sku,
			cast(SERVERPROPERTY('EngineEdition') as smallint)  AS engine_edition,
			slo.service_objective AS hardware_type,
                        cast(DATABASEPROPERTYEX(DB_NAME(),'MaxSizeInBytes') as bigint)/(1024*1024)  AS total_storage_mb,
			NULL AS available_storage_mb,  -- Can we find out storage?
			NULL as uptime
	FROM	 sys.databases d   
			-- sys.databases.database_id may not match current DB_ID on Azure SQL DB
			CROSS JOIN sys.database_service_objectives slo
			WHERE d.name = DB_NAME() AND slo.database_id = DB_ID()

Let me know if there are questions or if it is Ok to submit a PR with this fix

[danielnelson]

Yes, please open a pull request.

[chuckbarkertech]

The pull request is #6794
This is my first open source pull request so please forgive my ignorance. It appears to me that the unit tests are missing for the SQL Server properties query. Do I need to add those for this?

[chuckbarkertech]

This query is actually covered through the gather command in the TestSqlServer_MultipleInstance test.