通过本地Agent监控Azure sql database

--新建保存监控记录的库 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_configure
18 IF NOT EXISTS ( SELECT  *
19                 FROM    azure_dblink_configure
20                 WHERE   dblink = N‘azure_sql_db_01‘
21                         AND dbname = N‘your_DB_name‘ )
22     BEGIN 
23         INSERT  INTO azure_dblink_configure
24                 ( dblink ,
25                   dbname ,
26                   descriptions ,
27                   createuser
28                 )
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) c
121                         WHERE   a.spid > 50
122                         AND a.blocked > 0
123                         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_beijing
135                         )
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_beijing
139                         FROM  ‘+  @linkserver+‘.sys.dm_db_partition_stats‘ );
140                     END; 
141              
142                 END TRY 
143             
144             
145                 BEGIN CATCH
146                     SELECT  ERROR_MESSAGE();
147                     --如链接不成功需要作废该链接，启用下述备注的代码
148                     --UPDATE  azure_dblink_configure
149                     --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等把监控的数据友好展示出来。

 

通过本地Agent监控Azure sql database

标签：