topic Re: Microsft SQL Server Extension - Best Practices in Extensions

Microsft SQL Server Extension - Best Practices

Joao1 — Wed, 01 Oct 2025 08:55:06 GMT

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

Re: Microsft SQL Server Extension - Best Practices

AntonPineiro — Thu, 02 Oct 2025 06:54:29 GMT

Hi,

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

Best regards

Re: Microsft SQL Server Extension - Best Practices

Joao1 — Thu, 02 Oct 2025 08:13:27 GMT

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

Re: Microsft SQL Server Extension - Best Practices

Joao1 — Mon, 06 Oct 2025 23:08:07 GMT

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

Re: Microsft SQL Server Extension - Best Practices

Joao1 — Thu, 06 Nov 2025 10:21:28 GMT

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

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?

Re: Microsft SQL Server Extension - Best Practices

jose_montanez — Fri, 07 Nov 2025 15:37:35 GMT

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: ...

Re: Microsft SQL Server Extension - Best Practices

gsaharan — Fri, 07 Nov 2025 16:05:28 GMT

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?

Re: Microsft SQL Server Extension - Best Practices

Gramblicka-DXC — Wed, 28 Jan 2026 10:21:23 GMT

Hi,

yes, this issue is caused by the implementation by dynatrace. I raised a product idea for this yet i was told it is what it is. It connects always to master so any user dbs are simply not having their data gathered. You have to rely on disk monitoring for size.