日常监控部署

日常监控涉及的数据表、存储过程抽离到单独用户数据库，新环境中可以通过还原此库及相关作业即可快速完成部署~
1、获取原备份文件
从已部署的实例上备份数据库

--备份数据库
BACKUP DATABASE [DBA_Monitor] TO  DISK = N'D:\Perfmon\DBA_Monitor_F_20160308.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'DBA_Monitor-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
BACKUP DATABASE [msdb] TO  DISK = N'D:\Perfmon\msdb_F_20160308.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'msdb-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

2、还原数据库
还原DBA_Monitor库及msdb库

--监控使用的表、存储过程所在数据库
restore headeronly from DISK = N'F:\TroubleShooting\DBA_Monitor_F_20160308.bak' 
RESTORE DATABASE [DBA_Monitor] FROM  DISK='F:\TroubleShooting\DBA_Monitor_F_20160308.bak' 
WITH  FILE=1,  
MOVE 'DBA_Monitor' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\DBA_Monitor.mdf',  
MOVE 'DBA_Monitor_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\DBA_Monitor_log.ldf',  
NOUNLOAD,  REPLACE,  STATS=10
GO
--包含监控作业的msdb库还原到msdbnew
RESTORE DATABASE [msdbnew] FROM  DISK = N'F:\TroubleShooting\msdb_F_20160308.bak' 
WITH  FILE = 1,  
MOVE N'MSDBData' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\msdbnew.mdf',  
MOVE N'MSDBLog' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08R2\MSSQL\DATA\msdbnew_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

3、利用作业迁移的脚本将需要的作业迁移
在where条件增加筛选，迁移相关的作业。有时可能需修改作业步骤，点击编辑作业报错，从ICassFactory为CLSID为{AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2}的COM组件创建实例失败，原因是出现以下错误：c001f011.(Microsoft.Server.manageDTS)
参考链接修改

cd c:\windows\system32
regsvr32 "D:\Program Files\Microsoft SQL Server\100\DTS\Binn\dts.dll"

dts.dll要指定到SQL Server的安装路径，后期查看编辑作业是否还会报错。
也可以参考"编写作业脚本为"中的job存储过程，对作业进行修改

--修改作业步骤对应的数据库
SELECT * FROM msdb.dbo.sysjobs
WHERE NAME LIKE '%表大小%'
EXEC msdb.dbo.sp_update_jobstep @job_id = '2C4D4932-E019-43BE-8BDE-0A13B2CD158F',@step_id=1, @database_name=N'DBA_Monitor'
EXEC msdb.dbo.sp_update_jobstep @job_id = '2C4D4932-E019-43BE-8BDE-0A13B2CD158F',@step_id=2, @database_name=N'DBA_Monitor'

4、运行各迁移作业，检查运行是否正常
运行作业之前，可以将原数据清空

USE DBA_Monitor
GO
sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"

在运行迁移作业时，发现其中一作业出错，已以用户 NT AUTHORITY\SYSTEM 的身份执行。 无法解决 equal to 运算中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。 [SQLSTATE 42000] (错误 468).  该步骤失败。
检查对应的存储过程发现是在执行以下语句时报错

FROM #temp a
INNER JOIN cte b
ON a.jobname=b.jobname
AND a.step_id=b.step_id

#temp是通过create table创建的，没有特别设置。cte是从sysjobs、sysjobhistory获取的作业信息。以前也是按这样的步骤操作，并没有报排序规则的错误。
检查msdb、DBA_Monitor数据库排序，发现msdb对应SQL_Latin1_General_CP1_CI_AS，DBA_Monitor对应Chinese_PRC_CI_AS，实例级别对应Chinese_PRC_CI_AS排序规则。查看相关数据库服务器的排序规则都是Chinese_PRC_CI_AS，那自己本地的msdb排序规则怎么变成Latin1？后来想起有一次测试迁移作业，将msdb数据库用X\MSSQL\Binn\Templates\下的msdbdata、msdblog覆盖了！导致msdb库的排序规则与安装时不一致！
解决方案：1、修改数据库排序规则(无效)

USE [master]
GO
ALTER DATABASE [msdb] COLLATE Chinese_PRC_CI_AS
GO

无法对 数据库'msdb' 执行 更改，因为它是系统 数据库。
2、创建#temp表时指定jobname字段的排序规则(有效)

create table #JobRunLongtime
(
    --jobname varchar(128) not null,
    jobname varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS not null,

3、在关联字段后面加上排序规则(有效)

FROM #temp a
INNER JOIN cte b
ON a.jobname collate SQL_Latin1_General_CP1_CI_AS=b.jobname
AND a.step_id=b.step_id

字段step_id为int型，不需要指定为Latin1排序规则。按照方法2或方法3修改后，这个问题被解决，但是作业依旧报错，已以用户 NT AUTHORITY\SYSTEM 的身份执行。 未在此数据库中启用 Service Broker 消息传递功能。请使用 ALTER DATABASE 语句启用 Service Broker 消息传递功能。 [SQLSTATE 42000] (错误 14650).  该步骤失败。
根据提示可知是发送邮件时出错，可参考启用Service Broker；其他数据库邮件错误请参考代理系列中配置数据库邮件。