详解DBA如何安排SQL Server数据库自动监控

数据库 SQL Server
SQL Server本身给广大DBA提供了不少自动化监控的手段,只是苦于对基础监控架构不熟悉,不够了解而使很多DBA出现重复无效劳动。今天我们就讲讲如何安排SQL Server数据库自动监控,减轻大家的工作负担。

作为DBA,不仅仅是帮助开发人员写优秀的查询脚本、高效的数据存储架构。其实更多时候都是在运维,大部分工作时间都不算饱和的,可是为什么有些DBA如此忙碌、狼狈不抗甚至等到客户或者乙方公司抱怨呢,那很多原因就是对SQL Server 的基础监控架构不熟悉,不够了解,不能自动化安排监控任务。

获取通知:SQL Server为我们提供的诸如SQL Mail,Database Mail 邮件方式的信息发送方式,因此我们利用邮件来进行最基础的获取通知异常等情况。我在之前的文章提过Databasemail,大家可以做为参考

MSSQL数据库邮件系列一(环境搭建)

http://www.cnblogs.com/bhtfg538/archive/2009/09/27/1575329.html

MSSQL数据库邮件系列二(SSMS和TSQL)

http://www.cnblogs.com/bhtfg538/archive/2009/09/28/1576061.html

我提到了基本的邮件配置,但是也许我们的内网数据库是没有不能访问Internet的,如何发送到我们的监控邮箱呢。有朋友可能尝试会插入一个Mail表,然后在另外一台能否访问Internet的内网机器读取该表来发送邮件,其实我们可以通过简单的SMTP中继来实现,我们知道的SMTP其实是个推协议,一般是发送到提供商的STMP服务器,其实我们也能通过发送到自己的smtp服务器然后转发到指定DNS响应的SMTP服务器。

我测试是Windows Server 2008的机器,我们来安装SMTP服务器。首先打开

1.服务器管理器 –> 功能 –> 添加功能 –> SMTP 服务器。

2.安装成功以后,在管理工具 –> IIS (6.0) 管理器打开设置。

sqlmonitor_1

3.首先点击 域 –> 新建域 –>

sqlmonitor_2

(我新建了一个SMTP.QQ.COM的域,所以我要转发邮件到QQ邮箱)。

我们看到QQ提供的SMTP.QQ.COM使用的标准的25端口。所以打开WF.MSC 去添加25端口的出站规则,同时打开入站规则的25(让DB服务器能够中继到该SMTP服务器),不关闭选项卡。

4.在“出站安全”中->选择“基本身份验证”->添加你的邮箱登录帐号 –>(TSL加密根据你SMTP服务器的规则)来设置。

5.选中“SMTP Virtual Server #1” –> 右键属性 –> 切换到 “访问”选项卡 –>在“中继限制”中单击“中继” 添加你对应机器的访问列表。

6.***一步,把你的DatabaseMail帐号设置成 匿名身份验证,SMTP服务器选择你的内网SMTP服务器地址,端口默认25,其他都不变动。

如果发送测试邮件通过说明没问题了,搞不定可以搜搜资料吧,或者直接提问。那么我也就解决了***个问题“我们如何获取通知”,采用最基本的邮件方式。

    详细的监控信息:数据库避免不了一些异常状态,比如错误的脚本导致的异常,空间不够,磁盘挂了,复制失败了等。这里我先提提SQL Server事件。这个意味着SQL Server发生特定错误产生的事件,每个事件都有对应的数据库、严重级别、错误号、错误文本。

