您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > sql database各个库的情况

sql database各个库的情况

发布时间:2020-01-04 04:03编辑:威尼斯官方网站登录浏览(69)

     

    背景:

    就算Azure sql database有DMVs能够查看DTU等采取情形,但记录一时间限制,不会直接保存。为了更加好监察和控制Azure_sql_database上各类库的DTU使用状态、数据库磁盘使用状态、拥塞等气象。通过地方的Agent的job使用link server 链接到各种Azure sql database 对应库(本地Ip能直连azure sql databaseState of Qatar,把相关的音讯读抽出来,存款和储蓄在地面已新建好的对应表中,通过深入分析本地对应表中著录来得以完毕监察和控制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

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

    图片 3图片 4

     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

    其三步:在地面新建存款和储蓄进度

    图片 5图片 6

      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等把督察的多少友好展示出来。

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:sql database各个库的情况

    关键词: