Sai~

监控前言

    上一节我们提到了MSSQL的基于SQL Event的监控，但是有些时候我们需要更加详细、适用于调优排错的监控。SQL Server内部运行的可见性是的查询调整、优化和综合排查成为可能！这一节主要和大家说说SQL Server跟踪（SQL Server Profile）的一些监控方式和途径。

使用场景

    记得某次给一家公司调优的时候，负责人发给我一堆业务的T-SQL脚本，我面对海量脚本还是从容，虽然不了解内部复杂的业务，但是我们得专注问题的关键 “慢”，我们根据查询的“慢”把他们筛选出来，一一调式优化，不就迅速解决问题吗？三天后，负责人含泪握着我的手，哥们辛苦了，查询响应得到了质的改善。

跟踪提供者

    SQL Server 为我们两者提供跟踪的方式：一种是一个物理文件（可保存在本机或者UNC网络路径），一种是行集。对于后者大家应该比较熟悉

这个工具在 SSMS 的 工具 –> SQL Profile

详细的我暂时不介绍，先说说两者的区别和类同点 DIFFAndSame(行集,文件提供者)。

• 两者都是用类似Buffer来保存当前的事件数据，很明显是为了减少IO的压力，这样可以不阻塞和尽量不遗漏 事件数据，当Buffer 到达一定量时候可能才会Flush到磁盘或者发送到网络的终端（客户端）显示监控行集。
• 物理文件保存监控结果的方式的重要保证是不能遗漏任何事件，一旦IO降速的时候，可能会影响到整个T-SQL的执行情况。

    SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ('SQLTRACE_LOCK','IO_COMPLETION');

    我使用这个语句来监控TRACE 和IO 完成对我当前机器的影响，我的某个客户的IO情况：                       

因为我进行了大量的过滤，因此这个值还是能够接受的，影响不是特别大。

• 行结果集的方式，其实也是我们最熟悉的，就是使用SQL Server Profile监控GUI 直接展现给我们看到的。但是，我是非常不建议使用的，首先如果Buffer满了，它有一定的延迟，可能会抛弃事件已清空缓存区继续接受事件，而事件没有发送到Client，也没有写到物理文件，自然就丢失了。比如，SQL Server Profile 在DB服务器进行监控，因为高负载的机器再用来展示，很有可能就会丢失事件，另外物理文件方式，其实是接受一个足够大的Buffer，进行的大块写操作，性能是优于行集的。

（行集）

保密性原则

    SQL Server的安全特性会自动过滤 包含隐私的数据，比如密码。我在我的SSMS中执行了如下的语句：

EXEC sp_password 'pp','pp1','sa';

这是修改sa帐号密码的系统sp，我打开了SQL Server Profile –> 选择了T-SQL 监控模版 

然后执行上面的存储过程，监控结果：

监控结果：--*sp_password----------------------------

SQL Server Profile

    使用SQL Server Profile GUI工具还是很多优势，首先是减少了我们监控的复杂性，可以快速的建立监控，在跟踪属性中，可以可以选择MSSQL为我们提供的模版，包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分别监控当前DB运行的所有查询，所有查询的耗时、所有的锁定状态。

在跟踪属性 –> 选择事件选择 我们可以选择自己需要的事件，所有的事件在MSDN 都有定义->单击列筛选器 可以自定义过滤，排序噪点干扰因素

（我随便选择了一个耗时 = 500 微妙的过滤条件）

其他的模版大家可以自己看看MSDN 手册，自己尝试一下：SQL Server 2008 R2 本机  MSDN

服务器端跟踪和物理方式收集

    SQL Server Profile 只是对一些存储过程的封装，我更倾向于，自己定义常用的脚本，将监控结果保存在本机，用来大量的分析和存档。

当然涉及4个存储过程，虽然设置过滤的脚本非常麻烦，但是SQL Server Profile 可以利用 文件->导出 可以导出监控脚本意味着，我们不需要编写复杂的T-SQL 脚本，不过还是建议大家熟悉这几个存储过程：

