查看/修改分发复制代理的各个属性

最近给服务器分发复制系统做了一次检查,发现许多设置并没有遵循<Replication Agent Security Model>.

 

1 发布的检查的项目为:

发布类型:事物型

状态:active

立即初始化:false

允许匿名:false

独立的分发代理: 如果是表的发布,则为true,否则为false (表的订阅为pull类型; 其他类型<例如存储过程>的订阅为push类型,这类的订阅对时时行要求不高,可以使用share agent,并且对应的job运行频率也可以调低)

ftp地址

ftp端口

ftp登录

 

相关脚本,在发布服务器的发布库执行:

 

use   [ publicationDB ]
 
select  
name ,description , case   repl_freq
                         when   0   then   ' Transactional '
                         when   1   then   ' Snapshot '
                         else    ' Error '
                    end    as  repl_freq,
                    case   [ status ]  
                         when   0   then   ' Inactive '
                         when   1   then   ' Active '
                         else   ' Error '
                    end   as  status ,
                   independent_agent,immediate_sync ,allow_push ,allow_pull ,allow_anonymous ,snapshot_in_defaultfolder 
,ftp_address ,ftp_port ,ftp_subdirectory ,ftp_login 
From  syspublications

 

 

如果发现错误的设置,可以通过来sp_changepublication修改

 

exec  sp_changepublication  @publication   =   ' xx '  , @property   =   ' allow_anonymous '  ,  @value   =  false

 

--true表示匿名订阅,会导致分发库中的事物在@max_distretention到达之前无法被删除,这样分发库中的数据就会累积到@max_distretention

 

exec sp_changepublication @publication = 'publicationTest' , @property = 'immediate_sync' , @value = false

--true表示立即初始化,会导致每次运行快照代理是所有的article都被重新初始化. 并且也会有上述的匿名订阅的问题.

 

2  snapshot和log reader agent的一些属性,在发布服务器的发布库执行

exec  sp_helppublication_snapshot
exec  sp_helplogreader_agent

我对这两个存储过程的结果集进行了一些修改

if   not   exists ( select   1   from  sys.databases  where  name  =   ' pub3 ' )
     goto  notExistError
    
use   [ pub3 ]
 
create   table  #publication_snapshot
(
id  int  , -- 快照代理的 ID。
name  nvarchar ( 100 ),  -- 快照代理的名称。
publisher_security_mode  smallint  ,
--  --代理在连接发布服务器时所使用的安全模式，可以是下列模式之一：
-- 0 = SQL Server 身份验证
-- 1 = Windows 身份验证。
publisher_login sysname, 
-- 连接发布服务器时所使用的登录名。
publisher_password  nvarchar ( 524 ) ,
  -- 出于安全原因，始终返回值 **********。
job_id  uniqueidentifier  ,
  -- 代理作业的唯一 ID。
job_login  nvarchar ( 512 ) ,
  -- 运行快照代理时所用的 Windows 帐户，以 DOMAIN\username 的格式返回。
job_password sysname, 
-- 出于安全原因，始终返回值 **********。
schedule_name sysname, 
-- 用于该代理作业的计划的名称。
frequency_type  int  ,
--  代理计划运行的频率，可以为下列值之一：
-- 1 = 执行一次 
-- 2 = 按需 
-- 4 = 每天 
-- 8 = 每周 
-- 16 = 每月 
-- 32 = 与“每月”选项相关 
-- 64 = 自动启动 
-- 128 = 重复执行
 
frequency_interval  int  ,
--  代理运行的日期，可以为下列值之一：
-- 1 = 星期日 
-- 2 = 星期一 
-- 3 = 星期二 
-- 4 = 星期三 
-- 5 = 星期四 
-- 6 = 星期五 
-- 7 = 星期六 
-- 8 = 天 
-- 9 = 工作日 
-- 10 = 休息日
frequency_subday_type  int  ,
--  定义当 frequency_type 为 4（每天）时代理运行频率的类型，可以为下列值之一：
-- 1 = 在指定时间 
-- 2 = 秒 
-- 4 = 分钟 
-- 8 = 小时
frequency_subday_interval  int  ,
  -- 在计划的代理执行之间出现的 frequency_subday_type 间隔数。
frequency_relative_interval  int  ,
--  当 frequency_type 为 32（与“每月”选项相关）时，代理在给定月份的这一周运行，可以为下列值之一：
-- 1 = 第一周 
-- 2 = 第二周 
-- 4 = 第三周 
-- 8 = 第四周 
-- 16 = 最后一周 
frequency_recurrence_factor  int  ,
  -- 在计划的代理执行之间间隔的周数或月数。
active_start_date  int  ,
  -- 计划第一次运行代理的日期，格式为 YYYYMMDD。
active_end_date  int  ,
  -- 计划最后一次运行代理的日期，格式为 YYYYMMDD。
active_start_time  int  ,
  -- 计划第一次运行代理的时间，格式为 HHMMSS。
active_end_time  int  ,
  -- 计划最后一次运行代理的时间，格式为 HHMMSS。
 )
 
