Multiple datasource for MSSQL

[hamedkhosrawi]

Hi, I'm trying to configure the sql_exporter to support multiple MSSQL data sources. However, I am encountering issues with my YAML configuration when trying to include multiple targets under the same job. The error I get is:

Error creating exporter: yaml: unmarshal errors:
  line 12: cannot unmarshal !!seq into map[string]config.Secret"

Here is the configuration I have tried so far:

global:
  scrape_timeout_offset: 500ms
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3
  max_connection_lifetime: 10m

jobs:
  - name: multi_datasource_job
    static_configs:
      - targets:
          - {name: "stage", data_source_name: "sqlserver://USER_MON:FK*[email protected]:1433/master"}
          - {name: "local", data_source_name: "sqlserver://SA:[email protected]:1433/master"}
    collectors:
      - stage_db

collector_files:
  - "*.collector.yml"

Despite this configuration, I am still encountering the error mentioned above. I'm unable to figure out how to properly configure multiple MSSQL data sources under a single job.
Questions:
Can you guide me on how to properly configure multiple MSSQL data sources in the exporter?
Is there a limitation in sql_exporter that prevents multiple targets within a single job for MSSQL?
Environment:
sql_exporter version: 0.15.0
MSSQL version: SQL Server 2017/2019
Dockerized environment

[burningalchemist]

Hi @hamedkhosrawi, the configuration provided on your end is incorrect, try this out:

...
jobs:
  - job_name: multi_datasource_job
    static_configs:
      - targets:
            local: "sqlserver://USER_MON:FK*[email protected]:1433/master"
            stage: "sqlserver://SA:[email protected]:1433/master"
    collectors:
      - stage_db
...

The difference is as follows:

1. should be job_name instead of name for job names;
2. static_configs is a list of targets, but each targets item is a map (not a list);
3. in each targets map, you may provide key=value pairs, where the key is the name of the target (e.g. stage), and the value is a working connection string.

You can find a working example in the Miscellaneous section here (click on multiple connections heading to unfold the section).

In the future, I'll try to invest some time into a proper explanation for invalid configuration on the app side. For now, we have to rely upon the docs in the README.md and available examples.

Let me know if it works on your end. 👍

[hamedkhosrawi]

Thank you so much for the detailed explanation and the clarification regarding the configuration differences! I really appreciate the time you took to provide the examples and the guidance on job_name, static_configs, and the structure of the targets. It all makes much more sense now, and the reference to the Miscellaneous section in the docs is incredibly helpful. 🙌

If I encounter any issues while implementing or testing further, I'll definitely reach out. Thanks again for your willingness to help and for your efforts in improving the documentation for everyone.
best regards