背景： 雖然Azure sql database有DMVs可以查看DTU等使用情況，但記錄有時間限制，不會一直保留。為了更好監控Azure_sql_database上各個庫的DTU使用情況、資料庫磁碟使用情況、阻塞等情況。通過本地的Agent的job使用link server 鏈接到各個Azure ...
背景：
雖然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_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_statssys.resource_stats
