Windows_exporter: Feature: SQL Server

[martinlindhe]

Does SQL server expose WMI metrics?
If so, it would be very nice to also support that in this project.

[martinlindhe]

There are WMI provider for SQL Server management: https://technet.microsoft.com/en-us/library/ms186452%28v=sql.90%29.aspx

And there are WMI provider for SQL Server Events: https://technet.microsoft.com/en-us/library/ms186449%28v=sql.90%29.aspx

[brian-brazil]

If you're going down the route of being a WMI exporter rather than a Windows machine metrics exporter, then this should work similarly to the JMX exporter. That is there would be a config file per service to map WMI to Prometheus metrics.

[martinlindhe]

Okay. Perhaps a separate project for sql server then. Will consider this later on.

[martinlindhe]

However, i think it is a common use case to want to monitor a windows box that is running both IIS and sql server, without the need/hazzle of running multiple exporters.

[brian-brazil]

The Prometheus architecture is that you'd run an exporter per IIS and per SQL server.

[martinlindhe]

This is related to #12

[martinlindhe]

Some code (MIT licensed) that this could be based on exists here: https://github.com/bosun-monitor/bosun/blob/master/cmd/scollector/collectors/sqlagent_windows.go

[martinlindhe]

Possibly overlapping with this project: https://github.com/iamseth/azure_sql_exporter

[carlpett]

Partial overlap, yes, but that one does not support on-prem SQL server. We have probably a dozen or so instances locally that I'd like to get some metrics from.

[martinlindhe]

Just added it for reference. I also want to proceed adding "normal" sql server support to wmi_exporter :-)

/m

[martinlindhe]

I'm planning to have a look at sql server support this week

[martinlindhe]

Failed to make any progress last week. Not sure i'll be able to work on this current week.

[martinlindhe]

More notes:

get all sql server instances

New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer | ForEach-Object {$_.ServerInstances}

query a instance to find databases ("MSSQLSERVER" is the default instance name):

Get-WmiObject -NameSpace root\Microsoft\SQLServer\ServerEvents\MSSQLSERVER -list
(i get "Access denied")

get errors

Get-WmiObject -NameSpace root\Microsoft\SQLServer\ComputerManagement13 -query "SELECT * FROM SqlErrorLogEvent";
(i get "UnauthorizedAccessException")

find available classes

Get-WmiObject -NameSpace root\Microsoft\SQLServer\ComputerManagement13 -list

NOTE: namespace is not constant. For SQL Server 2005 it's "ComputerManagement", for SQL Server 2008 it's "ComputerManagement10" and for SQL Server 2016 it's "ComputerManagement13"

getting some metrics

assuming instance name MSSQLSERVER:

Get-WmiObject -query "SELECT * FROM Win32_PerfRawData_MSSQLSERVER_SQLServerSQLStatistics";

about sql server express

sql server express may not be detected as a normal sql server. ignoring this for the moment

[carlpett]

Looks like there are quite a few different wmi classes in play here... Here's an (slightly obfuscated) exerpt from one of our machines:

> Get-WmiObject -query "SELECT * FROM meta_class WHERE __this ISA 'Win32_PerfRawData'" | 
    ? Name -like '*MSSQL*' | select -ExpandProperty name
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>AccessMethods
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>AvailabilityReplica
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BackupDevice
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BatchRespStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BrokerActivation
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BrokerDBMTransport
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BrokerStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BrokerTOStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BufferManager
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>BufferNode
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CatalogMetadata
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CloudDBMessaging_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CloudDBReplication_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CLR
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CursorManagerbyType
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>CursorManagerTotal
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>DatabaseMirroring
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>DatabaseReplica
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>Databases
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>DeprecatedFeatures
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ExecStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>FileTable
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>GeneralStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>HTTPStorage
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>Latches
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>Locks
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>MemoryBrokerClerks
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>MemoryManager
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>MemoryNode
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>PlanCache
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ReplicationAgents
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ReplicationDist
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ReplicationLogreader
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ReplicationMerge
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ReplicationSnapshot
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ResourcePoolStats
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>SQLErrors
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>SQLStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>TCMAgents_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ThrottlHardPartition_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>ThrottlSoftPartition_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>TraceEventStatistics_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>TraceStatistics_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>TransactionManager_Costly
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>Transactions
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>UserSettable
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>WaitStatistics
Win32_PerfRawData_MSSQL<INSTANCE>_MSSQL<INSTANCE>WorkloadGroupStats

Then there are a few about the SQL Agent as well:

Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>Alerts
Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>Jobs
Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>JobSteps
Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>Statistics
Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>SystemJobs

Potentially we could use a query such as this one to enumerate all the counters: SELECT __class FROM meta_class WHERE __class LIKE 'Win32_PerfRawData_MSSQL%'. But the more I think about it, the more I think this is a collector which will require some flags to specify which databases to look at. Otherwise we'll probably export a _lot_ more than needed, and do potentially quite expensive queries

[martinlindhe]

Currently I'm trying to make a minimal working sql collector:

• enumerate local server instances
• enumerate databases per instance (was thinking along the lines of the iis collector)
• exposing some trivial metrics for these
• trying to do all of the above without need of authentication, so it can be zero-conf. (not sure its possible)
• using the white/blacklist filter to select databases
• then, follow up by adding more functionality in later patches.

As you say, there's tons of metrics to collect.

[martinlindhe]

Also, there's this confusion (for me) about different services to speak to:
these all have wmi counters:

• SQL Server
• SQL Agent
• SQL Browser

[martinlindhe]

And finally, SQL Browser is exposing some kind of autodiscovery on port 1434 (udp).

[carlpett]

Some thoughts:

• Authentication should be fine, we're running the service as LOCAL SYSTEM by default, which should have access to all the counters (we should never be authenticating wíth the database itself this way)
• Getting the instances can probably be skipped by using wildcards in the WMI query

Regarding the services, the Server is the server parts, unsurprisingly :) The Agent is used to run scheduled jobs, some basic monitoring, etc (one particularily interesting class here: Win32_PerfRawData_SQLAgent<INSTANCE>_SQLAgent<INSTANCE>Alerts). And last the browser, which as you say is used for discovery. Basically when we run multi-instance installs, it's a lot simpler to let the client do discovery rather than hardcoding some port. AFAIK this one doesn't expose any counters?

[martinlindhe]

I pushed my work-in-progress branch to https://github.com/martinlindhe/wmi_exporter/tree/sql-server

I really don't know if/when I can finish it into a working state, if someone else wants to to take a stab at it, feel free!

[pazitzik]

these are some sql counters:
BatchRequestsPersec
ConnectionResetPersec
LoginsPersec

[carlpett]

FYI, there is an mssql_exporter here, which might make this issue obsolete.

[martinlindhe]

@carlpett good find

I think we better recommend users to check out https://github.com/awaragi/prometheus-mssql-exporter if this comes up.
However, it is written in node which might be cumbersome for users. Anyhow, someone should be able to reimplement a go-based collector based on that one if there is need.

Closing for now