• sp_trace_create 定义跟踪 ，创建的跟踪会在sys.traces查询的到。
• s_trace_setevent 设置监控事件
• sp_trace_setfilter 设置过滤
• sp_trace_setstatus 设置跟踪的状态  常用的是  sp_trace_setstatus @traceid,0 停止功能 、sp_trace_setstatus @traceid,2 移除跟踪，这将导致sys.traces最终查询不到该跟踪

其实整个跟踪还是比较简单的。我这里有一个常用的脚本：

用来 监控超过指定秒数 和 数据库 的 批处理和存储过程 语句（超过5MB的文件，会执行ROLLOVER，根据文件名在后面添加类似_1,_2.trc的跟踪结果）：

CREATE PROC [dbo].[sp_trace_sql_durtion]

    @DatabaseName nvarchar(128),

    @Seconds bigint,

    @FilePath nvarchar(260)

AS

BEGIN

DECLARE @rc int,@TraceID int,@MaxFileSize bigint;

SET @MaxFileSize = 5;

EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL;

IF @rc != 0 

    RETURN;

DECLARE @On bit;

SET @On = 1;

EXEC sp_trace_setevent @TraceID,10,35,@On;

EXEC sp_trace_setevent @TraceID,10,1,@On;

EXEC sp_trace_setevent @TraceID,10,13,@On;

EXEC sp_trace_setevent @TraceID,41,35,@On;

EXEC sp_trace_setevent @TraceID,41,1,@On;

EXEC sp_trace_setevent @TraceID,41,13,@On;

SET @Seconds = @Seconds * 1000000;

EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds;

IF @DatabaseName IS NOT NULL

    EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName

EXEC sp_trace_setstatus @TraceID,1

SELECT TraceID = @TraceID;

END

参数非常的明了，数据库名称、执行事件超过多少秒、保存的路径。

当我们运行这个脚本一段事件以后，可以快速的发现大量耗时的T-SQL，我们可以通过

SELECT * FROM fn_trace_gettable(N'监控文件路径',1);

来查看行方式的结果。

同样的富有创造力的读者可以自己创建监控锁定，监控死锁等方式保存文件，但是我的建议是尽可能的减少噪音，也就是说我们要达到什么目地就

建立什么功能，这样才能将大问题细化解决。

在《Microsfot SQL Server 2005 技术内幕： T-SQL 程序设计》 中有一个正则，用来将类似的语句全部组合成，只有参数形式替换具体值

的SQL CLR，但是我认为那个正则还有bug，等我空了给大家写一个，自己也能使用的更完善。

监控异常

    在上个系列中，讲述了具体的SQL Event抓去的异常，可以及时通知，但是具体的异常信息，并不是特别详细。因此我们可以选择事件中的

Error来添加有关T-SQL批处理和SP的所有异常，用于分析，这个跟踪非常有利于我们监控一些异常情况！！！

我创建了一个跟踪的脚本，和上面的跟踪事件的脚本一样，超过5MB RollOver。

    我们要定期的执行这个跟踪，虽然不建议长期开启，但是定期监控处理异常是有利我们系统更加长时间运作的。

CREATE PROC [dbo].[sp_trace_sql_exception]

    @FilePath nvarchar(260)

AS

DECLARE @rc int,@TraceID int,@Maxfilesize bigint

SET @maxfilesize = 5 

EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL 

IF (@rc != 0) 

    RETURN;

DECLARE @on bit

SET @on = 1

EXEC sp_trace_setevent @TraceID, 33, 1, @on

EXEC sp_trace_setevent @TraceID, 33, 14, @on

EXEC sp_trace_setevent @TraceID, 33, 51, @on

EXEC sp_trace_setevent @TraceID, 33, 12, @on

EXEC sp_trace_setevent @TraceID, 11, 2, @on

EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setevent @TraceID, 11, 51, @on

EXEC sp_trace_setevent @TraceID, 11, 12, @on

EXEC sp_trace_setevent @TraceID, 13, 1, @on

EXEC sp_trace_setevent @TraceID, 13, 14, @on

EXEC sp_trace_setevent @TraceID, 13, 51, @on

EXEC sp_trace_setevent @TraceID, 13, 12, @on

DECLARE @intfilter int,@bigintfilter bigint;

EXEC sp_trace_setstatus @TraceID, 1

SELECT TraceID=@TraceID

GOTO finish

ERROR: 

SELECT ErrorCode=@rc

FINISH: 

定期执行吧，同志们，找异常。。。

默认跟踪和黑盒跟踪

    在sys.traces中的TraceID = 1的跟踪是SQL Server 默认跟踪，这个跟踪比较轻量级，一般监控服务器的启用停止，对象的创建和删除，日志和数据文件自动增长以及其他数据库的变化。（监控那些没事删错了表的人，是最好的，当然前提不要都使用一个帐号！）

可以通过

EXEC sp_configure 'default trace enabled',0;

RECONFIGURE WITH OVERRIDE;

来关闭默认跟踪。

黑盒跟踪，就是可以帮助我们诊断数据库没事自个奔了的异常，在MSDN 搜索sp_create_trace的时候应该也发现了

的选项，那么我们也能创建一个类似的存储过程来快速的创建黑盒跟踪，帮助我们诊断一些异常！

CREATE PROCEDURE sp_trace_blackbox
    @FilePath nvarchar(260)
AS
BEGIN
    DECLARE @TraceID int,@MaxFileSize bigint
    SET @MaxFileSize = 25;
    EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize
    EXEC sp_trace_setstatus @TraceID,1;

END

我这里提供@FilePath = NULL参数，这个默认就保存在SQL Server的数据文件夹中。

结尾

  这里详细的描述了SQL Server Trace 的各种功能特性，有兴趣的朋友可以深入到MSDN研究监控，我这是也只是一笔带过，也参考了MSDN 和《Microsoft SQL Server 2005调优》那本书，下面的监控可能和大家讲述 DDL触发器监控，C2审核以及SQL Server的事件通知（涉及的Service Broker我会开一个系列和大家详细说说Service Broker），最后的结束可能就是说说2008的数据收集监控，大家期待吧。休息~

    作为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

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

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

SELECT * FROM msdb.dbo.sysalerts;

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

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

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

    前端时间给别人做迁移数据库时候,遇到一些问题.大致是,如果备份的数据库存在EXTERNAL_ACCESS 和UNSAFE的程序集,那么在还原的时候程序集会出现一些奇怪的错误:

消息 10314，级别 16，状态 11，第 1 行
在尝试加载程序集 ID 65536 时 Microsoft .NET Framework 出错。服务器可能资源不足，或者不信任该程序集，因为它的 PERMISSION_SET 设置为 EXTERNAL_ACCESS 或 UNSAFE。请重新运行查询，或检查有关的文档了解如何解决程序集信任问题。有关此错误的详细信息:
System.IO.FileLoadException: 未能加载文件或程序集“testclr, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null”或它的某一个依赖项。异常来自 HRESULT:0x80FC80F1
System.IO.FileLoadException:
   在 System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   在 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   在 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   在 System.Reflection.Assembly.Load(String assemblyString)

至于产生的原因大概是,在备份数据库的时候,在机器A,那么数据库的拥有者是A\Administrator(如果用windows登录创建),那么但是我们还原到服务器B,那么拥有者可能是B\Administrator,那么SQL CLR的安全性会认为该程序集不可靠.

    例如:

我首先创建一个简单的SQL CLR 存储过程:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void Test()

    {

        // 在此处放置代码

    }

};

编译创建程序集.

在机器A上的数据库上执行:

CREATE DATABASE test;

USE test;

ALTER DATABASE test SET TRUSTWORTHY ON;

CREATE ASSEMBLY [TestCLR] FROM 'E:\Documents\Visual Studio 2010\Projects\TestCLR\TestCLR\bin\Release\TestCLR.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

--SAFE;

CREATE PROC dbo.usp_test

AS

EXTERNAL NAME [TestCLR].StoredProcedures.Test;

EXEC dbo.usp_test;

USE master;

BACKUP DATABASE test TO DISK = 'c:\test.bak' WITH FORMAT;

将c:\test.bak copy 到机器B上,然后执行:

USE [master];

--还原数据库

RESTORE DATABASE test FROM DISK = 'c:\test.bak' WITH RECOVERY,

MOVE 'test' TO 'E:\data\test.mdf',

MOVE 'test_log' TO 'E:\data\test.ldf',REPLACE;

--如果没有启用CLR,开启

EXEC sp_configure 'clr enabled',1

RECONFIGURE WITH OVERRIDE;

USE test;

--查看程序集,是存在的.

SELECT * FROM sys.assemblies;

SELECT * FROM sys.assembly_files;

--还原之后的数据库TRUSTWORTHY 都是OFF的,需要重新设置

ALTER DATABASE test SET TRUSTWORTHY ON;

USE test;

--执行存储过程

EXEC dbo.usp_test;

但是一执行就报错了.

解决方案:

在还原数据库之后,我们可以将数据库的OWNER设置成SA.

exec sp_changedbowner 'sa'

再调用存储过程就是成功的.

可以查看:KB http://support.microsoft.com/kb/918040

后来经过一些整理,发现当SQL CLR 存在EXTERNAL_ACCESS或者是UNSAFE的程序集的时候,SQL Server会检查DBO的SID在sys.databases 和sys.server_principals是否一致.

因此我们可能未必一定要修改成sa 的,只要所有者的SID在sys.databases和sys.server_principals 是一致的,就不出问题.

我们在SSMS里面右键数据库属性->找到文件选项卡->发现在所有者(是空的,还原以后原来的SID,数据库所有者在当前的sys.server_principals不匹配的),我们可以在 [...] 里面选择一个,具有创建CREATE ASSEMLY 权限的所有者就好,我选择了B\Administrator,然后测试 CLR 存储过程,没问题,

引深:

在SQL Server 复制里面也存在类似的问题,就是我们做 "对等复制" 的时候,会出现DBO不存在,以及sp_replcmd 不存在类似的错误.其实也是因为对等复制初始化订阅是通过 RESTORE 来实现的,因此只要简单的修改数据库所有者 就好了....那么对等复制的问题也就解决了!!

之前看到博友们一些文章解决asp.net的下载文件名的乱码,但是貌似针对其他浏览器比如Firefox的中文还是有乱码,空格出现+号(这时候IE,Chrome没问题)

刚才试了试,用这个方法可以搞定了IE,Chrome,Firefox.其他没测试,先贴上代码:

代码

public class UrlEncoder 
    { 
        /// <summary> 
        /// Encodes non-US-ASCII characters in a string. 
        /// </summary> 
        /// <param name="s"></param> 
        /// <returns></returns> 
        public static string ToHexString(string s) 
        { 
            char[] chars = s.ToCharArray(); 
            StringBuilder builder = new StringBuilder(); 
            for (int index = 0; index < chars.Length; index++) 
            { 
                bool needToEncode = NeedToEncode(chars[index]); 
                if (needToEncode) 
                { 
                    string encodedString = ToHexString(chars[index]); 
                    builder.Append(encodedString); 
                } 
                else 
                { 
                    builder.Append(chars[index]); 
                } 
            } 
            return builder.ToString(); 
        } 
        /// <summary> 
        /// Determines if the character needs to be encoded. 
        /// </summary> 
        /// <param name="chr"></param> 
        /// <returns></returns> 
        private static bool NeedToEncode(char chr) 
        { 
            string reservedChars = "$-_.+!*'(),@=&"; 
            if (chr > 127) 
                return true; 
            if (char.IsLetterOrDigit(chr) || reservedChars.IndexOf(chr) >= 0) 
                return false; 
            return true; 
        } 
        /// <summary> 
        /// Encodes a non-US-ASCII character. 
        /// </summary> 
        /// <param name="chr"></param> 
        /// <returns></returns> 
        private static string ToHexString(char chr) 
        { 
            UTF8Encoding utf8 = new UTF8Encoding(); 
            byte[] encodedBytes = utf8.GetBytes(chr.ToString()); 
            StringBuilder builder = new StringBuilder(); 
            for (int index = 0; index < encodedBytes.Length; index++) 
            { 
                builder.AppendFormat("%{0}", Convert.ToString(encodedBytes[index], 16)); 
            } 
            return builder.ToString(); 
        } 
    }

