[转载]SQL Server 监控系列-Mikel

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

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

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

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

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

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

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

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

3.首先点击域 –> 新建域 –> （我新建了一个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严重级别的事件。为乙方公司提供服务时快速的部署也很重要，因此我整理了一些脚本：

USE [msdb]

GO

/****** Object:  Alert [SQL_EVENT_13]    Script Date: 12/08/2010 14:44:53 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_13',

        @message_id=0,

        @severity=13,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

USE [msdb]

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_14',

        @message_id=0,

        @severity=14,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_15',

        @message_id=0,

        @severity=15,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_16',

        @message_id=0,

        @severity=16,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_17',

        @message_id=0,

        @severity=17,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_18',

        @message_id=0,

        @severity=18,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_19',

        @message_id=0,

        @severity=19,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_20',

        @message_id=0,

        @severity=20,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_21',

        @message_id=0,

        @severity=21,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_22',

        @message_id=0,

        @severity=22,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_23',

        @message_id=0,

        @severity=23,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_24',

        @message_id=0,

        @severity=24,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_25',

        @message_id=0,

        @severity=25,

        @enabled=1,

        @delay_between_responses=1800,

        @include_event_description_in=1,

        @category_name=N'[Uncategorized]',

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

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

USE [msdb]

GO

/****** Object:  Operator [Terry Mao]    Script Date: 01/19/2011 11:37:34 ******/

EXEC msdb.dbo.sp_add_operator @name=N'Terry Mao',

        @enabled=1,

        @weekday_pager_start_time=90000,

        @weekday_pager_end_time=180000,

        @saturday_pager_start_time=90000,

        @saturday_pager_end_time=180000,

        @sunday_pager_start_time=90000,

        @sunday_pager_end_time=180000,

        @pager_days=0,

        @email_address=N'10000@qq.com',

        @category_name=N'[Uncategorized]'

GO

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas,”Courier New”,courier,monospace; background-color: rgb(255, 255, 255); }.csharpcode pre { margin: 0em; }.csharpcode .rem { color: rgb(0, 128, 0); }.csharpcode .kwrd { color: rgb(0, 0, 255); }.csharpcode .str { color: rgb(0, 96, 128); }.csharpcode .op { color: rgb(0, 0, 192); }.csharpcode .preproc { color: rgb(204, 102, 51); }.csharpcode .asp { background-color: rgb(255, 255, 0); }.csharpcode .html { color: rgb(128, 0, 0); }.csharpcode .attr { color: rgb(255, 0, 0); }.csharpcode .alt { background-color: rgb(244, 244, 244); width: 100%; margin: 0em; }.csharpcode .lnum { color: rgb(96, 96, 96); }我添加了一个 “Terry Mao” 的操作人员,对应邮件地址为10000的邮件地址，然后我们可能需要为所有的事件添加订阅人员，也就是Terry Mao。

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

SELECT * FROM msdb.dbo.sysalerts;

CREATE PROC dbo.sp_add_all_notification
    @Operator sysname
AS
BEGIN
DECLARE @Name sysname,@OperatorID int;
SELECT @OperatorID = id FROM msdb.dbo.sysoperators WHERE name = @Operator;
IF @OperatorID IS NULL
    RETURN -1;
    
DECLARE Cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT [name] FROM

msdb.dbo.sysalerts alters WHERE NOT EXISTS

(SELECT 1 FROM msdb.dbo.sysnotifications notify

WHERE notify.operator_id = @OperatorID AND notify.alert_id = alters.id);
OPEN Cur;

FETCH NEXT FROM Cur INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_add_notification @alert_name = @Name,

@operator_name = @Operator,

@notification_method = 1;
    FETCH NEXT FROM Cur INTO @Name;
END

CLOSE Cur;
DEALLOCATE Cur;
RETURN 0;
END

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas,”Courier New”,courier,monospace; background-color: rgb(255, 255, 255); }.csharpcode pre { margin: 0em; }.csharpcode .rem { color: rgb(0, 128, 0); }.csharpcode .kwrd { color: rgb(0, 0, 255); }.csharpcode .str { color: rgb(0, 96, 128); }.csharpcode .op { color: rgb(0, 0, 192); }.csharpcode .preproc { color: rgb(204, 102, 51); }.csharpcode .asp { background-color: rgb(255, 255, 0); }.csharpcode .html { color: rgb(128, 0, 0); }.csharpcode .attr { color: rgb(255, 0, 0); }.csharpcode .alt { background-color: rgb(244, 244, 244); width: 100%; margin: 0em; }.csharpcode .lnum { color: rgb(96, 96, 96); }这样就会把所有的事件全部给监控起来，其实我就解决了过滤的监控列表，但是我们可能会对运行中的SQL Server 比如长时间执行的语句，存储过程进行监控来调优。比如，我们给另外一家公司做DBA顾问，就立马需要获取有问题的查询，这个我会在接下来的文章详细说明。其中包括Profile的监控，SQL Server WMI的监控等。

[转载]SQL Server 监控系列

相关推荐

热门标签

分类

链接表

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