经过当地Agent监察和控制Azure sql database【彩民之家

2019-11-08 08:24 来源:未知

背景

我们在数据库现身堵塞及时邮件预先警告提醒中监察和控制了数据库的封堵情状,为了更加好的保卫安全数据库,特别是进步终端客商顾客体验,大家要尽量制止在数据库中冒出死锁的事态。大家知道搜集死锁能够敞开追踪标识如1204,然后在日记中查阅死锁相关新闻,或许应用Profiler去追踪死锁,我们愿意全体的死锁音信征集到某表供大家早先时期优化剖析利用,大家得以采纳相对比较轻量的自带扩展事件(system_health卡塔尔国来成功那个供给。

其三步:在该地新建存款和储蓄进程

彩民之家高手论坛 1彩民之家高手论坛 2

  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代理发送预先警告邮件,就要小心Agent是或不是正规运作;
  • 是还是不是有调用上述脚本的数据库客户权限;
  • 数据库自带扩充事件system_health是不是正规运作;

  

首先步:新建库新建表

彩民之家高手论坛 3彩民之家高手论坛 4

  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

兑现进程

a.新建寄存死锁的表

IF DB_ID('azure_monitor') IS NULL 
BEGIN 
CREATE DATABASE azure_monitor ;
END 
GO 

ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
GO 

USE [azure_monitor];    --存放死锁信息的库名
GO

IF OBJECT_ID('monitor_deadlock', 'U') IS NOT NULL
    DROP TABLE dbo.monitor_deadlock;
GO
CREATE TABLE [dbo].[monitor_deadlock]
    ( 
      [ServerName] [VARCHAR](50),
      [DataBaseName] [NVARCHAR](100) NULL ,
      [DeadlockID] [BIGINT] NULL ,
      [TransactionTime] [DATETIME] NULL ,
      [DeadlockGraph] [XML] NULL ,
      [DeadlockObjects] [NVARCHAR](MAX) NULL ,
      [Victim] [INT] NOT NULL ,
      [SPID] [INT] NULL ,
      [ProcedureName] [VARCHAR](200) NULL ,
      [LockMode] [CHAR](1) NULL ,
      [Code] [VARCHAR](1000) NULL ,
      [ClientApp] [NVARCHAR](245) NULL ,
      [HostName] [VARCHAR](20) NULL ,
      [LoginName] [VARCHAR](20) NULL ,
      [InputBuffer] [VARCHAR](1000) NULL ,
      [Capture_date] [DATETIME] NOT NULL ,
      [capture_day] AS ( CONVERT([VARCHAR](12), [Capture_date], ( 112 )) ) ,
      [comfirm_user] [NVARCHAR](50) NULL ,
      [comfirm_flag] [INT] NOT NULL
    )
ON  [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [dbo].[monitor_deadlock] ADD  CONSTRAINT [DF__monitor_d__Captu__2CF2ADDF]  DEFAULT (GETDATE()) FOR [Capture_date];
GO
ALTER TABLE [dbo].[monitor_deadlock] ADD  CONSTRAINT [DF__monitor_d__comfi__2DE6D218]  DEFAULT ((1)) FOR [comfirm_flag];
GO

b.新建读取死锁的积攒进度

USE [azure_monitor];
    --存放读取死锁信息的存储过程的库名
GO
IF OBJECT_ID('monitor_P_deadlock', 'P') IS  NULL
    EXEC( 'CREATE  procedure dbo.monitor_P_deadlock AS  ');
GO
/*=============================================
-- Author:    jil.wen
-- Create date: 2017/04/11
-- Description:   监控数据库上死锁情况;
-- demo :   exec dbo.monitor_P_deadlock 
 ============================================= */
ALTER PROCEDURE monitor_P_deadlock
AS
    BEGIN 
      -- DELETE  FROM dbo.monitor_deadlock
      --  WHERE   [capture_day] = CONVERT([VARCHAR](12), GETDATE(), ( 112 ))
      --          AND comfirm_flag = 1;
        DECLARE @SessionName sysname;
        DECLARE @Servername VARCHAR(50);
        SELECT  @Servername = @@SERVERNAME;
        SELECT  @SessionName = 'system_health';
/*
SELECT  Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers
FROM sys.dm_xe_session_targets t
            INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE target_name = 'event_file'
--*/
        IF OBJECT_ID('tempdb..#Events') IS NOT NULL
            BEGIN
                DROP TABLE #Events;
            END;
        DECLARE @Target_File NVARCHAR(1000) ,
            @Target_Dir NVARCHAR(1000) ,
            @Target_File_WildCard NVARCHAR(1000);
        SELECT  @Target_File = CAST(t.target_data AS XML).value('EventFileTarget[1]/File[1]/@name',
                                                              'NVARCHAR(256)')
        FROM    sys.dm_xe_session_targets t
                INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
        WHERE   s.name = @SessionName
                AND t.target_name = 'event_file';
        SELECT  @Target_Dir = LEFT(@Target_File,
                                   LEN(@Target_File) - CHARINDEX('',
                                                              REVERSE(@Target_File)));
        SELECT  @Target_File_WildCard = @Target_Dir   ''   @SessionName
                  '_*.xel';
--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.
        SELECT  DeadlockGraph = CAST(event_data AS XML) ,
                DeadlockID = ROW_NUMBER() OVER ( ORDER BY file_name, file_offset )
        INTO    #Events
        FROM    sys.fn_xe_file_target_read_file(@Target_File_WildCard, NULL,
                                                NULL, NULL) AS F
        WHERE   event_data LIKE '<event name="xml_deadlock_report%';
        WITH    Victims
                  AS ( SELECT   VictimID = Deadlock.Victims.value('@id',
                                                              'varchar(50)') ,
                                e.DeadlockID
                       FROM     #Events e
                                CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess')
                                AS Deadlock ( Victims )
                     ),
                DeadlockObjects
                  AS ( SELECT DISTINCT
                                e.DeadlockID ,
                                ObjectName = Deadlock.Resources.value('@objectname',
                                                              'nvarchar(256)')
                       FROM     #Events e
                                CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*')
                                AS Deadlock ( Resources )
                     )
            INSERT  INTO monitor_deadlock
                    ( ServerName ,
                      DataBaseName ,
                      DeadlockID ,
                      TransactionTime ,
                      DeadlockGraph ,
                      DeadlockObjects ,
                      Victim ,
                      SPID ,
                      ProcedureName ,
                      LockMode ,
                      Code ,
                      ClientApp ,
                      HostName ,
                      LoginName ,
                      InputBuffer
                    )
                    SELECT  @Servername AS ServerName ,
                            DatabaseName ,
                            DeadlockID ,
                            TransactionTime ,
                            DeadlockGraph ,
                            DeadlockObjects ,
                            Victim ,
                            SPID ,
                            ProcedureName ,
                            LockMode ,
                            Code ,
                            ClientApp ,
                            HostName ,
                            LoginName ,
                            InputBuffer
                    FROM    ( SELECT    DatabaseName = LEFT(SUBSTRING(( SELECT
                                                              ( ', '
                                                                o.ObjectName )
                                                              FROM
                                                              DeadlockObjects o
                                                              WHERE
                                                              o.DeadlockID = e.DeadlockID
                                                              ORDER BY o.ObjectName
                                                              FOR
                                                              XML
                                                              PATH('')
                                                              ), 3, 4000),
                                                            CHARINDEX('.',
                                                              SUBSTRING(( SELECT
                                                              ( ', '
                                                                o.ObjectName )
                                                              FROM
                                                              DeadlockObjects o
                                                              WHERE
                                                              o.DeadlockID = e.DeadlockID
                                                              ORDER BY o.ObjectName
                                                              FOR
                                                              XML
                                                              PATH('')
                                                              ), 3, 4000)) - 1) ,
                                        e.DeadlockID ,
                                        TransactionTime = Deadlock.Process.value('@lasttranstarted',
                                                              'datetime') ,
                                        DeadlockGraph ,
                                        DeadlockObjects = SUBSTRING(( SELECT
                                                              ( ', '
                                                                o.ObjectName )
                                                              FROM
                                                              DeadlockObjects o
                                                              WHERE
                                                              o.DeadlockID = e.DeadlockID
                                                              ORDER BY o.ObjectName
                                                              FOR
                                                              XML
                                                              PATH('')
                                                              ), 3, 4000) ,
                                        Victim = CASE WHEN v.VictimID IS NOT NULL
                                                      THEN 1
                                                      ELSE 0
                                                 END ,
                                        SPID = Deadlock.Process.value('@spid',
                                                              'int') ,
                                        ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]',
                                                              'varchar(200)') ,
                                        LockMode = Deadlock.Process.value('@lockMode',
                                                              'char(1)') ,
                                        Code = Deadlock.Process.value('executionStack[1]/frame[1]',
                                                              'varchar(1000)') ,
                                        ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp',
                                                              'varchar(100)'),
                                                              29)
                                                      WHEN 'SQLAgent - TSQL JobStep (Job '
                                                      THEN 'SQLAgent Job: '
                                                             ( SELECT
                                                              name
                                                              FROM
                                                              msdb..sysjobs sj
                                                              WHERE
                                                              SUBSTRING(Deadlock.Process.value('@clientapp',
                                                              'varchar(100)'),
                                                              32, 32) = ( SUBSTRING(sys.fn_varbintohexstr(sj.job_id),
                                                              3, 100) )
                                                             )   ' - '
                                                             SUBSTRING(Deadlock.Process.value('@clientapp',
                                                              'varchar(100)'),
                                                              67,
                                                              LEN(Deadlock.Process.value('@clientapp',
                                                              'varchar(100)'))
                                                              - 67)
                                                      ELSE Deadlock.Process.value('@clientapp',
                                                              'varchar(100)')
                                                    END ,
                                        HostName = Deadlock.Process.value('@hostname',
                                                              'varchar(20)') ,
                                        LoginName = Deadlock.Process.value('@loginname',
                                                              'varchar(20)') ,
                                        InputBuffer = Deadlock.Process.value('inputbuf[1]',
                                                              'varchar(1000)')
                              FROM      #Events e
                                        CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process')
                                        AS Deadlock ( Process )
                                        LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID
                                                              AND v.VictimID = Deadlock.Process.value('@id',
                                                              'varchar(50)')
                            ) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason
ORDER BY                    DeadlockID DESC;
    END; 

c.在Agent新建job调用上述【monitor_P_deadlock】存款和储蓄进度

简易,详细情形能够参照他事他说加以考查数据库现身窒碍及时邮件预警提醒(下卡塔尔

d.收罗功效如下

彩民之家高手论坛 5

背景:

虽说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在开荒上的一些组别。

 

测量检验蒙受

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

本土测量检验意况:

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卡塔尔国能够虚构用SSIS来完毕监察和控制;

2卡塔尔国也得以设想不新建DBLink,间接在agent中接收sqlcmd来调用azure sql database。

3卡塔 尔(阿拉伯语:قطر‎为了便利直观查看监控的数量,能够杜撰用Power BI等把监督检查的数码友好显示出来。

 

基本思路:

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

第二步:在地点实例中新建好各类对应azure sql database 各种库的数据库链接,并把有关信息寄存在azure_dblink_configure表中;

其三步:在地面库中新建好存款和储蓄进程用来拍卖azure sql database上的笔录存款和储蓄在当地对应的表中;

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

 

现实贯彻步骤:

参照他事他说加以调查资料:

sys.dm_db_resource_stats
sys.resource_stats

其次步:新建link server,针对Azure sql database各样库新建链接

彩民之家高手论坛 6彩民之家高手论坛 7

 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

第四步:本地Agent 使用job调用存款和储蓄进度

Agent中job设置详细情况省略,能够参谋数据库出现窒碍及时邮件预先警告提示(下卡塔 尔(英语:State of Qatar)。注意布署时间间隔合理设置。

 

TAG标签:
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:经过当地Agent监察和控制Azure sql database【彩民之家