MSSQLServer 数据采集

简介

采集 MSSQLServer 数据上报到 DataFlux 中

前置条件

• 已安装 MSSQLServer 2017 及以上版本。

配置

创建ServiceBroker实体

采用Service Broker的方式实现异步操作，需要创建Service Broker相关实体，包括消息、队列、合约和服务。

'/demo/message' 是官方推荐的 URL 式命名，也可以使用其他替代。

ServiceBorker文档

CREATE MESSAGE TYPE [/demo/message]
    VALIDATION = NONE
GO
CREATE CONTRACT [/demo/message_contract]
(
    [/demo/message] SENT BY INITIATOR,
)
GO
CREATE QUEUE [message_queue]
GO
CREATE SERVICE [/demo/message_service] ON QUEUE [message_queue]
(
    [/demo/message_contract]
)
GO

创建测试数据表

创建数据表用以绑定触发器和测试连通性。

CREATE TABLE serverinfo (
    hostname    VARCHAR(128),
    ip        VARCHAR(16),
    cpucore        INT );

INSERT INTO serverinfo (hostname, ip, cpucore) VALUES('ubuntu-db', '192.168.0.11', 4);

启动OLA

在 MSSQLServer 中发送 HTTP 请求，需要开启 OLA（Ole Automation Procedures）自动化过程。开启方式如下：

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO

发送 HTTP 请求函数

建立 HTTP 连接时，HOST 和 Header 要根据 dataway 文档进行填写。

SP_OAMethod 文档

sp_OAMethod 的参数为 DataWay 的接收 URL，需要根据 DataWay 文档来进行配置

根据 DataWay 的文档，为 HTTP 添加对应的 Header；

CREATE FUNCTION send_http (@content VARCHAR(4096))
returns VARCHAR(1024)
AS
BEGIN
    DECLARE    @obj INT,
        @hr INT,
        @msg VARCHAR(1024);

    EXEC @hr = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @obj OUT;    
    EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'POST', 'http://DataWayIP:PORT/v1/write/metrics?template=&token=&shortrp=&precision=ms', 'false';
    EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'User-Agent', 'test-agent';
    EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'X-Trace-Id', 'test-traceid';
    EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'X-Datakit-UUID', 'test-UUID';
    EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL,'X-Version', 'test-version';
    EXEC @hr = sp_OAMethod @obj, 'send', NULL, @content;

    EXEC @hr = sp_OAGetProperty @obj, 'responseText', @msg OUT;
    EXEC @hr = sp_OADestroy @obj;
    return @msg;
END

创建触发器

触发器规则使用insert，并绑定到第二步创建的数据表'serverinfo'。

行协议是 DataWay 接收数据的通用格式，包括Measurement、Tag、Field和Timestamp四个部分，格式如下：

weather,location=us-midwest temperature=82 1465839830100400200
  |    -------------------- --------------  |
  |             |             |             |
  |             |             |             |
+-----------+--------+-+---------+-+---------+
|measurement|,tag_set| |field_set| |timestamp|
+-----------+--------+-+---------+-+---------+

例如：serverinfo,hostname=macbook-pro ip="127.0.0.1",cpucore=8i 1582874030123

行协议文档，需要注意：

• 英文状态下逗号、双引号、等于号和空格都需要转义，在前面加转义字符\

• Field 的值如果是整型，需要在后面加个英文字符 i，以示跟浮点型的区别，比如上面的 cpucore

触发器会将数据拼装成行协议格式的字符串，发送到Service Broker的message_queue中。

CREATE TRIGGER trig ON serverinfo AFTER INSERT
AS
    DECLARE    @handle UNIQUEIDENTIFIER,
        @content VARCHAR(4096),
        @hostname VARCHAR(128),
        @ip VARCHAR(16),
        @cpucore INT,
        @timestamp BIGINT;

    BEGIN DIALOG CONVERSATION @handle
        FROM SERVICE [/demo/message_service]
        TO SERVICE '/demo/message_service'
        ON CONTRACT [/demo/message_contract]
        WITH ENCRYPTION = OFF;

    SET @host = (SELECT host FROM INSERTED);
    SET @ip = (SELECT ip FROM INSERTED);
    SET @cpucore = (SELECT cpucore FROM INSERTED);
    SET @timestamp = (SELECT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()));

    SET @content = (SELECT 'serverinfo, hostname=' + @hostname + ' ip="' + @ip +'",cpucore=' + CAST(@cpucore AS VARCHAR(10)) + 'i ' + CAST(@timestamp AS VARCHAR(13)) + '\n')
    
    -- 如果 SEND 语句不是批处理或存储过程中的第一个语句，则必须使用分号 (;) 终止前面的语句。    
    ;SEND ON CONVERSATION @handle
    MESSAGE TYPE [/demo/message]
        (@content);
GO

消费ServiceBroker队列数据

从message_queue中消费一条数据，并将其当做参数调用send_http()，由该函数将数据发送到 DataWay。

ServiceBroker数据队列消费文档

CREATE PROCEDURE consumer
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE    @handle UNIQUEIDENTIFIER,
        @typename SYSNAME,
        @body VARCHAR(4096);

    BEGIN TRANSACTION;
        RECEIVE TOP(1)
             @handle = conversation_handle,
             @typename = message_type_name,
             @body = message_body
        FROM message_queue;
     
    PRINT @body
     SELECT dbo.send_http(@body);
    COMMIT TRANSACTION;
END
GO