然后在下载下面中:

代码

response.ContentType = "application/octet-stream"; 
response.HeaderEncoding = Encoding.UTF8; 
string encodefileName = UrlEncoder.ToHexString(fileName); 
string userAgent = request.UserAgent.ToUpper(); 
string fileNameHead = null;
if (userAgent.IndexOf("FIREFOX") > 0) 
    fileNameHead = string.Format("attachment;filename*=utf8''{0}", encodefileName); 
else 
    fileNameHead = string.Format("attachment;filename={0}", encodefileName);
response.AddHeader("Content-Disposition", fileNameHead); 

就成了.

    本文主要和大家描述一下关于Powershell变量的详细内容,上回和大家聊到了Powershell的远程管理,有朋友反映Powershell的基础只是还不够了解,所以先谈谈在Powershell中的变量问题.

创建自己的变量

PS C:\Users\Terry> $a = 1.2  那么我们就创建了一个名字为a的变量,注意Powershell 中变量都用美元符号"$"做为开始.

PS C:\Users\Terry> $b = 2.3 我们再次创建了一个为b的变量,两个变量是可以直接进行运算的,例如对他们进行一个乘操作.

PS C:\Users\Terry> $a*$b
2.76

很明显的,我们也可以对变量直接进行变量区间的传递赋值:

PS C:\Users\Terry> $c=$a*$b
PS C:\Users\Terry> $c
2.76

当使用字符串变量的时候,这里有一些小的技巧,大家可以注意了:

PS C:\Users\Terry> "`$c=`$a*`$b equals $c"
$c=$a*$b equals 2.76

在字符串""包围的区间要引用变量,可以很简单的,直接"$变量" 来表达我们的变量值,当我们需要转移$或者双引号等特殊字符使用我们通常使用"`"(1旁边那个键).

接下来可以进行更复杂的运算,如果在字符串引用变量进行进一步运算:

比如我们想在字符串中直接显示变量a和变量b的乘积值

PS C:\Users\Terry> "$a*$b"
1.2*2.3

这样是不行的.

通常我们使用

PS C:\Users\Terry> "$($a*$b)"
2.76

来进行一个转换,同理的.

我创建一个数组,然后求他的数组长度

PS C:\Users\Terry> $arr=1,2,3,4
PS C:\Users\Terry> $arr
1
2
3
4
PS C:\Users\Terry> "`$arr array length equals $arr.Length (this is error)"
$arr array length equals 1 2 3 4.Length (this is error)
PS C:\Users\Terry> "`$arr array length equals $($arr.Length) (this is ok)"
$arr array length equals 4 (this is ok)

如果我们的变量名比较复杂,存在空格,或者特殊字符,一般我们直接使用是会出错的,Powershell默认会使用空格或者一些特殊字符作为分解符号(DELIMITER)

比如:

PS C:\Users\Terry> $a b=123
表达式或语句中出现意外标记“b=123”。
所在位置 行:1 字符: 9
+ $a b=123 <<<<
    + CategoryInfo          : ParserError: (b=123:String) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

这样创建一个$a b 的变量就有问题了.所以要使用{}来对变量名进行一个解析:

PS C:\Users\Terry> ${a b}=123
PS C:\Users\Terry> ${a b}
123
PS C:\Users\Terry> "${a b}"
123

变量返回值

当我们调用一些命令的时候,我们还可以把返回值直接赋予给变量.

比如:

PS C:\Users\Terry> $ret = ls
PS C:\Users\Terry> $ret

    目录: C:\Users\Terry

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        2010/11/15     10:43            CMB
d-r--         2010/11/4     19:18            Desktop
d----        2010/11/18     15:23            Tracing
-a---        2010/11/16     16:24      11544 a
-a---        2010/11/13     14:30      21466 function.ps1

PS C:\Users\Terry> $ret.GetType()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

我将枚举目录的结果直接返回给变量$ret,查看$ret的类型是Object[] 数组.

Powershell变量魅力

一键赋值能力:

PS C:\Users\Terry> $a=$b=$c=1
PS C:\Users\Terry> $a
1
PS C:\Users\Terry> $b
1
PS C:\Users\Terry> $c
1

变量交换能力:

PS C:\Users\Terry> $a=1;$b=2
PS C:\Users\Terry> $a,$b=$b,$a
PS C:\Users\Terry> "$a $b"
2 1

使用Powershell还可以非常方便的访问文件

比如我们在c:\根目录下创建一个1.txt 里面随便写入内容

我们可以直接使用

PS C:\Users\Terry> ${c:\1.txt}
123123123

PS C:\Users\Terry> $command = "`${$env:windir\windowsupdate.log}"
PS C:\Users\Terry> Invoke-Expression $command

来查看内部的内容,某些时候,我们可以用来查询windows日志:

这里有一个小技巧,我们直接使用环境变量$env:windir再在外部使用$来打开文件时候是不能正确解析的,他会被当作变量名的文件路径了

所以我们要把调用的命令解析成可执行的方式交给Invoke_Expression 来使用 .

维护Powershell变量

首先是查找变量

可以使用DIR/Get-ChildItem (是Alias对)

PS C:\Users\Terry> dir variable:

Name                           Value
----                           -----
$                              $a $b
?                              True
^                              $a $b
_
a                              2
a b                            123
args                           {}
arr                            {1, 2, 3, 4}
b                              1
c                              1

我们刚才创建的变量就可以看到了.

当然具体的帮助大家可以看看Get-Help Get-ChildItem –Full 看看如果进行-include和-exclude操作.当我们要查找我们的a变量的时候,通常会这样搜索:

存在字符串1的数组行:

PS C:\Users\Terry> dir variable:|Out-String -Stream|Select-String "1"

$                              1
a b                            123
arr                            {1, 2, 3, 4}
b                              1
c                              1
PID                            1396
PROFILE                        E:\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1
PSHOME                         C:\Windows\System32\WindowsPowerShell\v1.0
ReportErrorShowSource          1
text                           $a*$b={123*0.19}

当我们要验证某个变量是否存在的时候,可以使用Test-Path

PS C:\Users\Terry> Test-Path variable:\a
True

说明变量a是存在.

删除变量:

PS C:\Users\Terry> Remove-Variable -Name a
PS C:\Users\Terry> del variable:\b
PS C:\Users\Terry> $a
PS C:\Users\Terry> $b

使用Remove-Variable 或者是Del 来删除变量.

关于变量操作的 Ps cmd 大家可以直接使用来搜:

PS C:\Users\Terry> Get-Command -Noun Variable

CommandType     Name
-----------     ----
Cmdlet          Clear-Variable
Cmdlet          Get-Variable
Cmdlet          New-Variable
Cmdlet          Remove-Variable
Cmdlet          Set-Variable

在Powershell中还存在许多的windows环境变量,$env:*之类的

大家可以看看dir env:来看看系统变量.

最后 Powershell变量的作用域

Powershell 会存在一个作用域,也就是变量在那个作用域下可用.我们需要了解的有4个作用域:

global,private,local,script

我们先看看官方是怎么表述的:

Scope allocation
Description

$private:test = 1

变量只能在当前作用域,脚本,函数可见,不能传递给其他的作用域,同理也只能在当前作用域下读写.
$local:test = 1

变量在本地作用域下创建,这是创建变量的默认作用域,本地作用域变量能够在其他作用域下读取,但是不能通过其他作用域修改,只有本地作用域能够修改.
$script:test = 1

