你的位置:首页 > 数据库

[数据库]通过本地Agent监控Azure sql database


 

背景:

虽然Azure sql database有DMVs可以查看DTU等使用情况,但记录有时间限制,不会一直保留。为了更好监控Azure_sql_database上各个库的DTU使用情况、数据库磁盘使用情况、阻塞等情况。通过本地的Agent的job使用link server 链接到各个Azure sql database 对应库(本地Ip能直连azure sql database),把相关的信息读取出来,存储在本地已新建好的对应表中,通过分析本地对应表中记录来实现监控azure sql database各个库的情况。如需了解azure sql database 与 ssms在开发上的一些区别。

 

基本思路:

第一步:本地库中新建好相应的表用来存放从azure sql database 上读取的记录;

第二步:在本地实例中新建好各个对应azure sql database 各个库的数据库链接,并把相关信息存放在azure_dblink_configure表中;

第三步:在本地库中新建好存储过程用来处理azure sql database上的记录存储在本地对应的表中;

第四步:在本地数据库的代理中新建job通过计划循环调用存储过程;

本地测试环境:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

    Feb 10 2012 19:39:15

    Copyright (c) Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

 

具体实现步骤:

第一步:新建库新建表

 1 --新建保存监控记录的库 2 IF DB_ID('azure_monitor') IS NOT NULL 3   DROP DATABASE azure_monitor; 4 GO 5 CREATE DATABASE azure_monitor; 6 GO  7 USE azure_monitor; 8 GO  9 --在保存监控记录的库上新建如下表: 10 IF OBJECT_ID('azure_dblink_configure','U') IS NOT NULL 11 DROP TABLE azure_dblink_configure; 12  13 CREATE TABLE azure_dblink_configure 14   ( 15    id INT IDENTITY(1, 1) , 16    dblink NVARCHAR(200) NOT NULL , --dblink 17    dbname NVARCHAR(50) NOT NULL , 18    descriptions NVARCHAR(200) , --描述 19    okflag BIT DEFAULT ( 1 ) 20         NOT NULL ,  ---1启用,0停用 21    createuser NVARCHAR(20) , --创建人 22    createdate DATETIME DEFAULT ( GETDATE() ) 23              NOT NULL , --创建时间 24    updatedate DATETIME DEFAULT ( GETDATE() ) 25              NOT NULL  ---更新时间 26   ); 27 ALTER TABLE azure_dblink_configure ADD CONSTRAINT PK_azure_dblink_configure PRIMARY KEY(dblink,dbname); 28  29 --监控存储空间表 30 IF OBJECT_ID('monitor_azure_spaceused','U') IS NOT NULL 31 DROP TABLE monitor_azure_spaceused; 32  33 CREATE TABLE monitor_azure_spaceused 34   ( 35    id INT IDENTITY(1, 1) 36       PRIMARY KEY , 37    dblink NVARCHAR(200), 38    database_name VARCHAR(200) , 39    [sum_database(G)] decimal(18, 2), 40    execute_time_beijing DATETIME, 41    create_time DATETIME DEFAULT(GETDATE()) 42   ); 43  44 --监控DTU等情况表  45 IF OBJECT_ID('monitor_azure_DTU', 'U') IS NOT NULL 46   DROP TABLE monitor_azure_DTU; 47  48 CREATE TABLE monitor_azure_DTU 49   ( 50    id INT IDENTITY(1, 1) 51       PRIMARY KEY , 52    dblink NVARCHAR(200), 53    database_name VARCHAR(200) , 54    beijin_end_time DATETIME NULL , 55    avg_cpu_percent DECIMAL NULL , 56    avg_data_io_percent DECIMAL NULL , 57    avg_log_write_percent DECIMAL NULL , 58    avg_memory_usage_percent DECIMAL NULL , 59    xtp_storage_percent DECIMAL NULL , 60    max_worker_percent DECIMAL NULL , 61    max_session_percent DECIMAL NULL , 62    dtu_limit INT NULL , 63    create_time DATETIME DEFAULT ( GETDATE() ) 64 ); 65  66 CREATE INDEX IX_monitor_azure_DTU ON monitor_azure_DTU ([database_name]) INCLUDE ([beijin_end_time]); 67  68 --监控阻塞表  69 IF OBJECT_ID('monitor_azure_blocked', 'U') IS NOT NULL 70   DROP TABLE monitor_azure_blocked; 71  72 CREATE TABLE monitor_azure_blocked 73   ( 74    id INT IDENTITY(1, 1) 75       PRIMARY KEY , 76    dblink NVARCHAR(200), 77    dbname VARCHAR(200) , 78    spid SMALLINT NOT NULL , 79    kpid SMALLINT NOT NULL , 80    blocked SMALLINT NOT NULL , 81    waittype [VARCHAR](MAX) NOT NULL , 82    waittime BIGINT NOT NULL , 83    lastwaittype NCHAR(32) NOT NULL , 84    waitresource NCHAR(256) NOT NULL , 85    dbid SMALLINT NOT NULL , 86    uid SMALLINT NULL , 87    cpu INT NOT NULL , 88    physical_io BIGINT NOT NULL , 89    memusage INT NOT NULL , 90    login_time DATETIME NOT NULL , 91    last_batch DATETIME NOT NULL , 92    ecid SMALLINT NOT NULL , 93    open_tran SMALLINT NOT NULL , 94    status NCHAR(30) NOT NULL , 95    sid [VARCHAR](MAX) NOT NULL , 96    hostname NCHAR(128) NOT NULL , 97    program_name NCHAR(128) NOT NULL , 98    hostprocess NCHAR(10) NOT NULL , 99    cmd NCHAR(16) NOT NULL ,100    nt_domain NCHAR(128) NOT NULL ,101    nt_username NCHAR(128) NOT NULL ,102    net_address NCHAR(12) NOT NULL ,103    net_library NCHAR(12) NOT NULL ,104    loginame NCHAR(128) NOT NULL ,105    context_info [VARCHAR](MAX) NOT NULL ,106    sql_handle [VARCHAR](MAX) NOT NULL ,107    stmt_start INT NOT NULL ,108    stmt_end INT NOT NULL ,109    request_id INT NOT NULL ,110    [text] NVARCHAR(max),111    createtime DATETIME DEFAULT ( GETDATE() )112   );

