Azure SQL Elastic pool Monitoring possible?

[sgomeztd]

Hi,

I'm trying to use the SQL database Wizard to monitor some DDBB we have on Azure but after some analysis I think is just not possible with the current check_mssql_server.php as is not prepared for it.

I can successfully connect to it and run time2connect commands but all the other modes fail to execute because internally it appends the instance name to the SQL query but Azure uses GUID on the on the sys.dm_os_performance_counters instead of the table

This is the command I tested ( changing preftype do not affect the result)

Code: Select all

/check_mssql_server.php -H mysqlserver.database.windows.net --checktype 'database' -U 'xxxxxx -P 'xxxxxxx' --instancename 'my_database1' --perftype azuresqldb -p 1433 --mode datasize --warning 10000 --critical 100000 -vvv
..
..
..
INFO: [MSSQLQuery::run_on_connection] query [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='Data File(s) Size (KB)' [color=#FF0000]AND instance_name='my_database1'[/color];] (2173)

I have truncated some of the output but that part in red in the important one. Maybe I have missed it but I think that this check will not work unless is updated to first get the GUID for the instancename from sys.databases. If that SQL query is run with the GUID instead of the instance name it does work OK.

Am I missing something?

[jmichaelson]

Are you saying that Azure uses a GUID instead of "sys.dm_os_performance_counters" or a GUID instead of something else?

[sgomeztd]

Hi,

It uses GUID instead of instance_name on the tables to identify the ddbb. Here is the query and the result that shows what I mean.

Code: Select all

SELECT *  FROM sys.dm_os_performance_counters WHERE counter_name='Data File(s) Size (KB)' and instance_name='e68fcd2d-fb66-4420-99a5-9ee38e794e62'

RESULT
object_name,counter_name,instance_name,cntr_value,cntr_type
"MSSQL$B5BEF46AEE6F:Databases ","Data File(s) Size (KB)","e68fcd2d-fb66-4420-99a5-9ee38e794e62",32768,65792

To get the GUID

SELECT name, physical_database_name
FROM sys.databases;

name,physical_database_name
"master",e68fcd2d-fb66-4420-99a5-9ee38e794e62
"my_database1",e68fcd2d-fb66-4420-99a5-9ee38e794e62

If the check code is updated to fist get the GUID using the instanace name from sys.databases and use that GUID as instance_name on the rest of the queries it should work but I have not tested it.

I guess these SQL Elastic pool work a bit different than the traditional MSSQL Servers

[jmichaelson]

Thanks for that! I'll open an internal issue for an enhancement.