Microsft SQL Server Extension - Best Practices

[Joao1]

Hello,

I'm currently implementing a solution to monitor various Microsoft SQL Server Instances and Databases in a client. I'm currently having trouble with some metrics not having information about some databases. I was reading the documentation and i found this:

Limitations

Aggregated metrics for database files

The two metrics below

• sql-server.databases.file.usedSpace
• sql-server.databases.file.emptySpace

are only reported for the database the extension is currently connected to. This is due to sys.allocation_units only containing information about used pages of the database that is currently used inside the connection.

That got me wondering about this part "are only reported for the database the extension is currently connected to". 

Now the question:

Does that mean that if a certain instance has 30 databases, when defining the endpoint, instead of just having the instance defined and only 1 endpoint, i need to define 30 endpoints with each having its own database explicited?

Thanks in advance,

João

[AntonPineiro · Accepted solution]

Hi,

You just need to define one endpoint, but you can see that metric only in database connected.

Best regards

[Joao1]

Hello,

I understand what you're saying and that's what i currently have. But i'm missing data from some metrics.

My question:

If i want to have data from all databases (without missing metric data, and i also confirmed that the user has all the permissions needed (admin)), do i need to define a endpoint for each database, even if it belongs to the same instance?

If the database is not defined in the endpoint, is the default one "master" ?

Best regards,

Joao

[Joao1]

Update:

I found another instance where it seems you have to be connected to a specific database if you want to retrieve data for Longest Queries. Documentation:

"

Top queries

How to enable?

Collection of top queries ordered by total duration can be enabled using the Queries feature set.

Prerequisites

• Query Store must be enabled on SQL Server instance.
• The database from which queries are collected is determined by:
  • Explicit database name specified in the endpoint for monitoring configuration; or
  • Default database configured for the connected user."
    
    

  Note: Query Store is only available starting from Microsoft SQL Server 2016+. SQL Server 2022 has it enabled by default.

  So, i'm currently working on a automatism that will create in mass hundreds of endpoints (Each with its own database defined) in a single/multi monitoring configuration to check the ActiveGate performance and extension behaviour.

  Best regards,

  Joao

[Joao1]

Hi,

If possible, i need help with something related to the YAML of the extension. I was editing it for a custom extension using the same data source. I was trying to edit a query which is used for the database state, to get information for a specific database. My question:

1. I created a variable with perItem: true 
so that i can define it in each endpoint, which is called:

- id: database

    displayName: Database Name

    perItem: true

    type: text

this is the query:

metrics:

          - key: sql-server.databases.state

            value: col:state

        query: >-

          SELECT

            name                  AS database_name,

            state,

            state_desc,

            recovery_model_desc,

            user_access_desc,

            is_auto_close_on,

            ISNULL(

              DATABASEPROPERTYEX(name, 'Updateability'),

              CASE

                is_read_only

                WHEN

                  0

                THEN

                  'READ_WRITE'

                ELSE

                  'READ_ONLY'

              END

            ) AS updateability

          FROM

            sys.databases

          WHERE

            name = 'var:database'

I tried it like that with 'var:database' and with ${var:database} and both didn't work. Does anyone know a way to dynamically put the information there?

[jose_montanez]

Does your variable definition have the 'vars' like this? I don't use in a query, but in my dimensions var:name works.

vars:

- id: ...

[gsaharan]

Hi all, 

I am also working on the same extension, did anyone faced any "ERROR" like-Failed to assign monitoring configuration to ActiveGate. Reason: High system resources consumption detected for datasources (soft limit)

Do we have to make any changed in the AG config file?