View Code



第二步:新建link server,针对Azure sql database各个库新建链接

 1 --具体的例子 2 EXEC sp_addlinkedserver  3 @server='azure_sql_db_01', -- dblink名称 4 @srvproduct='',    5 @provider='sqlncli', -- using SQL Server Native Client  6 @datasrc='XXXXXX.database.chinacloudapi.cn', -- 链接的数据库链接  7 @location='',  8 @provstr='',  9 @catalog='your_DB_name'    10 11 EXEC sp_addlinkedsrvlogin 'azure_sql_db_01', 'false', NULL, '用户名', '用户密码';12 --注意用户是否有权限正常执行下述新建的存储过程13 14 EXEC sp_serveroption 'azure_sql_db_01', 'rpc out', true;15 16 17 --插入azure_dblink_configure18 IF NOT EXISTS ( SELECT *19         FROM  azure_dblink_configure20         WHERE  dblink = N'azure_sql_db_01'21             AND dbname = N'your_DB_name' )22   BEGIN 23     INSERT INTO azure_dblink_configure24         ( dblink ,25          dbname ,26          descriptions ,27          createuser28         )29     VALUES ( N'azure_sql_db_01' ,30          N'your_DB_name' ,31          N'某某项目' ,32          N'新建人员'33         );34   END; 

View Code

