查看/修改分发复制代理的各个属性
最近给服务器分发复制系统做了一次检查,发现许多设置并没有遵循<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
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
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'
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
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'
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
