MSSQL enhancements: support user databases

[lalit-satapathy]

The MSSQL Integration currently loads "transaction_log metrics" from following dbs:

• tempdb
• msdb
• model
• master

The integration code should be updated to load from all the dbs.

[lalit-satapathy]

The short-term solution to this would be to add an optional input in transaction_log for users to enter a customer DB name. The metrics can be extracted for this customer DB along with all system DBs. CC @ManojS-shetty

[VF-JustinMolnar]

Hi @lalit-satapathy ,
I am interested in the short term solution you describe. Do you have any details on how we can add an optional input to the transaction_log so we can capture our custom databases?
Thanks.

[ManojS-shetty]

Hi @lalit-satapathy , I am interested in the short term solution you describe. Do you have any details on how we can add an optional input to the transaction_log so we can capture our custom databases? Thanks.

HI @VF-JustinMolnar We would like to inform you that as Lalit suggested we will be providing this optional input in transaction_log as a part of package release.

We will be updating the progress of the development and testing on this issue thread.

Thank you

[Danouchka]

Hi @lalit-satapathy for me it's a bug compared to Metricbeat capabilities , user called it a regression. Why on that point, Metricbeat retrieves metrics for all DBs and Elastic-Agent only system DBs ?
Additional input , even on short term, does not make sense because it supposes to enter manually each DB name.
But the idea is also to monitor Customer DBs for which the DBA or infra team would not be aware of.

[lalit-satapathy]

Hi @Danouchka,

This is only a "short term solution" as mentioned in here. The long-term solution is TBD.

[ritalwar]

Hi, similar to metricbeat, I am working on retrieving metrics for all the DBs using agent as well.
Will be updating the progress here.

[ritalwar]

Short-term solution where user database can be manually provided is done. #4869.

[rameshelastic]

@ritalwar , lets update the latest on this issue.

[ritalwar]

Working on long term solution approach where we can get all user DBs metrics for transaction log.
Able to resolve it for sys.dm_db_log_stats table data and able to get data for all the DBs.
Trying to find a way to get all Dbs data from sys.dm_db_log_space_usage table.

[ritalwar]

Considering a strategy where a first integration can be added to obtain a list of all databases. The user can then edit the integration to add database names from that list and obtain the corresponding metrics.

[Danouchka]

Hi In that case, when you retrieve the list of all database, you have also to get some useful information (asked by customer DBA) such as if they are online or not, their file path, the occupied size of the file etc.. I had written an ER for that here #4260 But why not retrieving all of databases metrics ? For instance, When you retrieve metrics from GCE Compute and the customer has thousands of VM, we dont enter the hostnames we want to monitor one by one in the GCP integration So why would it be different approach here ? Thanks, Dan Dan Abitbol Solutions Architect SEMEA, elastic.co Search. Observe. Protect. +33662168617 

…

Le 8 févr. 2023 à 06:06, Richa Talwar ***@***.***> a écrit : Considering a strategy where a first integration can be added to obtain a list of all databases. The user can then edit the integration to add database names from that list and obtain the corresponding metrics. — Reply to this email directly, view it on GitHub <#4108 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALSQI4XJ7WZFK63EY2SNT4DWWMSWXANCNFSM6AAAAAAQB6L4ZQ>. You are receiving this because you were mentioned.

[Danouchka]

Hi

Your strategy may be good but please

1/ explain why it's different from GCP compute metrics
2/ do you agree then that we have to implement #4260 before

Thank you

[ritalwar]

Hi,

I'm not certain about the comparison, so currently looking into the other possibilities as well.

Additionally, as mentioned earlier, trying to find a means to collect data for all Dbs from the sys.dm_db_log_space_usage table, making some headway with the use of the "DBCC SQLPERF (LOGSPACE)" query, which is giving Log Size (MB) and Log Space Used (%) data for all the dbs.

Trying to further get  total_log_size_in_bytes and log_space_in_bytes_since_last_backup data for all databases.

[Danouchka]

Hi @ritalwar

Please combine your query with the latest one mentionned in #4260
As we need those information (path to filename, online status) as well. Thank you

For reminder, The query mentionned in #4260. (just one issue with this one below, SpaceUsed is null for user DBs)
SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)*100./(size/128.0 ) as percentfree FROM sys.master_files WHERE type IN (0,1);

[Danouchka]

Hello

Regarding latest elastic-agent integration "Microsoft SQL Server" , version 1.13.0, we have still issues.
Although we can add user databases, some metrics are not retrieved well regarding the transaction log
Elastic agent is 8.6.1

