创建监控数据的表
```sql
USE [distribution]
GO
/****** Object: Table [dbo].[tmpPendingResult] Script Date: 2021/6/30 13:19:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tmpPendingResult](
[publisher] [nvarchar](200) NULL,
[publisher_db] [nvarchar](200) NULL,
[publication] [nvarchar](200) NULL,
[subscriber] [nvarchar](200) NULL,
[subscriber_db] [nvarchar](200) NULL,
[subscription_type] [nvarchar](200) NULL,
[pendingcmdcount] [bigint] NULL,
[estimatedprocesstime] [bigint] NULL
) ON [PRIMARY]
GO
```
增加一个每分钟的定时作业
```sql
use distribution
delete from distribution.dbo.tmpPendingResult
declare my_cursor cursor for --my_cursor为游标的名称，随便起
SELECT a.publisher ,
a.publisher_db ,
a.publication ,
c.name AS subscriber ,
b.subscriber_db AS subscriber_db ,
CAST(b.subscription_type AS VARCHAR) AS subscription_type
FROM dbo.MSreplication_monitordata a ( NOLOCK )
JOIN ( SELECT publication_id ,
subscriber_id ,
subscriber_db ,
subscription_type
FROM MSsubscriptions (NOLOCK)
GROUP BY publication_id ,
subscriber_id ,
subscriber_db ,
subscription_type
) b ON a.publication_id = b.publication_id
JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id
WHERE a.agent_type = 1 --这是游标my_cursor的值，这里随便发挥看业务场景
--打开游标
open my_cursor --没什么好说的
--变量
declare @publisher NVARCHAR(200)
declare @publisher_db NVARCHAR(200)
declare @publication NVARCHAR(200)
declare @subscriber NVARCHAR(200)
declare @subscriber_db NVARCHAR(200)
declare @subscription_type NVARCHAR(200)
declare @pendingcmdcount BIGINT
declare @estimatedprocesstime BIGINT
declare @sql NVARCHAR(2000)
--循环游标
fetch next from my_cursor into @publisher,@publisher_db,@publication,@subscriber,@subscriber_db,@subscription_type
while @@FETCH_STATUS=0 --假如检索到了数据继续执行
begin
set @sql='select @pendingcmdcount=pendingcmdcount,@estimatedprocesstime=estimatedprocesstime FROM OPENROWSET(''SQLOLEDB'',''Server='+@publisher+';Trusted_Connection=yes;'',''set fmtonly off;exec distribution..sp_replmonitorsubscriptionpendingcmds @publisher='''''+@publisher+''''',@publisher_db='''''+@publisher_db+''''',@publication='''''+@publication+''''',@subscriber='''''+@subscriber+''''',@subscriber_db='''''+@subscriber_db+''''',@subscription_type=0'')'
exec sp_executesql @sql,N'@pendingcmdcount BIGINT out,@estimatedprocesstime BIGINT out',@pendingcmdcount out,@estimatedprocesstime out
insert into distribution.dbo.tmpPendingResult values(@publisher,@publisher_db,@publication,@subscriber,@subscriber_db,@subscription_type,@pendingcmdcount,@estimatedprocesstime)
fetch next from my_cursor into @publisher,@publisher_db,@publication,@subscriber,@subscriber_db,@subscription_type
end--关闭释放游标
close my_cursor
deallocate my_cursor
```
1、开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
```sql
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
```
2、关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：
```sql
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
```
获取数据暴露metrics
```bash
docker run -d \
--name prometheus-sql \
-p 8080:8080 \
-v /opt/docker/prometheus-sql/queries.yml:/queries.yml \
--link sqlagent:sqlagent \
dbhi/prometheus-sql
```
修改queries.yml
```bash
cat /opt/docker/prometheus-sql/queries.yml
```
```yml
- pendingcmdcount:
driver: mssql
connection:
host: x.x.x.x
port: 1433
user: monitor
password: xxxxxx
database: master
sql: >
select publisher,publisher_db,subscriber,subscriber_db,publication,sum(pendingcmdcount) as pe from distribution.dbo.tmpPendingResult group by publisher,publisher_db,subscriber,subscriber_db,publication
data-field: pe
interval: 30s
```
查看暴露的参数 http://172.16.11.10:8080/metrics
```
...
query_result_pendingcmdcount{publication="xgj_w1_stl_dingding",publisher="mssql01",publisher_db="xgj_w1_stl",subscriber="172.16.13.3",subscriber_db="dingding_source"} 0
query_result_pendingcmdcount{publication="xgj_w1_stl_dingding_p1",publisher="mssql01",publisher_db="xgj_w1_stl",subscriber="172.16.13.3",subscriber_db="dingding_source"} 0
query_result_pendingcmdcount{publication="xgj_w1_stl_dingding_p2",publisher="mssql01",publisher_db="xgj_w1_stl",subscriber="172.16.13.3",subscriber_db="dingding_source"} 19
...
```