脚本作用域变量只在脚本中有效,他可以在脚本的任何作用域下可见.比如在一个脚本的函数下创建了一个变量,那么在脚本的外部的函数也是可以访问的到的.
$global:test = 1

变量任何作用域下可用,甚至是外部函数和脚本.
我们来做几个测试加强对这个概念的理解:

PS C:\Users\Terry> notepad test.ps1

我们先打开一个测试的脚本test.ps1在里面输入:

$windows = $env:windir
"Windows Folder: $windows"

保存关闭以后,执行

PS C:\Users\Terry> Set-ExecutionPolicy RemoteSigned

执行策略更改
执行策略可以防止您执行不信任的脚本。更改执行策略可能会使您面临 about_Execution_Policies
帮助主题中所述的安全风险。是否要更改执行策略?
[Y] 是(Y)  [N] 否(N)  [S] 挂起(S)  [?] 帮助 (默认值为“Y”): y

我们的Powershell默认是不允许执行没有签名的脚本,所以我们设置成RemoteSigned.允许本地未签名脚本.

PS C:\Users\Terry> $windows = "Hello"

PS C:\Users\Terry> & .\test.ps1
Windows Folder: C:\Windows
PS C:\Users\Terry> $windows
Hello

(小技巧,执行脚本的时候,要使用&或者. 路径的方式)

我们首先在当前作用域下创建 windows 变量,然后调用脚本,脚本复制为 c:\windows,执行完脚本以后,windows变量还是Hello

再来回顾一下我们当时的定义:

变量在本地作用域下创建,这是创建变量的默认作用域,本地作用域变量能够在其他作用域下读取,但是不能通过其他作用域修改,只有本地作用域能够修改.

我们修改test.ps1在顶部加上:$windows 增加一个打印,再次执行:

PS C:\Users\Terry> $windows = "Hello"

PS C:\Users\Terry> & .\test.ps1
Hello

Windows Folder: C:\Windows
PS C:\Users\Terry> $windows
Hello

也证实了,官方的定义.只能读取不能修改.

关于Global 没有太多想解释的..$global:test=1创建就好了(这里强调一个,所有变量都 只对当前Session有效,也就是一个Powershell进程)

那么我们来看看private变量

PS C:\Users\Terry> $private:a=1
PS C:\Users\Terry> $a
1
PS C:\Users\Terry> function test{$a}

PS C:\Users\Terry> test

没有任何输出,说明private 是不能跨域的

我们修改刚才的test.ps1更新内容为$a

PS C:\Users\Terry> $private:a = 1
PS C:\Users\Terry> notepad .\test.ps1
PS C:\Users\Terry> . .\test.ps1
1
PS C:\Users\Terry> & .\test.ps1

细心的读者可能发现了,使用&和.调用脚本是不同的.

&的话,是会重新开打脚本的Scope,而.只作为本地Scope 当前Scope来执行脚本的.

Script变量,大家可以自己测试啦,其实都很简单的.

Powershell强类型变量

Powershell是基于.NET 类型来使用的,.NET 我们知道是使用的强类型,意味着,我们在PS中可以使用同样强大的强类型变量

PS C:\Users\Terry> [DateTime]$d = "1"
无法将值“1”转换为类型“System.DateTime”。错误:“该字符串未被识别为有效的 DateTime。”
所在位置 行:1 字符: 13
+ [DateTime]$d <<<<  = "1"
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

PS C:\Users\Terry> [Int]$i = 1
PS C:\Users\Terry> $i="1"
PS C:\Users\Terry> $i ="a"
无法将值“a”转换为类型“System.Int32”。错误:“输入字符串的格式不正确。”
所在位置 行:1 字符: 3
+ $i <<<<  ="a"
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

使用强类型变量,可以做进一步执行前的类型确认.

只读或只写其他功能的变量大家可以参考这个连接了,这些功能一般相对来说用的比较少.

参考Link:http://powershell.com/cs/blogs/ebook/archive/2008/10/22/chapter-3-variables.aspx