通过本地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: )
具体实现步骤：
第一步：新建库新建表
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 ,
dbname NVARCHAR(50) NOT NULL ,
descriptions NVARCHAR(200) ,
okflag BIT DEFAULT ( 1 )
NOT NULL ,
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) ,
decimal(18, 2),
execute_time_beijing DATETIME,
create_time DATETIME DEFAULT(GETDATE())
);
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 () INCLUDE ();
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 (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 (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 (MAX) NOT NULL ,
sql_handle (MAX) NOT NULL ,
stmt_start INT NOT NULL ,
stmt_end INT NOT NULL ,
request_id INT NOT NULL ,
NVARCHAR(max),
createtime DATETIME DEFAULT ( GETDATE() )
);
View Code
第二步：新建link server，针对Azure sql database各个库新建链接
EXEC sp_addlinkedserver
@server='azure_sql_db_01',
@srvproduct='',
@provider='sqlncli',
@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;
IF NOT EXISTS ( SELECT*
FROM azure_dblink_configure
WHERE dblink = N'azure_sql_db_01'
AND dbname = N'your_DB_name' )
BEGIN
INSERTINTO azure_dblink_configure
( dblink ,
dbname ,
descriptions ,
createuser
)
VALUES( N'azure_sql_db_01' ,
N'your_DB_name' ,
N'某某项目' ,
N'新建人员'
);
END;
View Code
第三步：在本地新建存储过程
CREATEPROCEDURE dbo.Azure_p_monitor
AS
BEGIN
SET NOCOUNT ON;
DECLARE @linkserver NVARCHAR(MAX);
DECLARE @dblink NVARCHAR(200);
DECLARE @dbname NVARCHAR(50);
DECLARE @id INT;
DECLARE cur_wen CURSOR FORWARD_ONLY
FOR
SELECTid ,
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
BEGIN
DECLARE @addtime DATETIME;
IF EXISTS ( SELECT1
FROM monitor_azure_DTU
WHERE database_name = @dbname )
BEGIN
SELECT@addtime = MAX()
FROM monitor_azure_DTU
WHERE database_name = @dbname;
END;
ELSE
SELECT@addtime = DATEADD(dd, -1, GETDATE());
DECLARE @addtime_nvar NVARCHAR(200);
SELECT@addtime_nvar = CAST(@addtime AS NVARCHAR(200));
EXEC ( 'INSERTINTO 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, , , cpu, physical_io, memusage, login_time, last_batch, ecid, open_tran, , , hostname, , hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, , , stmt_start, stmt_end, request_id,text)
SELECT '+''''+@dblink+''''+' as dblink,*
FROMopenquery('+@dblink+','' SELECTb.name AS dbname ,
a.spid ,
a.kpid ,
a.blocked ,
a.waittype ,
a.waittime ,
a.lastwaittype ,
a.waitresource ,
a. ,
a. ,
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. ,
a. ,
a.hostname ,
a. ,
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. ,
a. ,
a.stmt_start ,
a.stmt_end ,
a.request_id,
c.text from sys.sysprocesses a inner join sys.databases b ON a.=b.database_idcross 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
( 'INSERTINTO .
( dblink,
database_name ,
,
execute_time_beijing
)
SELECT '+''''+@dblink+ ''''+' as dblink,'+''''+ @dbname+ ''''+' AS database_name , --监控的具体库名
ROUND(( SUM(reserved_page_count) * 8.0 / 1024 ) / 1024, 2) AS ,
DATEADD(hh, 8, GETDATE()) AS execute_time_beijing
FROM'+@linkserver+'.sys.dm_db_partition_stats' );
END;
END TRY
BEGIN CATCH
SELECTERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM cur_wen INTO @id, @dblink, @dbname;
END;
DEALLOCATE cur_wen;
SET NOCOUNT OFF;
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等把监控的数据友好展示出来。
页:
[1]