In elastic-integration, I added the 2 following user DBs

• Microsoft.eShopOnWeb.Identity
• Microsoft.eShopOnWeb.CatalogDb

I have 2 issues regarding transaction log metrics
a) I have part of the metrics for Microsoft.eShopOnWeb.Identity. You can compare with metricbeat.
Elastic agent integration

Metricbeat mssql module

b) I have data only for Microsoft.eShopOnWeb.Identity and not Microsoft.eShopOnWeb.CatalogDb
Elastic agent integration

Metricbeat mssql module

c) In Elastic Agent integration, I have the following error

[elastic_agent.metricbeat][error] Error fetching data for metricset sql.query: fetch table mode failed: mssql: Database ‘Microsoft’ does not exist. Make sure that the name is entered correctly.

Does elastic agent have issues with User DB dotted names ?

Elastic Agent diagnostics available in MP

[Danouchka]

My integration settings
cc @lalit-satapathy

[ritalwar]

Separate issue filed to support user dbs with special characters.
Please track here: #5528

[rameshelastic]

@ritalwar , can we close this too?

[ritalwar]

We currently support user databases in such a way that customers can add a list of user databases to observe performance and transaction-log metrics.
However, it would take some time to support all user databases by default; @lalit-satapathy can we create a separate issue to provide that support, or should we keep this issue open and decide priority for this in 8.9?

[shmsr]

Hi @ritalwar

Please combine your query with the latest one mentionned in #4260 As we need those information (path to filename, online status) as well. Thank you

For reminder, The query mentionned in #4260. (just one issue with this one below, SpaceUsed is null for user DBs) SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)*100./(size/128.0 ) as percentfree FROM sys.master_files WHERE type IN (0,1);

For user database(s), querying from sys.master_files and getting NULL for the concerned filled seems expected. From the documentation of sys.master_files:

Contains a row per file of a database as stored in the master database.

So it does not say user databases and that's why I think you are getting NULL. To get the relevant info I think you have to use sys.database_files instead.

Example:

• https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql#database-data-space-allocated-and-unused-allocated-space
• https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver16#examples

cc: @Danouchka

[Danouchka]

Hi @shmsr I will give a try thank you ! and let you know

[Danouchka]

I get this below.
Strange, I get some NULL regardless using sys.database_files or sys.master_files.

sqlcmd -S localhost -U SA -P sqlMS200675# -Q "SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, CAST(max_size AS bigint)/128.0 AS max_file_size_mb, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0)*100./(size/128.0 ) as percentfree FROM sys.master_files  WHERE type_desc IN ('ROWS','LOG') ;"
DbName                                                                                                                           FileName                                                                                                                         type_desc                                                    CurrentSizeMB       FreeSpaceMB                   State_desc                                                   physical_name                                                                                                                                                                                                                                                        max_file_size_mb             percentfree
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------- ----------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- ----------------------------------------
master                                                                                                                           master                                                                                                                           ROWS                                                                    4.437500                       .812500 ONLINE                                                       /var/opt/mssql/data/master.mdf                                                                                                                                                                                                                                                           -.007812                               18.3098591
master                                                                                                                           mastlog                                                                                                                          LOG                                                                     2.000000                      1.351563 ONLINE                                                       /var/opt/mssql/data/mastlog.ldf                                                                                                                                                                                                                                                          -.007812                               67.5781500
master                                                                                                                           tempdev                                                                                                                          ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/tempdb.mdf                                                                                                                                                                                                                                                           -.007812                                     NULL
master                                                                                                                           templog                                                                                                                          LOG                                                                     8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/templog.ldf                                                                                                                                                                                                                                                          -.007812                                     NULL
master                                                                                                                           tempdev2                                                                                                                         ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/tempdb2.ndf                                                                                                                                                                                                                                                          -.007812                                     NULL
master                                                                                                                           tempdev3                                                                                                                         ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/tempdb3.ndf                                                                                                                                                                                                                                                          -.007812                                     NULL
master                                                                                                                           tempdev4                                                                                                                         ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/tempdb4.ndf                                                                                                                                                                                                                                                          -.007812                                     NULL
master                                                                                                                           modeldev                                                                                                                         ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/model.mdf                                                                                                                                                                                                                                                            -.007812                                     NULL
master                                                                                                                           modellog                                                                                                                         LOG                                                                     8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/modellog.ldf                                                                                                                                                                                                                                                         -.007812                                     NULL
master                                                                                                                           MSDBData                                                                                                                         ROWS                                                                   13.437500                          NULL ONLINE                                                       /var/opt/mssql/data/MSDBData.mdf                                                                                                                                                                                                                                                         -.007812                                     NULL
master                                                                                                                           MSDBLog                                                                                                                          LOG                                                                      .500000                          NULL ONLINE                                                       /var/opt/mssql/data/MSDBLog.ldf                                                                                                                                                                                                                                                    2097152.000000                                     NULL
master                                                                                                                           Microsoft.eShopOnWeb.CatalogDb                                                                                                   ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb.mdf                                                                                                                                                                                                                                   -.007812                                     NULL
master                                                                                                                           Microsoft.eShopOnWeb.CatalogDb_log                                                                                               LOG                                                                     8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/Microsoft.eShopOnWeb.CatalogDb_log.ldf                                                                                                                                                                                                                         2097152.000000                                     NULL
master                                                                                                                           Microsoft.eShopOnWeb.Identity                                                                                                    ROWS                                                                    8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity.mdf                                                                                                                                                                                                                                    -.007812                                     NULL
master                                                                                                                           Microsoft.eShopOnWeb.Identity_log                                                                                                LOG                                                                     8.000000                          NULL ONLINE                                                       /var/opt/mssql/data/Microsoft.eShopOnWeb.Identity_log.ldf                                                                                                                                                                                                                          2097152.000000                                     NULL

