--测试复制邮件告警
USE [sss]
GO
--建立测试表 发布表一定要有主键
CREATE TABLE Repl_Test
(
ID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY ,
TestNAME VARCHAR(100) NULL ,
CreatDate DATETIME NULL
)
/*********************************************************************/
--在发布库和订阅库建立一个同名的登录用户,这两个登录用户都对发布库有访问权限
/*********************************************************************/
--设置指定数据库的复制选项
--存储过程说明http://msdn.microsoft.com/zh-tw/library/ms188769.aspx
use [sss]
exec sp_replicationdboption
@dbname = N'sss',
@optname = N'publish',
@value = N'true'
GO
/*********************************************************************/
-- 添加事务发布
--存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_4s32.htm
use [sss]
exec sp_addpublication
@publication = N'testpub-sss',
@description = N'来自发布服务器“JOE”的数据库“sss”的事务发布。',
@sync_method = N'concurrent',
@retention = 0, --订阅是否过期,0为永不过期
@allow_push = N'true', --推送订阅
@allow_pull = N'true', --请求订阅为
@allow_anonymous = N'false', --false则表示不允许在该发布上使用匿名订阅
@repl_freq = N'continuous', --是复制频率的类型。默认设置为 continuous。如果是 continuous,则表示发布服务器提供所有基于日志的事务输出。如果是 Snapshot,则表示发布服务器只生成已调度同步事件
@status = N'active', --指定发布数据是否可用
@independent_agent = N'true', --指定是否有用于发布的单独的分发代理程序
@immediate_sync = N'false', --指定是否每次快照代理程序运行时都创建发布的同步文件
@replicate_ddl = 1, --复制DDL语句
@allow_initialize_from_backup = N'true' --是否允许备份初始化
GO
/*********************************************************************/
--添加快照代理
--存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_7ecj.htm
exec sp_addpublication_snapshot
@publication = N'testpub-sss',
@frequency_type = 1,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1
/*********************************************************************/
-- 添加发布项目
--存储过程说明http://www.yesky.com/imagesnew/software/tsql/ts_sp_repl_27s5.htm
use [sss]
exec sp_addarticle
@publication = N'testpub-sss',
@article = N'Repl_Test',
@source_owner = N'dbo',
@source_object = N'Repl_Test', --要发布的表
@type = N'logbased',
@pre_creation_cmd = N'drop', --当应用该项目的快照时,指定系统在订阅服务器上检测到同名的现有对象时所应采取的操作
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual', --自增列范围管理选项,manual为手动管理
@destination_table = N'Repl_Test', --是目的(订阅)表
@destination_owner = N'dbo',
@ins_cmd = N'CALL sp_MSins_dboRepl_Test', --是复制项目的插入时使用的复制命令类型
@del_cmd = N'CALL sp_MSdel_dboRepl_Test', --是复制项目的删除时使用的复制命令类型
@upd_cmd = N'SCALL sp_MSupd_dboRepl_Test' --是复制项目的更新时使用的复制命令类型
GO
/*********************************************************************/
--完整备份发布库
BACKUP DATABASE [sss] TO DISK ='C:\SSS_FULLBACKUP2014-4-13.BAK'
/*********************************************************************/
--在订阅库上还原数据库
USE [master]
RESTORE DATABASE [sss] FROM DISK = N'D:\sss_fullbackup2014-4-6.bak' WITH FILE = 1,
MOVE N'sss' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss.mdf',
MOVE N'sss_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\sss_log.ldf',
NOUNLOAD, REPLACE, STATS = 5
GO
/*********************************************************************/
--在发布库新建订阅 使用推送订阅
use [sss]
exec sp_addsubscription
@publication = N'testpub-sss',
@subscriber = N'JOE\SQL2012',
@destination_db = N'sss',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype='disk',
@backupdevicename='C:\SSS_FULLBACKUP2014-4-13.bak'--最后一次备份的备份文件(发布服务器上的存放位置)
/*********************************************************************/
--添加分发代理
exec sp_addpushsubscription_agent
@publication = N'testpub-sss',
@subscriber = N'JOE\SQL2012',
@subscriber_db = N'sss',
@job_login = null,
@job_password = null,
@subscriber_security_mode = 0,
@subscriber_login = N'ReplicationUser',
@subscriber_password = N'ReplicationForUser',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20140408,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'
GO
/*********************************************************************/
--脚本创建数据库邮件
--开启数据库邮件
EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'database mail xps',1
RECONFIGURE WITH OVERRIDE
/*********************************************************************/
--创建邮件帐户信息
EXEC msdb..sysmail_add_account_sp
@ACCOUNT_NAME ='ReplicationErrorMailLog',--邮件帐户名称
@EMAIL_ADDRESS ='linyonghua.hi@163.com',--发件人邮件地址
@DISPLAY_NAME ='系统管理员',--发件人姓名
@REPLYTO_ADDRESS =NULL,
@DESCRIPTION = NULL,
@MAILSERVER_NAME = 'SMTP.163.COM',--邮件服务器地址
@MAILSERVER_TYPE = 'SMTP',--邮件协议
@PORT =25,--邮件服务器端口
@USERNAME = 'linyonghua.hi@163.com',--用户名
@PASSWORD = 'xxx',--密码
@USE_DEFAULT_CREDENTIALS =0,
@ENABLE_SSL =0,
@ACCOUNT_ID = NULL
GO
/*********************************************************************/
--数据库配置文件
IF EXISTS(SELECT name FROM msdb..sysmail_profile WHERE name=N'ReplicationErrorProfileLog')
BEGIN
EXEC msdb..sysmail_delete_profile_sp
@profile_name='ReplicationErrorProfileLog'
END
EXEC msdb..sysmail_add_profile_sp
@profile_name = 'ReplicationErrorProfileLog',--profile名称
@description = '数据库邮件配置文件',--profile描述
@profile_id = null
GO
/*********************************************************************/
--用户和邮件配置文件相关联
EXEC msdb..sysmail_add_profileaccount_sp
@profile_name = 'ReplicationErrorProfileLog',--profile名称
@account_name = 'ReplicationErrorMailLog',--account名称
@sequence_number = 1--account 在profile 中顺序
GO
/*********************************************************************/
--发送简单文本的邮件
/*********************************************************************/
--创建链接服务器
--要开启分发服务器上的Distributed Transaction Coordinator(MSDTC服务)
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'JOE_DIST',
@srvproduct=N'sqlserver',
@provider=N'SQLOLEDB',
@datasrc=N'JOE'
GO
EXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc out', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'JOE_DIST',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'sa', --要求是对distribution有db_owner权限的 实际应用时最好不要用sa
@rmtpassword = N'testxxx'
GO
/*********************************************************************/
----发送邮件 ,这个步骤只是测试,检查编写的动态SQL是否正确
USE [distribution]
GO
DECLARE @SQL NVARCHAR(MAX)
DECLARE @replcountersSQL NVARCHAR(MAX)
DECLARE @replmonitorsubscriptionpendingcmdsSQL NVARCHAR(MAX)
CREATE TABLE #replcounters
(
[database] NVARCHAR(200) ,
[replicated_transactions] BIGINT ,
[replication_rate_trans_sec] DECIMAL(18, 2) ,
[replication_latency] DECIMAL(18, 2) ,
[replbeginlsn] BINARY ,
[replnextlsn] BINARY
)
CREATE TABLE #replmonitorsubscriptionpendingcmds
(
[pendingcmdcount] BIGINT ,
[estimatedprocesstime] BIGINT
)
INSERT [#replmonitorsubscriptionpendingcmds]
EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds 'JOE',
'SSS', 'testpub-sss', 'JOE\SQL2012', 'SSS', 0
INSERT [#replcounters] EXEC [distribution].[dbo].sp_replcounters
SELECT * FROM [#replcounters]
SELECT * FROM [#replmonitorsubscriptionpendingcmds]
SET @replcountersSQL = N'<H3>数据库滞后时间、吞吐量和事务计数的复制统计信息</H3>'+
'<table border="1">' +
N'<tr><th>[database]</th>
<th>[replicated_transactions]</th>
<th>[replication_rate_trans_sec]</th>
<th>[replication_latency]</th>
<th>[replbeginlsn]</th>
<th>[replnextlsn]</th>
</tr>'
+ CAST(( SELECT
[database] AS 'td' , '',
[replicated_transactions] AS 'td','',
[replication_rate_trans_sec] AS 'td','',
[replication_latency] AS 'td','',
CAST([replbeginlsn] AS INT) AS 'td','',
CAST([replnextlsn] AS INT) AS 'td'
FROM [#replcounters]
FOR
XML PATH('tr') ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N'</table>';
SET @replmonitorsubscriptionpendingcmdsSQL = N'<H3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</H3>'+
'<table border="1">' +
N'<tr><th>[pendingcmdcount]</th>
<th>[estimatedprocesstime]</th>
</tr>'
+ CAST(( SELECT
[pendingcmdcount] AS 'td' , '',
[estimatedprocesstime] AS 'td',''
FROM [#replmonitorsubscriptionpendingcmds]
FOR
XML PATH('tr') ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N'</table>';
SET @SQL=@replcountersSQL+'</br>'+@replmonitorsubscriptionpendingcmdsSQL
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'ReplicationErrorProfileLog' ,
@recipients = 'linyonghua.hi@163.com', -- varchar(max) --收件人
@subject = N'数据库复制的相关信息', -- nvarchar(255) 标题
@body_format = 'HTML', -- varchar(20) 正文格式可选值:text html
@body = @SQL
DROP TABLE [#replcounters]
DROP TABLE [#replmonitorsubscriptionpendingcmds]
/*********************************************************************/
--创建作业 作业命名规则:数据库名_ReplicationInfo
DECLARE @job_name SYSNAME
SET @job_name='SSS_ReplicationInfo'
EXEC msdb.dbo.sp_add_job @job_name=@job_name,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'发送复制数据库情况作业',
@category_name=N'REPL-Checkup',
@owner_login_name=N'sa'
/*********************************************************************/
--添加作业步骤
DECLARE @job_name SYSNAME
DECLARE @DBNAME NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
SET @DBNAME='distribution'
SET @job_name='SSS_ReplicationInfo'
SET @SQL = N'USE [distribution]
GO
DECLARE @SQL NVARCHAR(MAX)
DECLARE @replcountersSQL NVARCHAR(MAX)
DECLARE @replmonitorsubscriptionpendingcmdsSQL NVARCHAR(MAX)
CREATE TABLE #replcounters
(
[database] NVARCHAR(200) ,
[replicated_transactions] BIGINT ,
[replication_rate_trans_sec] DECIMAL(18, 2) ,
[replication_latency] DECIMAL(18, 2) ,
[replbeginlsn] BINARY ,
[replnextlsn] BINARY
)
CREATE TABLE #replmonitorsubscriptionpendingcmds
(
[pendingcmdcount] BIGINT ,
[estimatedprocesstime] BIGINT
)
INSERT [#replmonitorsubscriptionpendingcmds]
EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds ''JOE'',
''SSS'', ''testpub-sss'', ''JOE\SQL2012'', ''SSS'', 0
INSERT [#replcounters] EXEC [distribution].[dbo].sp_replcounters
SELECT * FROM [#replcounters]
SELECT * FROM [#replmonitorsubscriptionpendingcmds]
SET @replcountersSQL = N''<H3>数据库滞后时间、吞吐量和事务计数的复制统计信息</H3>''+
''<table border="1">'' +
N''<tr><th>[database]</th>
<th>[replicated_transactions]</th>
<th>[replication_rate_trans_sec]</th>
<th>[replication_latency]</th>
<th>[replbeginlsn]</th>
<th>[replnextlsn]</th>
</tr>''
+ CAST(( SELECT
[database] AS ''td'' , '''',
[replicated_transactions] AS ''td'','''',
[replication_rate_trans_sec] AS ''td'','''',
[replication_latency] AS ''td'','''',
CAST([replbeginlsn] AS INT) AS ''td'','''',
CAST([replnextlsn] AS INT) AS ''td''
FROM [#replcounters]
FOR
XML PATH(''tr'') ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N''</table>'';
SET @replmonitorsubscriptionpendingcmdsSQL = N''<H3>事务发布的订阅的等待命令数以及处理这些命令的粗略估计时间</H3>''+
''<table border="1">'' +
N''<tr><th>[pendingcmdcount]</th>
<th>[estimatedprocesstime]</th>
</tr>''
+ CAST(( SELECT
[pendingcmdcount] AS ''td'' , '''',
[estimatedprocesstime] AS ''td'',''''
FROM [#replmonitorsubscriptionpendingcmds]
FOR
XML PATH(''tr'') ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N''</table>'';
SET @SQL=@replcountersSQL+''</br>''+@replmonitorsubscriptionpendingcmdsSQL
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = ''ReplicationErrorProfileLog'' ,
@recipients = ''linyonghua.hi@163.com'', -- varchar(max) --收件人
@subject = N''数据库复制的相关信息'', -- nvarchar(255) 标题
@body_format = ''HTML'', -- varchar(20) 正文格式可选值:text html
@body = @SQL
DROP TABLE [#replcounters]
DROP TABLE [#replmonitorsubscriptionpendingcmds]
'
EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
@step_name = N'SendMail',
@step_id = 1,
@cmdexec_success_code = 0,
@on_success_action = 3,
@on_success_step_id = 0,
@on_fail_action = 2,
@on_fail_step_id = 0,
@retry_attempts = 0,
@retry_interval = 0,
@os_run_priority = 0,
@subsystem = N'TSQL',
@command = @SQL,
@database_name = @DBNAME,
@flags = 0
/*********************************************************************/
--创建作业的调度计划 每天每隔6个小时查看一次
DECLARE @job_name SYSNAME
DECLARE @Time VARCHAR(100)
DECLARE @Date DATETIME
--修改作业的执行时间
SET @Date = '2014-01-08 00:20:00.000'
SET @Time = REPLACE(CONVERT(VARCHAR, @Date, 8 ),':','')
SET @job_name='SSS_ReplicationInfo'
--修改作业的执行时间
SET @Time = REPLACE(CONVERT(VARCHAR, @Date, 8 ),':','')
EXEC msdb.dbo.sp_add_jobschedule @job_name = @job_name, @name=N'Plan',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20140414,
@active_end_date=99991231,
@active_start_time=@Time,
@active_end_time=235959
EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = N'(local)'
/*********************************************************************/
--创建对于[distribution].[dbo].[MSrepl_errors]表的insert触发器,当有错误的时候就发邮件
USE [distribution]
GO
IF ( OBJECT_ID('tgr_MSrepl_errors', 'tr') IS NOT NULL )
DROP TRIGGER tgr_MSrepl_errors
GO
CREATE TRIGGER tgr_MSrepl_errors ON [distribution].[dbo].[MSrepl_errors]
FOR INSERT --插入触发
AS
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'<H3>数据库复制出错信息</H3>' + '<table border="1">'
+ N'<tr><th>[xact_seqno]</th>
<th>[id]</th>
<th>[time]</th>
<th>[source_name]</th>
<th>[error_code]</th>
<th>[session_id]</th>
</tr>' + CAST(( SELECT e.[xact_seqno] AS 'td' ,
'' ,
e.[id] AS 'td' ,
'' ,
e.[time] AS 'td' ,
'' ,
e.[source_name] AS 'td' ,
'' ,
CAST(e.[error_code] AS NVARCHAR(200)) AS 'td' ,
'' ,
CAST(e.[session_id] AS NVARCHAR(200)) AS 'td' ,
''
FROM dbo.MSdistribution_history h
JOIN inserted e ON h.error_id = e.id
WHERE comments NOT LIKE '%transaction%'--失败的代理
ORDER BY id DESC
FOR
XML PATH('tr') ,
ELEMENTS-- TYPE
) AS NVARCHAR(MAX)) + N'</table>';
EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'ReplicationErrorProfileLog',
@recipients = 'linyonghua.hi@163.com', -- varchar(max) --收件人
@subject = N'数据库复制出错信息', -- nvarchar(255) 标题
@body_format = 'HTML', -- varchar(20) 正文格式可选值:text html
@body = @SQL
GO
(1)对[distribution].[dbo].[MSrepl_errors]表创建了insert触发器,当有错误的时候,SQLSERVER会向这个表插入错误记录