打开SSMS->在SQL Server 代理 –>的警告中可以创建警告。一般来说我们关注大于等于13严重级别的事件。为乙方公司提供服务时快速的部署也很重要,因此我整理了一些脚本:

  1.  
  2.  
  3. USE [msdb]  
  4. GO  
  5.    
  6. /****** Object:  Alert [SQL_EVENT_13]    Script Date: 12/08/2010 14:44:53 ******/  
  7. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_13',   
  8.         @message_id=0,   
  9.         @severity=13,   
  10.         @enabled=1,   
  11.         @delay_between_responses=1800,   
  12.         @include_event_description_in=1,   
  13.         @category_name=N'[Uncategorized]',   
  14.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  15. GO  
  16.    
  17. USE [msdb]  
  18. GO  
  19.    
  20. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  21. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_14',   
  22.         @message_id=0,   
  23.         @severity=14,   
  24.         @enabled=1,   
  25.         @delay_between_responses=1800,   
  26.         @include_event_description_in=1,   
  27.         @category_name=N'[Uncategorized]',   
  28.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  29. GO  
  30.    
  31. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  32. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_15',   
  33.         @message_id=0,   
  34.         @severity=15,   
  35.         @enabled=1,   
  36.         @delay_between_responses=1800,   
  37.         @include_event_description_in=1,   
  38.         @category_name=N'[Uncategorized]',   
  39.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  40. GO  
  41.    
  42. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  43. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_16',   
  44.         @message_id=0,   
  45.         @severity=16,   
  46.         @enabled=1,   
  47.         @delay_between_responses=1800,   
  48.         @include_event_description_in=1,   
  49.         @category_name=N'[Uncategorized]',   
  50.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  51. GO  
  52.    
  53. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  54. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_17',   
  55.         @message_id=0,   
  56.         @severity=17,   
  57.         @enabled=1,   
  58.         @delay_between_responses=1800,   
  59.         @include_event_description_in=1,   
  60.         @category_name=N'[Uncategorized]',   
  61.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  62. GO  
  63.    
  64. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  65. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_18',   
  66.         @message_id=0,   
  67.         @severity=18,   
  68.         @enabled=1,   
  69.         @delay_between_responses=1800,   
  70.         @include_event_description_in=1,   
  71.         @category_name=N'[Uncategorized]',   
  72.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  73. GO  
  74.    
  75. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  76. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_19',   
  77.         @message_id=0,   
  78.         @severity=19,   
  79.         @enabled=1,   
  80.         @delay_between_responses=1800,   
  81.         @include_event_description_in=1,   
  82.         @category_name=N'[Uncategorized]',   
  83.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  84. GO  
  85.    
  86. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  87. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_20',   
  88.         @message_id=0,   
  89.         @severity=20,   
  90.         @enabled=1,   
  91.         @delay_between_responses=1800,   
  92.         @include_event_description_in=1,   
  93.         @category_name=N'[Uncategorized]',   
  94.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  95. GO  
  96.    
  97. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  98. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_21',   
  99.         @message_id=0,   
  100.         @severity=21,   
  101.         @enabled=1,   
  102.         @delay_between_responses=1800,   
  103.         @include_event_description_in=1,   
  104.         @category_name=N'[Uncategorized]',   
  105.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  106. GO  
  107.    
  108. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  109. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_22',   
  110.         @message_id=0,   
  111.         @severity=22,   
  112.         @enabled=1,   
  113.         @delay_between_responses=1800,   
  114.         @include_event_description_in=1,   
  115.         @category_name=N'[Uncategorized]',   
  116.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  117. GO  
  118.    
  119. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  120. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_23',   
  121.         @message_id=0,   
  122.         @severity=23,   
  123.         @enabled=1,   
  124.         @delay_between_responses=1800,   
  125.         @include_event_description_in=1,   
  126.         @category_name=N'[Uncategorized]',   
  127.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  128. GO  
  129.    
  130. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  131. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_24',   
  132.         @message_id=0,   
  133.         @severity=24,   
  134.         @enabled=1,   
  135.         @delay_between_responses=1800,   
  136.         @include_event_description_in=1,   
  137.         @category_name=N'[Uncategorized]',   
  138.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  139. GO  
  140.    
  141. /****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/  
  142. EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_25',   
  143.         @message_id=0,   
  144.         @severity=25,   
  145.         @enabled=1,   
  146.         @delay_between_responses=1800,   
  147.         @include_event_description_in=1,   
  148.         @category_name=N'[Uncategorized]',   
  149.         @job_id=N'00000000-0000-0000-0000-000000000000' 
  150. GO 

上面提供了 13-25严重级别的事件。有了事件,必然的,需要一个操作员或者说是监控人员来对获取事件来转发邮件,我们可以看到SQL Server 代理中 有 操作员的选项卡,可以创建操作员:

  1. USE [msdb]  
  2. GO  
  3.    
  4. /****** Object:  Operator [Terry Mao]    Script Date: 01/19/2011 11:37:34 ******/  
  5. EXEC msdb.dbo.sp_add_operator @name=N'Terry Mao',   
  6.         @enabled=1,   
  7.         @weekday_pager_start_time=90000,   
  8.         @weekday_pager_end_time=180000,   
  9.         @saturday_pager_start_time=90000,   
  10.         @saturday_pager_end_time=180000,   
  11.         @sunday_pager_start_time=90000,   
  12.         @sunday_pager_end_time=180000,   
  13.         @pager_days=0,   
  14.         @email_address=N'10000@qq.com',   
  15.         @category_name=N'[Uncategorized]' 
  16. GO 