(15 rows affected)
[root@sa-da-webserver ~]# sqlcmd -S localhost -U SA -P sqlMS200675# -Q "SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0 AS FreeSpaceMB, State_desc as State_desc, physical_name as physical_name, CAST(max_size AS bigint)/128.0 AS max_file_size_mb, (size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint)/128.0)*100./(size/128.0 ) as percentfree FROM sys.database_files  WHERE type_desc IN ('ROWS','LOG') ;"
DbName                                                                                                                           FileName                                                                                                                         type_desc                                                    CurrentSizeMB       FreeSpaceMB                   State_desc                                                   physical_name                                                                                                                                                                                                                                                        max_file_size_mb             percentfree
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------- ----------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------- ----------------------------------------
master                                                                                                                           master                                                                                                                           ROWS                                                                    4.437500                       .812500 ONLINE                                                       /var/opt/mssql/data/master.mdf                                                                                                                                                                                                                                                           -.007812                               18.3098591
master                                                                                                                           mastlog                                                                                                                          LOG                                                                     2.000000                      1.351563 ONLINE                                                       /var/opt/mssql/data/mastlog.ldf                                                                                                                                                                                                                                                          -.007812                               67.5781500

(2 rows affected)
[root@sa-da-webserver ~]#

[shmsr]

@Danouchka Do you have any user-defined DB? I just see master. Also, with the second command, there are just 2 rows and that too of master and I do not any NULL there. Do you mean something else?

[Danouchka]

Good Point !
Microsoft.eShopOnWeb.CatalogDb was supposed to be a user DB ...let me check how it was created !

[shmsr]

To summarize, we already have a solution in place which enables users to manually feed the user databases along with the already set system databases (as default). But to close this issue we’d ideally want to have a solution that gets all accessible databases on a server.

I have a proposal that suggests a simple approach to how we can achieve the same instead of introducing SQL chaining similar to that of HTTP JSON chaining in beats. Implementation is quite simple and it'd help us close this issue with a solid and simple solution without affecting older functionality.

Even if we introduce SQL chaining which will introduce some kind of chaining where we use the response of one query into another, I hardly have any use case that cannot be achieved with a simpler alternative. Considering, integrations only fetch metrics, logs, etc. (for instrumentation/ observability needs) from the databases; the requirement of some kind of chaining seems unnecessary. If someone has a solid use case, I am all ears. In case, we have solid use cases then we'll definitely explore adding some kind of chaining.

Proposal: Recommended approach

• Add a new field in integrations for mssql module to enter database agnostic queries. Add queries as well.
• Add a new field in beats in the SQL module to respect the field and accordingly do the next few steps. Name of all databases are fetched on every call to Fetch and names of the databases are collected and then given queries are executed for each and every database.

Please see elastic/beats#35688 for more details.

[shmsr]

The feature requested is released in the 2.2.0 version of Microsoft SQL Server. For more info see: #7500

Please ensure to use the 8.10 stack or above in order to use the fetch_from_all_databases feature.