create   table  #logreader_agent
(
id  int ,  -- 代理 ID。
name  nvarchar ( 100 ) ,
  -- 代理的名称。
publisher_security_mode   smallint  ,
--  代理在连接发布服务器时所使用的安全模式，可以是下列模式之一：
-- 0 = SQL Server 身份验证
-- 1 = Windows 身份验证。
publisher_login sysname ,
  -- 连接发布服务器时所使用的登录名。
publisher_password  nvarchar ( 524 ) ,
  -- 出于安全原因，始终返回值 **********。
job_id  uniqueidentifier  ,
  -- 代理作业的唯一 ID。
job_login  nvarchar ( 512 ) ,
  -- 用于运行日志读取器代理的 Windows 帐户，该帐户以 domain\username 格式返回。
job_password sysname 
-- 出于安全原因，始终返回值 **********。
 )
 
 
insert  #publication_snapshot
  exec (  ' sp_helppublication_snapshot publicationTest ' ) -- replace the publicationName with your real name
insert  #logreader_agent
  exec (  ' sp_helplogreader_agent ' )
 
 
 
select  id,name  as  JobName,      case  publisher_security_mode 
                         when   0   then   ' SQL Authentication '  
                         WHEN   1   THEN   ' WINDOWS Authentication '
                         else   ' Error '
                      end   as  publisher_security_mode,
         publisher_login,job_login,frequency_type,
                   case   frequency_type
                             when      1   then   ' One time  '
                             when      2   then   ' On demand  '
                             when      4   then   ' Daily  '
                             when      8   then   ' Weekly  '
                             when      16   then   ' Monthly  '
                             when      32   then   ' Monthly relative  '
                             when      64   then   ' Autostart  '
                             when      128   then   ' Recurring '
                             else   ' Error '
                     end   as  frequency_type_desc,frequency_interval
   from  #publication_snapshot
  
select  id,name  as  JobName,      case  publisher_security_mode 
                                     when   0   then   ' SQL Authentication '  
                                     WHEN   1   THEN   ' WINDOWS Authentication '
                                     else   ' Error '
                                  end   as  publisher_security_mode,
         publisher_login,job_login
  from  #logreader_agent
 

notExistError:
print   ' please use an exist publication database '

 

 

 

确保snapshot属性中的frequency_type为1或者2,  frequency_interval(目前的业务没有重新初始化快照的需求,所以…)

publisher_security_mode 为1表示windows验证,对应的publisher_login会为空 . 在这种设置下,snapshot代理连接到发布库时使用’模拟进程账户(也就是job_login)’而不是sql login

如果发现错误的设置,可以通过sp_changepublication_snapshot 进行修改

exec  sp_changepublication_snapshot  @publication =    ' publicationTest ' ,  @frequency_type =   1

log reader agent的一些属性

如果发现错误的设置,可以通过sp_changelogreader_agent来进行修改

 

3 检查订阅的信息
sp_helpsubscription @article = N'all'  --在发布库执行
确认订阅的状态(subscription status),应该为2(活动)
订阅模式(subscription type):1为pull,0为push 表的订阅为pull类型; 其他类型<例如存储过程>的订阅为push类型,这类的订阅对时时行要求不高,可以使用share agent,并且对应的job运行频率也可以调低
job_login :如果是pull模式,此项为null,需要到订阅服务器上去检查.
subscriber_security: windows 验证 ,应该为1

 

检查pull订阅,在订阅库执行

 