第三步:在本地新建存储过程

 1 ----监控库azure sql database 的存储过程例子 2 /*============================================= 3 -- Author:  jil.wen 4 -- Create date: 2016/9/6 5 -- Description:  监控azure sql database 上对应库库容量、DTU、阻塞情况; 6 -- demo :  exec dbo.Azure_p_monitor  7  ============================================= */ 8 CREATE PROCEDURE dbo.Azure_p_monitor 9 AS 10   BEGIN  11     SET NOCOUNT ON; 12     DECLARE @linkserver NVARCHAR(MAX);--临时存储linkserver信息 13     DECLARE @dblink NVARCHAR(200);  --dblink名称 14     DECLARE @dbname NVARCHAR(50);   --dbname 名称 15     DECLARE @id INT;         --id 16     DECLARE cur_wen CURSOR FORWARD_ONLY 17     FOR 18       SELECT id , 19           dblink , 20           dbname 21       FROM  azure_dblink_configure 22       WHERE  okflag = 1 23       ORDER BY id ASC; 24     OPEN cur_wen; 25     FETCH NEXT FROM cur_wen INTO @id, @dblink, @dbname; 26     WHILE ( @@FETCH_STATUS = 0 ) 27       BEGIN  28        29         SELECT @linkserver = '[' + @dblink + ']' + '.' + '[' 30             + @dbname + ']';  31        --具体处理业务逻辑 32         BEGIN TRY  33           ----监控DTU存储过程例子 34           BEGIN  35             DECLARE @addtime DATETIME; 36           --取本地对应库的插入记录时间,注意本地的时间与azure sql database上的时间相差8小时 37             IF EXISTS ( SELECT 1 38                   FROM  monitor_azure_DTU 39                   WHERE  database_name = @dbname ) 40               BEGIN  41                 SELECT @addtime = MAX([beijin_end_time]) 42                 FROM  monitor_azure_DTU 43                 WHERE  database_name = @dbname; 44               END;  45             ELSE  --如果为没有,默认是当前时间减一天 46               SELECT @addtime = DATEADD(dd, -1, GETDATE()); 47            -- PRINT @addtime; 48             DECLARE @addtime_nvar NVARCHAR(200); 49             SELECT @addtime_nvar = CAST(@addtime AS NVARCHAR(200)); --转换类型 50            -- DECLARE @tmpsql NVARCHAR(MAX); --调试变量 51             EXEC ( ' INSERT INTO monitor_azure_DTU 52             ( dblink, 53             database_name , 54             beijin_end_time , 55             avg_cpu_percent , 56             avg_data_io_percent , 57             avg_log_write_percent , 58             avg_memory_usage_percent , 59             xtp_storage_percent , 60             max_worker_percent , 61             max_session_percent , 62             dtu_limit 63             ) 64             SELECT '+''''+@dblink+''''+ ' as dblink,'+'''' + @dbname + ''''+' AS database_name , 65             DATEADD(hh, 8, a.end_time) as beijin_end_time , 66             a.avg_cpu_percent , 67             a.avg_data_io_percent , 68             a.avg_log_write_percent , 69             a.avg_memory_usage_percent , 70             a.xtp_storage_percent , 71             a.max_worker_percent , 72             a.max_session_percent , 73             a.dtu_limit 74             FROM ' + @linkserver + '.sys.dm_db_resource_stats as a 75             WHERE  end_time > DATEADD(hh, -8,'+'''' +@addtime_nvar +'''' + ')'); 76           END;  77           ----监控阻塞存储过程例子 78           BEGIN  79  80             DECLARE @spid NVARCHAR(50); 81             SELECT @spid = CAST(@@spid AS NVARCHAR(50)); 82             83             84             EXEC (' 85             INSERT INTO monitor_azure_blocked( dblink,dbname, spid, kpid, blocked, waittype, waittime, lastwaittype, waitresource, [dbid], [uid], cpu, physical_io, memusage, login_time, last_batch, ecid, open_tran, [status], [sid], hostname, [program_name], hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, [context_info], [sql_handle], stmt_start, stmt_end, request_id,text) 86             SELECT '+''''+@dblink+''''+' as dblink,* 87             FROM openquery('+@dblink+','' SELECT b.name AS dbname , 88             a.spid , 89             a.kpid , 90             a.blocked , 91             a.waittype , 92             a.waittime , 93             a.lastwaittype , 94             a.waitresource , 95             a.[dbid] , 96             a.[uid] , 97             a.cpu , 98             a.physical_io , 99             a.memusage ,100             DATEADD(hh, 8, a.login_time) AS login_time ,--已换算成北京时间101             DATEADD(hh, 8, a.last_batch) AS last_batch ,--已换算成北京时间102             a.ecid ,103             a.open_tran ,104             a.[status] ,105             a.[sid] ,106             a.hostname ,107             a.[program_name] ,108             a.hostprocess ,109             a.cmd ,110             a.nt_domain ,111             a.nt_username ,112             a.net_address ,113             a.net_library ,114             DATEADD(hh, 8, a.login_time) AS loginame ,--换算成北京时间115             a.[context_info] ,116             a.[sql_handle] ,117             a.stmt_start ,118             a.stmt_end ,119             a.request_id,120             c.text from sys.sysprocesses a inner join sys.databases b ON a.[dbid]=b.database_id cross apply sys.dm_exec_sql_text(a.sql_handle) c121             WHERE  a.spid > 50122             AND a.blocked > 0123             AND a.spid <>'+@SPID+''')' );124            125           END;126           ----监控库容量的存储过程例子127           BEGIN 128 129             EXEC 130             ( 'INSERT INTO [dbo].[monitor_azure_spaceused]131             ( dblink,132             database_name ,133             [sum_database(G)] ,134             execute_time_beijing135             )136             SELECT '+''''+@dblink+ ''''+' as dblink,'+''''+ @dbname+ ''''+' AS database_name , --监控的具体库名137             ROUND(( SUM(reserved_page_count) * 8.0 / 1024 ) / 1024, 2) AS [sum_database(G)] ,138             DATEADD(hh, 8, GETDATE()) AS execute_time_beijing139             FROM '+ @linkserver+'.sys.dm_db_partition_stats' );140           END; 141       142         END TRY 143       144       145         BEGIN CATCH146           SELECT ERROR_MESSAGE();147           --如链接不成功需要作废该链接,启用下述备注的代码148           --UPDATE azure_dblink_configure149           --SET   okflag = 0 ,150           --    updatedate = GETDATE()151           --WHERE  id = @id;152         END CATCH;153       154        -- PRINT @tmpsql;155         FETCH NEXT FROM cur_wen INTO @id, @dblink, @dbname;156       END;157    158     DEALLOCATE cur_wen;159     SET NOCOUNT OFF;160   END; 

View Code  

第四步:本地Agent 使用job调用存储过程

Agent中job设置详情省略,请自行百度。注意计划时间间隔合理设置。

 

参考资料:

sys.dm_db_resource_stats
sys.resource_stats

补充:

1)可以考虑用SSIS来实现监控;
2)也可以考虑不新建DBLink,直接在agent中使用sqlcmd来调用azure sql database。
3)为了便于直观查看监控的数据,可以考虑用Power BI等把监控的数据友好展示出来。