金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 与连接本地库的一些操作区别,通过本地Agent监控

与连接本地库的一些操作区别,通过本地Agent监控

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-05 18:52

 

背景

我们知道Azure Sql Database 可以降低运维成本、是一种Pass模式,有资源弹性设置,可以自由调整资源自动对应不同业务高峰(当然也可以降低费用成本),也方便项目后期的资源扩展,以及一些其他优点或特性,这就不一一列出。那么数据库开发工程师来说,操作Azure Sql Database 与我们使用的SSMS(Microsoft Sql Server Management Studio)在本地库上操作有哪些区别。

 

我们简单从如下几部分进行比较

1.创建对象比较;(简单列一下Azure 上创建数据库操作,SSMS创建数据库默认省略)

2.连接库比较;

3.操作比较;

 

背景:

虽然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在开发上的一些区别。

 

1.创建对象比较

 金沙棋牌app手机下载 1

 

 

 金沙棋牌app手机下载 2

 金沙棋牌app手机下载 3

金沙棋牌app手机下载 4

 

 金沙棋牌app手机下载 5

 

 金沙棋牌app手机下载 6

金沙棋牌app手机下载 7

基本思路:

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

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

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

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

2.链接库的比较

金沙棋牌app手机下载 8

 

金沙棋牌app手机下载 9

金沙棋牌app手机下载 10

金沙棋牌app手机下载,通过上述图可以看到,在azure sql database 中涉及到实例一级的对象都是没有了。

在azure sql database大概总结如下:

1.没有 AlwaysON,没有数据库镜像,没有日志传送,没有复制。 有异地复制可以直接添加数据库辅助副本(库大小是指数据文件大小);

2.没有 agent ;如需执行定时调度可以参考《通过本地Agent监控Azure_sql_database》

3.没有SSIS;

4.不支持实例一级的对象(如不支持全局临时表、没有Profiler等);如需监控可以参考《如何在Azure sql database 下监控正在运行的脚本或某个存储过程是否已运行》

 

本地测试环境:

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: )

 

3.具体操作方面比较

 金沙棋牌app手机下载 11

 通过上图发现azure sql database系统数据库中没有tempdb,那能否使用临时表之类的对象?

首先查看当前的azure sql database 版本号

金沙棋牌app手机下载 12

 测试临时表(不是sql server 2016 temporal table)

 金沙棋牌app手机下载 13

金沙棋牌app手机下载 14

可以使用临时表,不支持使用全局的临时表。

金沙棋牌app手机下载 15

 Pass模式下,某些命令已不支持。

金沙棋牌app手机下载 16

 注意默认的getdate()是UTC时间,与北京时间相差8小时。

金沙棋牌app手机下载 17

 不能在本库显式调用其他库,如:use master

 

默认情况下,azure sql database 不支持跨库操作,需要创建扩展表,建同构同名的表,只能是只读且架构唯一。如要存储过程进行跨库操作,需使用sp_execute_remote调用。

 

ssms 2012界面下存储过程点右键如下图:

金沙棋牌app手机下载 18

SSMS 2016界面下存储过程点右键如下图:

金沙棋牌app手机下载 19

UI界面操作,建议安装最新版本SSMS.或者 exec sp_helptext 加具体的存储过程名称,执行,把执行结果拷出才是存储过程的脚本;

 

参考资料

如何创建扩展表

sp_execute_remote

具体实现步骤:

总结

azure sql database 与ssms相比:

1.没有 AlwaysON,没有数据库镜像,没有日志传送,没有复制。 有异地复制可以直接添加数据库辅助副本(库大小是指数据文件大小);

2.没有 agent ;如需执行定时调度可以参考《通过本地Agent监控Azure sql database》

3.没有SSIS;

4.不支持实例一级的对象(例如不支持全局变量、没有Profiler等);如需监控可以参考《如何在Azure sql database 下监控正在运行的脚本或某个存储过程是否已运行》

5.有些命令不支持,因azure sql database 是Pass模式;

6.注意默认getdate()是取UTC时间,与北京时间相差8小时;

7.不能在本库显式调用其他库;

8.默认情况下,azure sql database 不支持跨库操作,需要创建扩展表,建同构同名的表,只能是只读且架构唯一;

9.azure sql database 是基于v12的引擎,要在UI界面上操作建议安装ssms 2016版操作。

10.用户名限制,不能使用adminadministratorsaguestroot

第一步:新建库新建表

金沙棋牌app手机下载 20金沙棋牌app手机下载 21

  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各个库新建链接

金沙棋牌app手机下载 22金沙棋牌app手机下载 23

 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

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

金沙棋牌app手机下载 24金沙棋牌app手机下载 25

  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等把监控的数据友好展示出来。

 

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:与连接本地库的一些操作区别,通过本地Agent监控

关键词:

上一篇:没有了

下一篇:没有了