Monitoring：SqlServer database monitoring
Collect and monitor the general performance Metrics of SqlServer database. Support SqlServer 2017+.
Configuration parameter
| Parameter name | Parameter help description |
|---|---|
| Monitoring Host | Monitored IPV4, IPV6 or domain name. Note⚠️Without protocol header (eg: https://, http://) |
| Monitoring name | Identify the name of this monitoring. The name needs to be unique |
| Port | Port provided by the database. The default is 1433 |
| Query timeout | Set the timeout time when SQL query does not respond to data, unit: ms, default: 3000ms |
| Database name | Database instance name, optional |
| Username | Database connection user name, optional |
| Password | Database connection password, optional |
| URL | Database connection URL，optional，If configured, the database name, user name, password and other parameters in the URL will overwrite the above configured parameters |
| Collection interval | Interval time of monitor periodic data collection, unit: second, and the minimum interval that can be set is 30 seconds |
| Whether to detect | Whether to detect and check the availability of monitoring before adding monitoring. Adding and modifying operations will continue only after the detection is successful |
| Description remarks | For more information about identifying and describing this monitoring, users can note information here |
Collection Metric
Metric set：basic
| Metric name | Metric unit | Metric help description |
|---|---|---|
| machine_name | none | Windows computer name running the server instance |
| server_name | none | Server and instance information SQL Server associated with Windows instance |
| version | none | Version of the instance，SQL Server，format is "major.minor.build.revision" |
| edition | none | The product SQL server version of the installed instance |
| start_time | none | Database start time |
Metric set：performance_counters
| Metric name | Metric unit | Metric help description |
|---|---|---|
| database_pages | none | Database pages, Number of pages obtained (buffer pool) |
| target_pages | none | Target pages, The desired number of pages that the buffer pool must have |
| page_life_expectancy | s | Page life expectancy. The time that data pages stay in the buffer pool. This time is generally greater than 300 |
| buffer_cache_hit_ratio | % | Buffer cache hit ratio, Database buffer pool cache hit rate. The probability that the requested data is found in the buffer pool is generally greater than 80%, otherwise the buffer pool capacity may be too small |
| checkpoint_pages_sec | none | Checkpoint pages/sec, The number of dirty pages written to the disk by the checkpoint per second. If the data is too high, it indicates that there is a lack of memory capacity |
| page_reads_sec | none | Page reads/sec, Number of pages read per second in the cache pool |
| page_writes_sec | none | Page writes/sec, Number of pages written per second in the cache pool |
Metric set：connection
| Metric name | Metric unit | Metric help description |
|---|---|---|
| user_connection | none | Number of connected sessions |
Common Problem
- SSL connection problem fixed
jdk version: jdk11 Description of the problem: SQL Server 2019 uses the SA user connection to report an error Error message:
The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:xxxxxxxxxxxxxxxxxxxx
Screenshot of the problem:
solution:
Use advanced settings when adding SqlServer monitoring, customize JDBC URL, add parameter configuration after the spliced jdbc url, ;encrypt=true;trustServerCertificate=true;This parameter true means unconditionally trust the server returned any root certificate.
Example: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=demo;encrypt=true;trustServerCertificate=true;
Reference document: [microsoft pkix-path-building-failed-unable-to-find-valid-certification](https://techcommunity.microsoft.com/t5/azure-database-support-blog/pkix-path-building- failed-unable-to-find-valid-certification/ba-p/2591304)