--测试复制邮件告警

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 ='hiAT163.com',--发件人邮件地址

      @DISPLAY_NAME ='系统管理员',--发件人姓名

      @REPLYTO_ADDRESS =NULL,

      @DESCRIPTION = NULL,

      @MAILSERVER_NAME = 'SMTP.163.COM',--邮件服务器地址

      @MAILSERVER_TYPE = 'SMTP',--邮件协议

      @PORT =25,--邮件服务器端口

      @USERNAME = 'hiAT163.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',     --★Do

@locallogin = NULL ,

@useself = N'False',

@rmtuser = N'sa', --要求是对distribution有db_owner权限的 实际应用时最好不要用sa    --★Do

@rmtpassword = N'testxxx'    --★Do

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(1000) ,

 [replnextlsn] BINARY(1000)

)

CREATE TABLE #replmonitorsubscriptionpendingcmds

(

[pendingcmdcount] BIGINT ,

[estimatedprocesstime] BIGINT

)

INSERT  [#replmonitorsubscriptionpendingcmds]

EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds 'JOE',   --★Do

'SSS', 'testpub-sss', 'JOE\SQL2012', 'SSS', 0   --★Do

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' ,    --★Do

@recipients = 'xxxx@163.com', -- varchar(max) --收件人    --★Do

@subject = N'数据库复制的相关信息', -- nvarchar(255) 标题    --★Do

@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'     --★Do

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'     --★Do

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(1000) ,

 [replnextlsn] BINARY(1000)

)

CREATE TABLE #replmonitorsubscriptionpendingcmds

(

[pendingcmdcount] BIGINT ,

[estimatedprocesstime] BIGINT

)

INSERT  [#replmonitorsubscriptionpendingcmds]

EXEC [JOE_DIST].distribution.dbo.SP_replmonitorsubscriptionpendingcmds ''JOE'',   --★Do

''SSS'', ''testpub-sss'', ''JOE\SQL2012'', ''SSS'', 0   --★Do

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 = ''xxx@163.com'', -- varchar(max) --收件人   --★Do

@subject = N''数据库复制的相关信息'', -- nvarchar(255) 标题    --★Do

@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'     --★Do

--修改作业的执行时间

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
begin

    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 = 'xxxx@163.com', -- varchar(max) --收件人

        @subject = N'数据库复制出错信息', -- nvarchar(255) 标题

        @body_format = 'HTML', -- varchar(20) 正文格式可选值：text html

        @body = @SQL
end

GO

USE [distribution]

GO

SELECT * FROM [distribution].[dbo].[MSrepl_errors] ORDER BY [time] 

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', 100000000