The Microsoft SQL Server Exporter is a community-developed tool designed specifically for collecting monitoring metrics from Microsoft SQL Server (MSSQL) databases. It reports performance, health, and other metric data through the MSSQL Exporter, which can be used for monitoring dashboards and anomaly alarms. Tencent Cloud Observability Platform (TCOP) Prometheus offers integration with MSSQL Exporter along with pre-configured Grafana monitoring dashboards.
Note:
If the MSSQL to be monitored is Tencent Cloud TencentDB for SQL Server, it is recommended to use Cloud Monitoring in the Integration Center, which supports one-click collection of cloud product metrics.
2. Select the corresponding Prometheus instance from the instance list.
3. Go to the instance details page, select Data Collection > Integration Center.
4. In the Integration Center, locate and click MSSQL to open an installation window. On the installation page, enter details such as the metric collection name and address, then click save.
Configuration Instructions
Parameters
Description
name
Exporter name, which should meet the following requirements:
The name should be unique.
The name should conform to the following regular expression: '^[a-z0-9]([-a-z0-9]*[a-z0-9])?(\\.[a-z0-9]([-a-z0-9]*[a-z0-9])?)*$'.
TKE is recommended for convenient installation and management of the Exporter.
Prerequisites
A TKE cluster has been created in the region and VPC of the corresponding Prometheus instance, and a namespace has been created for the cluster.
In the TMP console, select the corresponding Prometheus instance, then choose Data Collection > Integrate with TKE to find the corresponding Kubernetes cluster and complete the cluster association. See the guide Associating Clusters.
1. In the left menu, choose Workload > Deployment to access the Deployment management page.
2. Click Create via YAML in the upper right corner of the page to create a YAML file, and select the corresponding namespace for server deployment. The following part shows how to deploy the Exporter by using a YAML file. Sample configurations are as follows:
apiVersion: apps/v1
kind: Deployment
metadata:
labels:
k8s-app: mssql-exporter # Adjust to the corresponding name based on business requirements. It is recommended to include information about the MSSQL instance.
name: mssql-exporter # Adjust to the corresponding name based on business requirements. It is recommended to include information about the MSSQL instance.
namespace: mssql-demo # Adjust to the corresponding namespace based on business requirements.
spec:
replicas: 1
selector:
matchLabels:
k8s-app: mssql-exporter # Adjust to the corresponding name based on business requirements. It is recommended to include information about the MSSQL instance.
template:
metadata:
labels:
k8s-app: mssql-exporter # Adjust to the corresponding name based on business requirements. It is recommended to include information about the MSSQL instance.
spec:
containers:
- env:
- name: SERVER
value: "127.0.0.1"# Adjust to the corresponding MSSQL domain name based on business requirements.
- name: PORT
value: "1433"# Adjust to the corresponding MSSQL port based on business requirements.
- name: USERNAME
value: user # Adjust to the corresponding MSSQL username based on business requirements.
- name: PASSWORD
value: "123456"# Adjust to the corresponding MSSQL password based on business requirements.
- name: EXPOSE
value: "4000"# Metrics export port. Adjust to the corresponding port based on business requirements.
- containerPort: 4000# Open the port corresponding to the EXPOSE environment variable specified above.
name: metric-port
terminationMessagePath: /dev/termination-log
terminationMessagePolicy: File
dnsPolicy: ClusterFirst
imagePullSecrets:
- name: qcloudregistrykey
restartPolicy: Always
schedulerName: default-scheduler
securityContext: {}
terminationGracePeriodSeconds: 30
Step 3: Verification
1. Click the Deployment created in the previous step on the Deployment page to go to the Deployment management page.
2. Click the Log tab. If no error messages are displayed, the setup is successful, as shown below:
3. Click the Pod tab to enter the Pod page.
4. Click Remote login under the operation bar on the right to log in to the Pod. In the command-line window, execute the following wget command for the address exposed by the Exporter to retrieve the corresponding MSSQL metrics. If no data is retrieved, check whether the connection string is correct. Details are as follows:
wget -qO- http://localhost:4000/metrics
The successful outcome is shown in the following figure:
Step 4: Adding a Collection Task
1. Log in to the TMP console and select the corresponding Prometheus instance to go to the management page.
2. In Data Collection > Integrate with TKE, select the associated cluster, and select Data Collection Configuration > Customize Monitoring Configuration > Via YAML to add a collection task.
3. Add a PodMonitors via service discovery to define the collection task. The YAML example is as follows:
apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
name: mssql-exporter # Enter a unique name.
namespace: cm-prometheus # Pay-as-you-go instance: Use the namespace of the cluster. Monthly subscription instance (no longer available): The namespace is fixed. Do not change it.
spec:
podMetricsEndpoints:
-interval: 30s
port: metric-port # Enter the port of the Prometheus Exporter in the Pod YAML file.
path: /metrics # Enter the path of the Prometheus Exporter. Default value: /metrics.
relabelings:
-action: replace
sourceLabels:
- instance
regex: (.*)
targetLabel: instance
replacement:'crs-xxxxxx'# Replace with the corresponding MSSQL instance ID.
namespaceSelector:# Select the namespace where the MSSQL Exporter pod to be monitored is located.
matchNames:
- mssql-demo
selector:# Enter the labels of the Pod to be monitored to locate the target Pod.
matchLabels:
k8s-app: mssql-exporter
Account Creation and Authorization
Tencent Cloud SQL Server
1. Log in to the Tencent Cloud SQL Server console, select the corresponding SQL Server instance, and enter the management page.
2. On the management page, select Account Management, then click Create Account.
Fill in the account name and password, select Account Type as Standard Account. Next, select the database to monitor and set the permission to Read-Only. Finally, click OK to complete the account creation.
Other SQL Server Services
The SQL Server monitor account needs to be granted VIEW SERVER STATE and VIEW ANY DEFINITION permissions. If monitoring a specific database is required, grant the appropriate db_datareader permission on that database. With connectivity between the client computer and SQL Server Database server confirmed, you can use the SSMS client or sqlcmd command line tool to connect to the database. For specific connection instructions, please refer to Tencent Cloud SQL Server official documentation. The following account creation operations are based on the SSMS client.
1. In the SSMS client, select File > Connect Object Explorer.
Fill in the server name in the form of {ip,port}, select SQL Server Authentication for identity authentication, then enter the login name and password, choose optional for encryption, and finally click Connect to complete the connection to the database server.
2. Right-click the database connected in the previous step, then click Create Query.
Enter the SQL statement in the right panel, then click Execute to run the statement.
3. Create a SQL Server account and grant it VIEW SERVER STATE and VIEW ANY DEFINITION permissions. The following sample command shows how to create a monitor account with both account name and password set to monitor, and grant the permissions required for monitoring.
USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name ='monitor')
CREATE LOGIN [monitor] WITH PASSWORD=N'monitor', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GRANT VIEW SERVER STATE TO [monitor];
GRANT VIEW ANY DEFINITION TO [monitor];
The command execution result is shown in the following figure:
4. To obtain monitoring data for a database, create a database user with the same name as the monitor account in the database and grant it the db_datareader role permission. The following example shows how to create an appropriate database user and grant permissions for the monitor account in a database named prometheus.
The command execution result is shown in the following figure:
Viewing Monitoring Information
Prerequisites
The Prometheus instance has been bound to a Grafana instance.
Operation Steps
1. Log in to the TMP console and select the corresponding Prometheus instance to go to the management page.
2. On the Basic Information page of the instance, locate the bound Grafana address. Open and log in, then navigate to the database folder to find the monitoring dashboard for the MSSQL instance. View the relevant monitoring data for the instance, as shown below:
Configuring Alarm
TMP supports configuring alarm rules based on the actual business situation. For details, see Creating Alarm Rules.
