MSSQL Exporters - recommendations please

[Pete Leese]

Hi everyone,

I will describe my use-case in the hope someone can recommend the best exporter.

I have around 30 SQL servers each containing around 10 separate instances that I would like to monitor and present visual data back via Grafana.

Ideally would like to configure a single exporter to connect to all SQL instances and not have a separate exporter per SQL instance I wish to monitor (each instance has a number of separate DB's mounted).

I am particularly interested in capturing ASYNC_NETWORK_IO metrics along with other performance metrics, and an added bonus if we can add certain queries to measure query response times further down the line, but not strictly required at this point.

Thanks in advance :)

Pete

[Alin Sînpălean]

The list of "reviewed" (as in someone likely took a look at the documentation before it being added to the list) third party Prometheus exporters is available at https://prometheus.io/docs/instrumenting/exporters/

There exists a SQL Server exporter implemented in Node.js, https://github.com/awaragi/prometheus-mssql-exporter . There are also a couple of database-agnostic ones -- https://github.com/justwatchcom/sql_exporter and https://github.com/chop-dbhi/prometheus-sql -- implemented in Go. I tried a couple of these and ran into all kinds of issues: from collection happening asynchronously, independent from scraping and thus introducing random time skew; to collection running over the deadline and causing multiple scrapes to either fail or return stale data.

So I rolled my own implementation, https://github.com/free/sql_exporter , which works better for me (might not for everyone). It is fully configuration driven (you define the queries that are run and how the results get mapped to metrics) and collection is synchronous with scraping (i.e. the queries are run when the /metrics endpoint is scraped). There is also an option to limit the frequency of collection, in case you're scraping the /metrics endpoint from multiple Prometheus instances. Also, I've built and tested it specifically against MSSQL instances, but with the intent to eventually run it against multiple, different DBMSs (which hasn't happened yet).

Cheers,

Alin.

[Pete Leese]

Thanks for the informative reply. I will check out your implementation today :)

[Pete Leese]

I have had some success with this, but not been able to get it to scrape multiple data sources.

I tried to add in another data_source_name:  line into the config but doesn't seem to scrape more than one instance.

Are you able to give me some pointers on how I can hook this up to connect to multiple instances or is this a limitation currently?

Thanks.

Pete

[Brian Brazil]

On 10 April 2018 at 11:45, 'Pete Leese' via Prometheus Users <promethe...@googlegroups.com> wrote:

I have had some success with this, but not been able to get it to scrape multiple data sources.

I tried to add in another data_source_name:  line into the config but doesn't seem to scrape more than one instance.

Are you able to give me some pointers on how I can hook this up to connect to multiple instances or is this a limitation currently?

This is by design. The general Prometheus architecture is that you have one exporter per application instance, as this mirrors the ideal scenario where each database would expose metrics directly and Prometheus discovers each and decides when each is scraped.

Brian

 

Thanks.

Pete

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

Hi everyone,

I will describe my use-case in the hope someone can recommend the best exporter.

I have around 30 SQL servers each containing around 10 separate instances that I would like to monitor and present visual data back via Grafana.

Ideally would like to configure a single exporter to connect to all SQL instances and not have a separate exporter per SQL instance I wish to monitor (each instance has a number of separate DB's mounted).

I am particularly interested in capturing ASYNC_NETWORK_IO metrics along with other performance metrics, and an added bonus if we can add certain queries to measure query response times further down the line, but not strictly required at this point.

Thanks in advance :)

Pete

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-users+unsubscribe@googlegroups.com.
To post to this group, send email to prometheus-users@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/1be993e2-5a95-4036-8330-ba05828722d0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Brian Brazil

www.robustperception.io

[Pete Leese]

I have around 100 SQL instances that I need to bring into Prometheus, so would need a single exporter I can configure multiple unique instances to scrape, perhaps need additional parameters so scrape configuration can be configured to scrape multiple end points or something. I cant really setup 100 copies of this exporter to scrap constantly, this approach doesn't work

 

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

[Alin Sînpălean]

If you look at the README on GitHub a couple of commits back you'll find out how to have it connect to multiple instances.

