Q: I can’t figure out how to monitor non-default instance of MS SQL server. Can you help?
A: We recommend using Microsoft SQL Database Server application template as a base and then accommodate its monitors’ settings to use your non-default MSSQL instance.
Follow the steps described below to achieve it:
1. Click 
button to start New Monitor Wizard.
2. In Application Templates group click Database Servers and then select Microsoft SQL Database Server:
Click Next button.
3. Fill in the field “Select host for new monitor(s)” on the next Wizard’s page and press Next.
4. Select the list of monitors you wish to use
and press Finish.
By default the created monitors contain settings for default MSSQL instance. To monitor non-default one you need to make the following changes:
- Specify Instance Name instead of default one
- Slightly modify some WMI queries since name of MSSQL instance affects WMI class name
- Slightly modify expressions that use system tables since their records are affected by name of MSSQL instance
Let’s assume that we need to monitor MS SQL instance named SANDBOX using monitors DB sanity check: record count query, Filestore, Kb and User connections.
5. DB sanity check: record count query monitor requires the following changes:
- change SQL Expression to use the existing table name you wish to use for monitoring
- change Database name to the existing database name you wish to use
- click Instance Name and specify it
- fill in Authentication block in accordance with credentials for your MSSQL instance
6. By default the setting Custom WMI Query of Filestore, Kb monitor contains the text as follows:
SELECT DataFilesSizeKB FROM Win32_PerfRawData_MSSQLSERVER_SQLServerDatabases WHERE Name='_Total'
For non-default instance use the text template as follows:
SELECT DataFilesSizeKB FROM Win32_PerfRawData_MSSQL<InstanceName>_MSSQL<InstanceName>Databases WHERE Name='_Total'
i.e. for the instance SANDBOX this setting should be as follows:
SELECT DataFilesSizeKB FROM Win32_PerfRawData_MSSQLSANDBOX_MSSQLSANDBOXDatabases WHERE Name='_Total'
The similar changes should be done for other WMI queries that use WMI classes with names starting with Win32_PerfRawData_MSSQL.
7. By default field SQL Expression of User connections monitor contains the text as follows:
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'User Connections'
For non-default instance use the text template as follows:
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$<InstanceName>:General Statistics' AND counter_name = 'User Connections'
i.e. for the instance SANDBOX this setting should be as follows:
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$SANDBOX:General Statistics' AND counter_name = 'User Connections'
Select Instance Name instead of TCP Port and specify your MSSQL instance name.
Fill in Authentication block in accordance with credentials for your MSSQL instance.
The similar changes should be done for other SQL expressions that use sys.dm_os_performance_counters system table.
Please contact technical support by e-mail in case you still have problems configuring the MSSQL monitor.