use  subdatabaseName  -- replace it with your real subscriber database name
go
create   table  #subscription
(
publisher sysname  null  , -- 发布服务器的名称。
[ publisher database ] sysname  null  , -- 发布服务器数据库的名称。
publication sysname  null  , -- 发布的名称。
independent_agent  bit ,  -- 指示该发布是否有独立的分发代理。
[ 订阅类型 ]   int  , -- 发布的订阅类型。
[ distribution agent ]   nvarchar ( 100 ) , -- 处理订阅的分发代理。
[ publication description ]   nvarchar ( 255 ) , -- 对发布的说明。
[ last updating time ]   datetime  , -- 订阅信息上次更新的时间。 这是由 ISO 日期 (114) 和 ODBC 时间 (121) 组成的 UNICODE 字符串。 格式为 yyyymmdd hh:mi:sss.mmm，其中“yyyy”表示年，“mm”表示月，“dd”表示日，“hh”表示小时，“mi”表示分钟，“sss”表示秒，“mmm”表示毫秒。
  [ 订阅名称 ] varchar ( 386 ),  -- 订阅的名称。
[ last transaction timestamp ] varbinary ( 16 ),  -- 上一个复制的事务的时间戳。
  [ 更新模式 ]   tinyint ,  -- 允许的更新类型。
  [ distribution agent job_id ]   int  , -- 分发代理的作业 ID。
enabled_for_synmgr  int  , -- 指示是否可以通过 Microsoft 同步管理器同步订阅。
[ subscription guid ]   binary ( 16 ) , -- 发布上订阅版本的全局标识符。
subid  binary ( 16 ),  -- 匿名订阅的全局标识符。
[ immediate_sync ]   bit  , -- 表示是否在每次快照代理运行时创建或重新创建同步文件。
[ publisher login ] sysname  null , -- [在发布服务器上用于 SQL Server 身份验证的登录 ID。]
[ publisher password ] nvarchar ( 524 ),  -- 在发布服务器上用于 SQL Server 身份验证的密码（加密）。
[ publisher security_mode ] int  ,
--  在发布服务器上实施的安全模式：
-- 0 = SQL Server 身份验证 
-- 1 = Windows 身份验证 
-- 2 = 同步触发器使用静态 sysservers 项执行远程过程调用 (RPC)，并且必须在 sysservers 表中将 publisher 定义为远程服务器或链接服务器。
[ distributor ]  sysname  null  ,  -- 分发服务器的名称。 
distributor_login  sysname  null  , -- 在分发服务器上用于 SQL Server 身份验证的登录 ID。
distributor_password  nvarchar ( 524 ),  -- 在分发服务器上用于 SQL Server 身份验证的密码（加密）。
distributor_security_mode  int  ,
--  在分发服务器上实施的安全模式： 
-- 0 = SQL Server 身份验证 
-- 1 = Windows 身份验证
 
ftp_address sysname  null  , -- 仅为保持向后兼容。 
ftp_port  int  , -- 仅为保持向后兼容。
ftp_login sysname  null  , -- 仅为保持向后兼容。
ftp_password  nvarchar ( 524 ),  -- --仅为保持向后兼容。
alt_snapshot_folder  nvarchar ( 255 ) , -- 存储快照文件夹的位置（如果该位置是默认位置以外的位置）。
working_directory  nvarchar ( 255 ) , -- 使用文件传输协议 (FTP) 传输快照文件（指定了该选项时）时将文件传输到的目录的完全限定路径。
use_ftp  bit  ,  -- 订阅通过 Internet 订阅发布，并配置 FTP 寻址属性。 如果为 0，则订阅不使用 FTP。 如果为 1，则订阅使用 FTP。
publication_type  int  ,
--  指定发布的复制类型
-- 0 = 事务复制
-- 1 = 快照复制
-- 2 = 合并复制
dts_package_name sysname  null  ,
  -- 指定 Data Transformation Services (DTS) 包的名称。
dts_package_location  int  ,
--  存储 DTS 包的位置：
-- 0 = 分发服务器
-- 1 = 订阅服务器
offload_agent  bit  ,
  -- 指定是否可以远程激活代理。 如果为 0，则无法远程激活代理。
offload_server sysname  null  ,
  -- 指定用于远程激活的服务器所在的网络的名称。 
last_sync_status  int  ,
--  订阅状态： 
-- 0 = 所有作业都在等待启动
-- 1 = 一个或多个作业正在启动
-- 2 = 所有作业都已成功执行
-- 3 = 至少有一个作业正在执行
-- 4 = 所有作业都已计划好并处于空闲状态
-- 5 = 在上次失败后至少有一个作业正在尝试执行
-- 6 = 至少有一个作业不能成功执行 
last_sync_summary sysname  null  , -- 对上一次同步结果的说明。 
last_sync_time  datetime  , -- 订阅信息上次更新的时间。 这是由 ISO 日期 (114) 和 ODBC 时间 (121) 组成的 UNICODE 字符串。 格式为 yyyymmdd hh:mi:sss.mmm，其中“yyyy”表示年，“mm”表示月，“dd”表示日，“hh”表示小时，“mi”表示分钟，“sss”表示秒，“mmm”表示毫秒。
job_login  nvarchar ( 512 ) , -- 运行分发代理时所用的 Windows 帐户，以 domain\username 的格式返回。
job_password sysname  null   -- 出于安全原因，总是返回值“**********”。
 )
 
 
  insert  #subscription
  exec ( ' sp_helppullsubscription  ' )
 
select  publisher, [ publisher database ] ,publication,distributor,distributor_security_mode, job_login , use_ftp  From  #subscription

 

可以使用sp_change_subscription_properties进行更改

declare   @publisher  sysname
set   @publisher = N ' publiserServer '
declare   @publication  sysname
set   @publication = N ' publicationName '
declare   @publisher_db  sysname
set   @publisher_db = N ' pubDB '
exec  sp_change_subscription_properties   @publisher   = @publisher ,
  @publisher_db   =     @publisher_db ,
  @publication = @publication
        ,   @property   =    ' distrib_job_login '
        ,   @value   =    ' domain\username '
 
exec  sp_change_subscription_properties   @publisher   = @publisher ,
  @publisher_db   =     @publisher_db ,
  @publication = @publication
        ,   @property   =    ' distrib_job_password '
        ,   @value   =    ' p@s5w0rd '
 
exec  sp_change_subscription_properties   @publisher   = @publisher ,
  @publisher_db   =     @publisher_db ,
  @publication = @publication
        ,   @property   =    ' distributor_security_mode '
        ,   @value   =    ' 1 '

 
go