But as Brian points out (and as I've experienced first hand) it doesn't work all that well. One instance being slow can, for example, cause scrapes to time out for other instances. That's why I ended up removing it from the documentation.

Cheers,

Alin.

On Tuesday, April 10, 2018 at 12:59:41 PM UTC+2, Brian Brazil wrote:

On 10 April 2018 at 11:45, 'Pete Leese' via Prometheus Users <promethe...@googlegroups.com> wrote:

I have had some success with this, but not been able to get it to scrape multiple data sources.

I tried to add in another data_source_name:  line into the config but doesn't seem to scrape more than one instance.

Are you able to give me some pointers on how I can hook this up to connect to multiple instances or is this a limitation currently?

This is by design. The general Prometheus architecture is that you have one exporter per application instance, as this mirrors the ideal scenario where each database would expose metrics directly and Prometheus discovers each and decides when each is scraped.

Brian

 

Thanks.

Pete

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

Hi everyone,

I will describe my use-case in the hope someone can recommend the best exporter.

I have around 30 SQL servers each containing around 10 separate instances that I would like to monitor and present visual data back via Grafana.

Ideally would like to configure a single exporter to connect to all SQL instances and not have a separate exporter per SQL instance I wish to monitor (each instance has a number of separate DB's mounted).

I am particularly interested in capturing ASYNC_NETWORK_IO metrics along with other performance metrics, and an added bonus if we can add certain queries to measure query response times further down the line, but not strictly required at this point.

Thanks in advance :)

Pete

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To post to this group, send email to promethe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/1be993e2-5a95-4036-8330-ba05828722d0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Brian Brazil

www.robustperception.io

[Pete Leese]

I think I am seeking a SQL exporter that works as a similar concept as Blackbox, where you pass parameters / scrape configurations over to the exporter ?

Can you point me in the direction of the readme that details how to set this up under multi-connection mode and I will check it out ?  

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

[Brian Brazil]

On 10 April 2018 at 12:32, 'Pete Leese' via Prometheus Users <promethe...@googlegroups.com> wrote:

I think I am seeking a SQL exporter that works as a similar concept as Blackbox, where you pass parameters / scrape configurations over to the exporter ?

That style of exporter only makes sense where it's impossible to run an exporter beside the database, which is not the case here. I'm not aware of any of these existing for SQL.

Brian

 

Can you point me in the direction of the readme that details how to set this up under multi-connection mode and I will check it out ?  

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

Hi everyone,

I will describe my use-case in the hope someone can recommend the best exporter.

I have around 30 SQL servers each containing around 10 separate instances that I would like to monitor and present visual data back via Grafana.

Ideally would like to configure a single exporter to connect to all SQL instances and not have a separate exporter per SQL instance I wish to monitor (each instance has a number of separate DB's mounted).

I am particularly interested in capturing ASYNC_NETWORK_IO metrics along with other performance metrics, and an added bonus if we can add certain queries to measure query response times further down the line, but not strictly required at this point.

Thanks in advance :)

Pete

--

You received this message because you are subscribed to the Google Groups "Prometheus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-users+unsubscribe@googlegroups.com.
To post to this group, send email to prometheus-users@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/d0b8a8d6-20bb-43df-b81a-7e623a33c641%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Brian Brazil

www.robustperception.io

[Alin Sînpălean]

Here you go: https://github.com/free/sql_exporter/blob/caf149bcfa2ccacb873e82c7e6bb8014b6a2b81d/README.md

Cheers,

Alin.

[Pete Leese]

Thanks, I will take a look at this, not sure if this is the right solution for me just yet mind.

Have you created any grafana dashboards for this ? If so can you share the json please?

[Ben Kochie]

The exporter is designed to be deployed on localhost on the same node as the database.  This way it's easy to make a 1:1 installation and can leverage your existing deployment automation.  For example, when I was running over 250 MySQL instances, we included the mysqld_exporter on localhost as part of our Chef-based deployment.

--

You received this message because you are subscribed to the Google Groups "Prometheus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-users+unsubscribe@googlegroups.com.
To post to this group, send email to prometheus-users@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/b782c771-5a46-44ae-b0e0-72003bd1c122%40googlegroups.com.

[Alin Sînpălean]

I guess what Pete is getting at is that he has multiple MSSQL instances on each node, i.e. multiple completely independent SQL Server processes running alongside each other. So he only wants one exporter per node, not one exporter per MSSQL process. Probably (marginally) easier to set up and manage one exporter instance per node than 10.

Alin.

On Tuesday, April 10, 2018 at 3:32:07 PM UTC+2, Ben Kochie wrote:

The exporter is designed to be deployed on localhost on the same node as the database.  This way it's easy to make a 1:1 installation and can leverage your existing deployment automation.  For example, when I was running over 250 MySQL instances, we included the mysqld_exporter on localhost as part of our Chef-based deployment.

On Tue, Apr 10, 2018 at 1:10 PM, 'Pete Leese' via Prometheus Users <promethe...@googlegroups.com> wrote:

I have around 100 SQL instances that I need to bring into Prometheus, so would need a single exporter I can configure multiple unique instances to scrape, perhaps need additional parameters so scrape configuration can be configured to scrape multiple end points or something. I cant really setup 100 copies of this exporter to scrap constantly, this approach doesn't work

 

On Monday, 9 April 2018 14:00:12 UTC+1, Pete Leese wrote:

Hi everyone,

I will describe my use-case in the hope someone can recommend the best exporter.

I have around 30 SQL servers each containing around 10 separate instances that I would like to monitor and present visual data back via Grafana.

Ideally would like to configure a single exporter to connect to all SQL instances and not have a separate exporter per SQL instance I wish to monitor (each instance has a number of separate DB's mounted).

I am particularly interested in capturing ASYNC_NETWORK_IO metrics along with other performance metrics, and an added bonus if we can add certain queries to measure query response times further down the line, but not strictly required at this point.

Thanks in advance :)

Pete

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-use...@googlegroups.com.
To post to this group, send email to promethe...@googlegroups.com.

[Pete Leese]

Yes, each physical server has around 10 DB instances running on them.

I cant see how this can be installed on the same host as the Microsoft SQL server as there are no windows binary.

I would only ever see this remotely collecting metrics from a DB instance.

[Alin Sînpălean]

There is no Windows binary, but Go is cross-platform so you can definitely build a Windows binary from the source code.

    go build github.com/free/sql_exporter/cmd/sql_exporter

should do the trick.

Alin.

[Pete Leese]

I think I can make this work.

If I’m scraping metrics locally against multiple sql instances.

My concern is that usernames and passwords to sql instances will be stored in plain text in the config .yml file.

Have you got any tips for securing ?

Cheers

Pete

[Pete Leese]

[Brian Brazil]

Make sure your file permissions are locked down to not allow anyone else to access the file.

Brian

 

Cheers

Pete

--
You received this message because you are subscribed to the Google Groups "Prometheus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to prometheus-users+unsubscribe@googlegroups.com.
To post to this group, send email to prometheus-users@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/prometheus-users/96572667-da29-430c-828d-44e34368eac9%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Brian Brazil

www.robustperception.io