背景：

虽然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

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

具体实现步骤：

第一步：新建库新建表

 --新建保存监控记录的库

 IF DB_ID('azure_monitor') IS NOT NULL

     DROP DATABASE azure_monitor;

 GO

 CREATE DATABASE azure_monitor;

 GO

 USE azure_monitor;

 GO

 --在保存监控记录的库上新建如下表：

 IF OBJECT_ID('azure_dblink_configure','U') IS NOT NULL

 DROP TABLE azure_dblink_configure;

 CREATE TABLE azure_dblink_configure

     (

       id INT IDENTITY(1, 1) ,

       dblink NVARCHAR(200) NOT NULL , --dblink

       dbname NVARCHAR(50) NOT NULL ,

       descriptions NVARCHAR(200) ,  --描述

       okflag BIT DEFAULT ( 1 )

                  NOT NULL ,   ---1启用，0停用

       createuser NVARCHAR(20) ,  --创建人

       createdate DATETIME DEFAULT ( GETDATE() )

                           NOT NULL ,  --创建时间

       updatedate DATETIME DEFAULT ( GETDATE() )

                           NOT NULL   ---更新时间

     );

 ALTER TABLE azure_dblink_configure ADD CONSTRAINT PK_azure_dblink_configure PRIMARY KEY(dblink,dbname);

 --监控存储空间表

 IF OBJECT_ID('monitor_azure_spaceused','U') IS NOT NULL

 DROP TABLE monitor_azure_spaceused;

 CREATE TABLE monitor_azure_spaceused

     (

       id INT IDENTITY(1, 1)

              PRIMARY KEY ,

       dblink NVARCHAR(200),

       database_name VARCHAR(200) ,

       [sum_database(G)] decimal(18, 2),

       execute_time_beijing DATETIME,

       create_time DATETIME DEFAULT(GETDATE())

     );

 --监控DTU等情况表

 IF OBJECT_ID('monitor_azure_DTU', 'U') IS NOT NULL

     DROP TABLE monitor_azure_DTU;

 CREATE TABLE monitor_azure_DTU

     (

       id INT IDENTITY(1, 1)

              PRIMARY KEY ,

       dblink NVARCHAR(200),

       database_name VARCHAR(200) ,

       beijin_end_time DATETIME NULL ,

       avg_cpu_percent DECIMAL NULL ,

       avg_data_io_percent DECIMAL NULL ,

       avg_log_write_percent DECIMAL NULL ,

       avg_memory_usage_percent DECIMAL NULL ,

       xtp_storage_percent DECIMAL NULL ,

       max_worker_percent DECIMAL NULL ,

       max_session_percent DECIMAL NULL ,

       dtu_limit INT NULL ,

       create_time DATETIME DEFAULT ( GETDATE() )

 );

 CREATE INDEX IX_monitor_azure_DTU ON monitor_azure_DTU ([database_name]) INCLUDE ([beijin_end_time]);

 --监控阻塞表

 IF OBJECT_ID('monitor_azure_blocked', 'U') IS NOT NULL

     DROP TABLE monitor_azure_blocked;

 CREATE TABLE monitor_azure_blocked

     (

       id INT IDENTITY(1, 1)

              PRIMARY KEY ,

       dblink NVARCHAR(200),

       dbname VARCHAR(200) ,

       spid SMALLINT NOT NULL ,

       kpid SMALLINT NOT NULL ,

       blocked SMALLINT NOT NULL ,

       waittype [VARCHAR](MAX) NOT NULL ,

       waittime BIGINT NOT NULL ,

       lastwaittype NCHAR(32) NOT NULL ,

       waitresource NCHAR(256) NOT NULL ,

       dbid SMALLINT NOT NULL ,

       uid SMALLINT NULL ,

       cpu INT NOT NULL ,

       physical_io BIGINT NOT NULL ,

       memusage INT NOT NULL ,

       login_time DATETIME NOT NULL ,

       last_batch DATETIME NOT NULL ,

       ecid SMALLINT NOT NULL ,

       open_tran SMALLINT NOT NULL ,

       status NCHAR(30) NOT NULL ,

       sid [VARCHAR](MAX) NOT NULL ,

       hostname NCHAR(128) NOT NULL ,

       program_name NCHAR(128) NOT NULL ,

       hostprocess NCHAR(10) NOT NULL ,

       cmd NCHAR(16) NOT NULL ,

       nt_domain NCHAR(128) NOT NULL ,

       nt_username NCHAR(128) NOT NULL ,

       net_address NCHAR(12) NOT NULL ,

       net_library NCHAR(12) NOT NULL ,

       loginame NCHAR(128) NOT NULL ,

       context_info [VARCHAR](MAX) NOT NULL ,

       sql_handle [VARCHAR](MAX) NOT NULL ,

       stmt_start INT NOT NULL ,

       stmt_end INT NOT NULL ,

       request_id INT NOT NULL ,

       [text]  NVARCHAR(max),

       createtime DATETIME DEFAULT ( GETDATE() )

     );

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

 --具体的例子

 EXEC sp_addlinkedserver

 @server='azure_sql_db_01', -- dblink名称

 @srvproduct='',

 @provider='sqlncli', -- using SQL Server Native Client

 @datasrc='XXXXXX.database.chinacloudapi.cn', -- 链接的数据库链接

 @location='',

 @provstr='',

 @catalog='your_DB_name'        

 EXEC sp_addlinkedsrvlogin 'azure_sql_db_01', 'false', NULL, '用户名', '用户密码';

 --注意用户是否有权限正常执行下述新建的存储过程

 EXEC sp_serveroption 'azure_sql_db_01', 'rpc out', true;

 --插入azure_dblink_configure

 IF NOT EXISTS ( SELECT  *

                 FROM    azure_dblink_configure

                 WHERE   dblink = N'azure_sql_db_01'

                         AND dbname = N'your_DB_name' )

     BEGIN

         INSERT  INTO azure_dblink_configure

                 ( dblink ,

                   dbname ,

                   descriptions ,

                   createuser

                 )

         VALUES  ( N'azure_sql_db_01' ,

                   N'your_DB_name' ,

                   N'某某项目' ,

                   N'新建人员'

                 );

     END;

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

 ----监控库azure sql database 的存储过程例子

 /*=============================================

 -- Author:    jil.wen

 -- Create date: 2016/9/6

 -- Description:   监控azure sql database 上对应库库容量、DTU、阻塞情况；

 -- demo :   exec dbo.Azure_p_monitor

  ============================================= */

 CREATE  PROCEDURE dbo.Azure_p_monitor

 AS

     BEGIN

         SET NOCOUNT ON;

         DECLARE @linkserver NVARCHAR(MAX);--临时存储linkserver信息

         DECLARE @dblink NVARCHAR(200);    --dblink名称

         DECLARE @dbname NVARCHAR(50);     --dbname 名称

         DECLARE @id INT;                  --id

         DECLARE cur_wen CURSOR FORWARD_ONLY

         FOR

             SELECT  id ,

                     dblink ,

                     dbname

             FROM    azure_dblink_configure

             WHERE   okflag = 1

             ORDER BY id ASC;

         OPEN cur_wen;

         FETCH NEXT FROM cur_wen INTO @id, @dblink, @dbname;

         WHILE ( @@FETCH_STATUS = 0 )

             BEGIN 

                 SELECT  @linkserver = '[' + @dblink + ']' + '.' + '['

                         + @dbname + ']';

                --具体处理业务逻辑

                 BEGIN TRY

                     ----监控DTU存储过程例子

                     BEGIN

                         DECLARE @addtime DATETIME;

                      --取本地对应库的插入记录时间,注意本地的时间与azure sql database上的时间相差8小时

                         IF EXISTS ( SELECT  1

                                     FROM    monitor_azure_DTU

                                     WHERE   database_name = @dbname )

                             BEGIN

                                 SELECT  @addtime = MAX([beijin_end_time])

                                 FROM    monitor_azure_DTU

                                 WHERE   database_name = @dbname;

                             END;

                         ELSE   --如果为没有，默认是当前时间减一天

                             SELECT  @addtime = DATEADD(dd, -1, GETDATE());

                        -- PRINT @addtime;

                         DECLARE @addtime_nvar NVARCHAR(200);

                         SELECT  @addtime_nvar = CAST(@addtime AS NVARCHAR(200)); --转换类型

                        -- DECLARE @tmpsql NVARCHAR(MAX);  --调试变量

                         EXEC ( '  INSERT  INTO monitor_azure_DTU

                         ( dblink,

                         database_name ,

                         beijin_end_time ,

                         avg_cpu_percent ,

                         avg_data_io_percent ,

                         avg_log_write_percent ,

                         avg_memory_usage_percent ,

                         xtp_storage_percent ,

                         max_worker_percent ,

                         max_session_percent ,

                         dtu_limit

                         )

                         SELECT '+''''+@dblink+''''+ ' as dblink,'+'''' + @dbname + ''''+' AS database_name ,

                         DATEADD(hh, 8, a.end_time) as beijin_end_time ,

                         a.avg_cpu_percent ,

                         a.avg_data_io_percent ,

                         a.avg_log_write_percent ,

                         a.avg_memory_usage_percent ,

                         a.xtp_storage_percent ,

                         a.max_worker_percent ,

                         a.max_session_percent ,

                         a.dtu_limit

                         FROM  ' + @linkserver + '.sys.dm_db_resource_stats as a

                         WHERE   end_time > DATEADD(hh, -8,'+'''' +@addtime_nvar +'''' + ')');

                     END;

                     ----监控阻塞存储过程例子

                     BEGIN 

                         DECLARE @spid NVARCHAR(50);

                         SELECT  @spid = CAST(@@spid AS NVARCHAR(50));

                         EXEC ('

                         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)

                         SELECT '+''''+@dblink+''''+' as dblink,*

                         FROM  openquery('+@dblink+','' SELECT  b.name AS dbname ,

                         a.spid ,

                         a.kpid ,

                         a.blocked ,

                         a.waittype ,

                         a.waittime ,

                         a.lastwaittype ,

                         a.waitresource ,

                         a.[dbid] ,

                         a.[uid] ,

                         a.cpu ,

                         a.physical_io ,

                         a.memusage ,

                         DATEADD(hh, 8, a.login_time) AS login_time ,--已换算成北京时间

                         DATEADD(hh, 8, a.last_batch) AS last_batch ,--已换算成北京时间

                         a.ecid ,

                         a.open_tran ,

                         a.[status] ,

                         a.[sid] ,

                         a.hostname ,

                         a.[program_name] ,

                         a.hostprocess ,

                         a.cmd ,

                         a.nt_domain ,

                         a.nt_username ,

                         a.net_address ,

                         a.net_library ,

                         DATEADD(hh, 8, a.login_time) AS loginame ,--换算成北京时间

                         a.[context_info] ,

                         a.[sql_handle] ,

                         a.stmt_start ,

                         a.stmt_end ,

                         a.request_id,

                         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

                         WHERE   a.spid > 50

                         AND a.blocked > 0

                         AND a.spid <>'+@SPID+''')' );

                     END;

                     ----监控库容量的存储过程例子

                     BEGIN 

                         EXEC

                         ( 'INSERT  INTO [dbo].[monitor_azure_spaceused]

                         ( dblink,

                         database_name ,

                         [sum_database(G)] ,

                         execute_time_beijing

                         )

                         SELECT '+''''+@dblink+ ''''+' as dblink,'+''''+ @dbname+ ''''+' AS database_name , --监控的具体库名

                         ROUND(( SUM(reserved_page_count) * 8.0 / 1024 ) / 1024, 2) AS [sum_database(G)] ,

                         DATEADD(hh, 8, GETDATE()) AS execute_time_beijing

                         FROM  '+  @linkserver+'.sys.dm_db_partition_stats' );

                     END; 

                 END TRY 

                 BEGIN CATCH

                     SELECT  ERROR_MESSAGE();

                     --如链接不成功需要作废该链接，启用下述备注的代码

                     --UPDATE  azure_dblink_configure

                     --SET     okflag = 0 ,

                     --        updatedate = GETDATE()

                     --WHERE   id = @id;

                 END CATCH;

               --  PRINT @tmpsql;

                 FETCH NEXT FROM cur_wen INTO @id, @dblink, @dbname;

             END;

         DEALLOCATE cur_wen;

         SET NOCOUNT OFF;

     END;

第四步：本地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

背景：