我添加了一个 “Terry Mao” 的操作人员,对应邮件地址为10000的邮件地址,然后我们可能需要为所有的事件添加订阅人员, 也就是Terry Mao。

在视图中,可以查询到所有创建的警报:

  1. SELECT * FROM msdb.dbo.sysalerts; 

当然不可能一个去添加订阅事件,可以创建类似功能的sp:

  1. CREATE PROC dbo.sp_add_all_notification  
  2.     @Operator sysname  
  3. AS 
  4. BEGIN 
  5. DECLARE @Name sysname,@OperatorID int;  
  6. SELECT @OperatorID = id FROM msdb.dbo.sysoperators WHERE name = @Operator;  
  7. IF @OperatorID IS NULL 
  8.     RETURN -1;  
  9.       
  10. DECLARE Cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT [nameFROM   
  11. msdb.dbo.sysalerts alters WHERE NOT EXISTS  
  12. (SELECT 1 FROM msdb.dbo.sysnotifications notify   
  13. WHERE notify.operator_id = @OperatorID AND notify.alert_id = alters.id);  
  14. OPEN Cur;  
  15.  
  16. FETCH NEXT FROM Cur INTO @Name;  
  17. WHILE @@FETCH_STATUS = 0  
  18. BEGIN 
  19.     EXEC msdb.dbo.sp_add_notification @alert_name = @Name,  
  20. @operator_name = @Operator,  
  21. @notification_method = 1;  
  22.     FETCH NEXT FROM Cur INTO @Name;  
  23. END 
  24.  
  25. CLOSE Cur;  
  26. DEALLOCATE Cur;  
  27. RETURN 0;  
  28. END 

这样就会把所有的事件全部给监控起来,其实我就解决了过滤的监控列表,但是我们可能会对运行中的SQL Server 比如长时间执行的语句,存储过程进行监控来调优。比如,我们给另外一家公司做DBA顾问,就立马需要获取有问题的查询,这个我会在接下来的文章详细说明。其中包括Profile的监控,SQL Server WMI的监控等。

原文链接:http://www.cnblogs.com/bhtfg538/archive/2011/01/19/1939190.html

【编辑推荐】

  1. SQL Server 2000删除实战演习
  2. SQL Server存储过程的命名标准如何进行?
  3. 卸载SQL Server 2005组件的正确顺序
  4. 对SQL Server字符串数据类型的具体描述
  5. SQL Server数据类型的种类与应用
责任编辑:彭凡 来源: 博客园
相关推荐

2010-07-15 17:28:50

SQL Server

2021-03-18 08:20:19

SQLServer数据库SQL

2011-08-11 09:12:31

SQL Server nolock

2010-03-16 10:12:40

SQL Server

2011-08-25 13:41:50

SQL Server 变更跟踪

2015-10-30 14:00:33

adosybaseodbc

2010-07-01 15:02:29

SQL Server数

2010-06-17 14:29:39

2011-08-24 12:49:56

SQL Server托管代码

2011-04-02 11:02:54

SQL Server数文件恢复

2011-08-19 14:53:02

SQL ServerDataRelatio

2011-08-30 11:04:30

链接查询内连接外连接

2011-08-22 11:39:53

SQL Server数PIVOT

2011-08-15 14:29:52

SQL Server数事务

2011-07-28 11:44:46

SQL Server数合并表格数据

2011-03-28 15:28:03

SQL Server 数据库

2009-04-22 09:42:07

SQL Server监视镜像

2021-05-17 06:57:34

SQLServer数据库

2010-07-08 11:05:14

SQL Server数

2011-07-18 15:36:38

定时自动备份SQLServer
点赞
收藏

51CTO技术栈公众号