自动配置复制订阅脚本

IF OBJECT_ID(‘pro_add_repl’) IS NOT NULL
DROP PROCEDURE pro_add_repl;
GO
– =============================================
– Author: qin
– Create date: <2017-10-25 18:28>目前仅支持推送订阅(push)（只测试了推送订阅，其它未测试）
– Update date: <2017-12-13 17:43>自动识别发布项目是否已存在，（新加表时）仅生成相应脚本,
– Update date: <2018-04-03 15:30>默认truncate目标表，添加项目筛选器
– Update date: <2018-12-06 18:00>优化筛选条件，添加初始化选项，订阅方式等
– Description: <添加发布订阅>
– =============================================
CREATE PROCEDURE pro_add_repl
(
@publication varchar(64), --发布名称
@article varchar(64)=’’, --发布项目名称（为空时取发布表名）
@sour_schema varchar(64)=‘dbo’, --发布表所属架构
@sour_table varchar(64), --发布表名
@dest_schema varchar(64)=’’, --订阅表所属架构（为空时取发布架构）
@dest_table varchar(64)=’’, --订阅表名（为空时取发布表名）
@filter_column varchar(64)=’’, --筛选列，默认为空
@filter_value varchar(64)=’’, --筛选值，默认为空
@subscription_type varchar(10)=‘pull’, --订阅方式：push 推送订阅 pull 请求订阅 none = 匿名
@pre_creation_cmd varchar(10) = ‘truncate’, --初始化时预创建命令(初始化)： none drop delete truncate
–@scriptflag int=2, --1完整创建部署脚本 2智能检测部署情况并创建部署脚本
@error_flag int=0 output, --错误标志 0正确
@error_desc varchar(1024)=’’ output --错误描述
)
WITH encryption
as
BEGIN
set nocount on;
–参数
set nocount on;
declare @sql nvarchar(max);
declare @sql_folder nvarchar(max);
declare @sql_publication nvarchar(max);
declare @sql_article_all nvarchar(max);
declare @sql_article_header nvarchar(max);
declare @sql_article_body nvarchar(max);
declare @sql_article nvarchar(max);
declare @sql_article_tail nvarchar(max);
declare @sql_subscription nvarchar(max);
declare @disk_folder nvarchar(512), @alt_snapshot_folder nvarchar(512);

-- 分发服务器（计算机名）
declare @Dist_Server varchar(64);	
-- 发布服务器（计算机名）及发布数据库名称
declare @publisher_server varchar(64),@publisher_db varchar(64)
-- 订阅服务器（计算机名）及订阅数据库名称
declare @subscriber_server varchar(64),@destination_db varchar(64);
-- 发布及订阅用户（建议发布与订阅一致）
declare @publisher_login varchar(64),@publisher_password varchar(64),@base_disk nvarchar(512),@base_folder nvarchar(512);


--在此处修改参数（这参数较少修改，故放在过程内，初次创建时修改）
--1.参数
select @publisher_login = 'repl',@publisher_password = 'Admin123456',@base_disk = 'E:',@base_folder = 'ReplData',@publisher_db = 'pub_database',@destination_db = 'repl_database';
select @Dist_Server = 'WINDOWS-server1',@publisher_server = 'WINDOWS-server2',@subscriber_server = 'WINDOWS-server3';



--以下正式执行部分，不要修改
--
if object_id('tempdb..#t_publication') is not null
	drop table #t_publication;
if object_id('tempdb..#t_pubarticle') is not null
	drop table #t_pubarticle;

CREATE TABLE #t_publication(publication varchar(64),publisher_dir varchar(1024),publication_flag tinyint,table_flag tinyint);
CREATE TABLE #t_pubarticle(publication varchar(64),article varchar(64),sour_schema varchar(64),sour_table varchar(64),filter_column varchar(64),filter_value varchar(64),dest_schema varchar(64),dest_table varchar(64),table_flag tinyint);	
CREATE TABLE #t_subarticle(publication varchar(64),article varchar(64),sour_schema varchar(64),sour_table varchar(64),filter_column varchar(64),filter_value varchar(64),dest_schema varchar(64),dest_table varchar(64),table_flag tinyint);	

IF ISNULl(@article,'') = ''
	SET @article = @sour_table;

IF ISNULl(@dest_schema,'') = ''
	SET @dest_schema = @sour_schema;

IF ISNULl(@dest_table,'') = ''
	SET @dest_table = @sour_table;

--1.生成发布对象
    IF @publication <> ''
BEGIN
	INSERT INTO #t_publication(publication,publisher_dir,publication_flag,table_flag)
	select @publication publication,'' publisher_dir,0 as publication_flag,0 table_flag 

	INSERT INTO #t_pubarticle(publication,article,sour_schema,sour_table,filter_column,filter_value,dest_schema,dest_table,table_flag)
	select 	@publication publication,@article article,@sour_schema sour_schema,@sour_table sour_table,@filter_column filter_column,@filter_value filter_value,@dest_schema dest_schema,@dest_table dest_table,0 as table_flag 
	where  1=1
		and not exists (select 1 from [distribution].dbo.MSarticles where source_owner + source_object = @sour_schema + @sour_table)

	----?
	INSERT INTO #t_subarticle(publication,article,sour_schema,sour_table,filter_column,filter_value,dest_schema,dest_table,table_flag)
	select 	@publication publication,@article article,@sour_schema sour_schema,@sour_table sour_table,@filter_column filter_column,@filter_value filter_value,@dest_schema dest_schema,@dest_table dest_table,0 as table_flag 
	where 1 = 1
		and @article in (select article from [distribution].dbo.MSarticles)
		and not exists (select 1 from dbo.sysarticles where dest_owner + dest_table = @dest_schema + @dest_table )
END
--select * from #t_pubarticle
--select * from #t_subarticle

if not exists (select * from #t_pubarticle) and not exists (select * from #t_subarticle) 
begin
	print '不存在需要发布或者订阅项目，退出';
	return;
end
--select * from #t_publication;return;
--select * from #t_pubarticle;return;

/*
select * from [distribution].dbo.MSpublications
select * from [distribution].dbo.MSarticles
select * from #t_publication order by publisher_dir,publication
select * from #t_pubarticle order by publication,sour_table;return

*/

--订正项目已存在但有新发布表的情况
update t 
set publication_flag = 1
from #t_publication t,[distribution].dbo.MSpublications s
where t.publication = s.publication
--select * from #t_publication;return;


--2.生成复制订阅配置脚本
-- 发布任务
declare @publication_flag int;
declare @snapshot_folder nvarchar(512);	--快照目录，为空时发布在基础目录下	;STN_FUEL_DAY_TEMPERATURE
--print @alt_snapshot_folder

--	发布项目
--1.生成发布脚本
while exists(select * from #t_publication where table_flag = 0)
begin
	--发布项目
	select top 1 @publication = publication,@publication_flag = publication_flag,@snapshot_folder = publisher_dir from #t_publication where table_flag = 0 order by publisher_dir,publication
	set @alt_snapshot_folder = '\\' + @publisher_server + '\' + @base_folder + '\' + case isnull(@snapshot_folder,'') when '' then ''  else isnull(@snapshot_folder,'') + '\' end + @publication + '\';
	--print @publication;

	--
	set @disk_folder = @base_disk + '\' + @base_folder + '\' + case isnull(@snapshot_folder,'') when '' then ''  else isnull(@snapshot_folder,'') + '\' end + @publication + '\';

	set @sql_folder = '-----------------开始: 要在发布服务器“' + @publisher_server + '”上运行的发布脚本-----------------' + char(13) + char(10) 
	print @sql_folder;

	if @publication_flag = 0
		set @sql_publication = '-- 创建快照目录

EXEC xp_cmdshell ‘‘if not exist ’ + @disk_folder + ’ mkdir ’ + @disk_folder + ‘’’;
GO
– 添加事务发布
use [’ + @publisher_db + ‘]
GO
EXEC sp_addpublication @publication = N’’’ + @publication + ‘’’, @description = N’‘来自发布服务器“’ + @publisher_server + ‘”的数据库“’ + @publisher_db + ‘”的事务发布。’’, @sync_method = N’‘concurrent’’, @retention = 0, @allow_push = N’‘true’’, @allow_pull = N’‘true’’, @allow_anonymous = N’‘false’’, @enabled_for_internet = N’‘false’’, @snapshot_in_defaultfolder = N’‘false’’,@alt_snapshot_folder = N’’’ + @alt_snapshot_folder + ‘’’, @compress_snapshot = N’‘false’’, @ftp_port = 21, @ftp_login = N’‘anonymous’’, @allow_subscription_copy = N’‘false’’, @add_to_active_directory = N’‘false’’, @repl_freq = N’‘continuous’’, @status = N’‘active’’, @independent_agent = N’‘true’’, @immediate_sync = N’‘false’’, @allow_sync_tran = N’‘false’’, @autogen_sync_procs = N’‘false’’, @allow_queued_tran = N’‘false’’, @allow_dts = N’‘false’’, @replicate_ddl = 1, @allow_initialize_from_backup = N’‘false’’, @enabled_for_p2p = N’‘false’’, @enabled_for_het_sub = N’‘false’’;
GO
EXEC sp_addpublication_snapshot @publication = N’’’ + @publication + ‘’’, @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 = 0, @publisher_login = N’’’ + @publisher_login + ‘’’, @publisher_password = N’’’ + @publisher_password + ‘’’;
GO’;
else
set @sql_publication = ‘’;
print @sql_publication

	--发布表
	set @sql_article_header = '-- 添加发布项目（表）

use [’ + @publisher_db + ‘]
GO’;
print @sql_article_header;

	--同步表
	--1. 添加项目
	while exists(select * from #t_pubarticle where publication = @publication and table_flag = 0)
	begin
		select top 1 @article = article,@sour_schema = sour_schema,@sour_table = sour_table,@filter_column=filter_column,@filter_value=filter_value,@dest_schema = dest_schema,@dest_table = dest_table from #t_pubarticle where publication = @publication and table_flag = 0 order by publication,sour_table
		--print @article;

		/*--1.1 添加项目
		set @sql_article = 'EXEC sp_addarticle @publication = N''' + @publication + ''', @article = N''' + @article + ''', @source_owner = N''' + @sour_schema + ''', @source_object = N''' + @sour_table + ''', @type = N''logbased'', @description = N'''', @creation_script = null, @pre_creation_cmd = N''truncate'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N''manual'', @destination_table = N''' + @dest_table + ''', @destination_owner = N''' + @dest_schema + ''', @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_' + @dest_schema + @dest_table + ''', @del_cmd = N''CALL sp_MSdel_' + @dest_schema + @dest_table + ''', @upd_cmd = N''SCALL sp_MSupd_' + @dest_schema + @dest_table + '''

GO’;*/
–
set @sql_article = ‘EXEC sp_addarticle @publication = N’’’ + @publication + ‘’’, @article = N’’’ + @article + ‘’’, @source_owner = N’’’ + @sour_schema + ‘’’, @source_object = N’’’ + @sour_table + ‘’’, @type = N’‘logbased’’, @description = N’’’’, @creation_script = null, @pre_creation_cmd = N’’’ + @pre_creation_cmd + ‘’’, @schema_option = 0x000000000803509F, @force_invalidate_snapshot = 1, @identityrangemanagementoption = N’‘manual’’, @destination_table = N’’’ + @dest_table + ‘’’, @destination_owner = N’’’ + @dest_schema + ‘’’, @vertical_partition = N’‘false’’, @ins_cmd = N’‘CALL sp_MSins_’ + @dest_schema + @dest_table + ‘’’, @del_cmd = N’‘CALL sp_MSdel_’ + @dest_schema + @dest_table + ‘’’, @upd_cmd = N’‘SCALL sp_MSupd_’ + @dest_schema + @dest_table + ‘’’;
GO’;
print @sql_article

		--1.2 修改项目对象筛选器
		if isnull(@filter_column,'') <> '' and isnull(@filter_value,'') <> ''
		begin
			--行筛选
			--exec sp_articlefilter @publication = N'repl_ptest', @article = N'STN_FUEL_DAY_TEMPERATURE2', @filter_name = N'FLTR_STN_FUEL_DAY_TEMPERATURE2_1__183', @filter_clause = N'[DAY_BATCH_DATE] >''2018-03-25''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
			set @sql_article = 'EXEC sp_articlefilter @publication = N''' + @publication + ''', @article = N''' + @article + ''', @filter_name = N''FLTR_' + @sour_table + ''', @filter_clause = N''' + @filter_column + ' >= ' + @filter_value + ''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;

GO’;
print @sql_article;

			--列筛选
			--EXEC sp_articlecolumn @publication = @publication, @article = @table;
			--EXEC sp_articlecolumn @publication = @publication, @article = @table, @column = N'DaysToManufacture', @operation = N'drop';
		end;

		--1.3 创建已发布项目视图（限未订阅项目使用）
		--exec sp_articleview @publication = N'STN_FUEL_DAY_TEMPERATURE', @article = N'STN_FUEL_DAY_TEMPERATURE', @view_name = N'SYNC_STN_FUEL_DAY_TEMPERATURE', @filter_clause = N'day_batch_date >= ''2018-04-12''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
		if @filter_column <> ''
			set @sql_article = 'EXEC sp_articleview @publication = N''' + @publication + ''', @article = N''' + @article + ''', @view_name = N''SYNC_' + @sour_table + ''', @filter_clause = N''' + @filter_column +  ' >= ' + @filter_value + ''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;

GO’;
print @sql_article;

		--订正标记
		update #t_pubarticle set table_flag = 1 where publication = @publication and article = @article;
		set @sql_article_body = isnull(@sql_article_body,'') + @sql_article + char(13) + char(10)
		--print @sql_article_all
	end
print '-----------------结束: 要在发布服务器“' + @publisher_server + '”上运行的脚本-----------------

’

	--2 生成订阅脚本
	if @publication_flag = 1
	begin
		set @sql_article_tail =  'EXEC sp_refreshsubscriptions @publication = N''' + @publication + ''';';
		print @sql_article_tail;
	end
	else
		set @sql_article_tail = '';

	--select @sql_article_header , @sql_article_body , @sql_article_tail
	set @sql_article_all = case when isnull(@sql_article_header,'') <>  '' then char(13) + char(10) + @sql_article_header else '' end + char(13) + char(10) + @sql_article_body +  case when isnull(@sql_article_tail,'') <>  '' then char(13) + char(10) + @sql_article_tail else '' end
	--print @sql_article_all

	if @publication_flag = 0
		if @subscription_type = 'push'
			set @sql_subscription = '

-----------------开始: 要在发布服务器“’ + @publisher_server + ‘”上运行的订阅脚本-----------------
– 添加推送事务订阅
use [’ + @publisher_db + ‘]
GO
EXEC sp_addsubscription @publication = N’’’ + @publication + ‘’’, @subscriber_server = N’’’ + @subscriber_server + ‘’’, @destination_db = N’’’ + @destination_db + ‘’’, @subscription_type = N’‘Push’’, @sync_type = N’‘automatic’’, @article = N’‘all’’, @update_mode = N’‘read only’’, @subscriber_server_type = 0;
GO
EXEC sp_addpushsubscription_agent @publication = N’’’ + @publication + ‘’’, @subscriber_server = N’’’ + @subscriber_server + ‘’’, @subscriber_server_db = N’’’ + @destination_db + ‘’’, @job_login = null, @job_password = null, @subscriber_server_security_mode = 0, @subscriber_server_login = N’’’ + @publisher_login + ‘’’, @subscriber_server_password = N’’’ + @publisher_password + ‘’’, @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 = 20171026, @active_end_date = 99991231, @enabled_for_syncmgr = N’‘False’’, @dts_package_location = N’‘Distributor’’;
GO’
else if @subscription_type = ‘pull’
set @sql_subscription = ’
-----------------开始: 要在发布服务器“’ + @publisher_server + ‘”上运行的订阅脚本-----------------
use [’ + @publisher_db + ‘]
exec sp_addsubscription @publication = N’’’ + @publication + ‘’’, @subscriber = N’’’ + @publisher_server + ‘’’, @destination_db = N’’’ + @destination_db + ‘’’, @sync_type = N’‘Automatic’’, @subscription_type = N’‘pull’’, @update_mode = N’‘read only’’
GO
-----------------结束: 要在发布服务器“’ + @publisher_server + '”上运行的脚本-----------------

-----------------开始: 要在订阅服务器“’ + @subscriber_server + ‘”上运行的订阅脚本-----------------
use [’ + @destination_db + ‘]
exec sp_addpullsubscription @publisher = N’’’ + @publisher_server + ‘’’, @publication = N’’’ + @publication + ‘’’, @publisher_db = N’’’ + @publisher_db + ‘’’, @independent_agent = N’‘True’’, @subscription_type = N’‘pull’’, @description = N’’’’, @update_mode = N’‘read only’’, @immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N’’’ + @publisher_server + ‘’’, @publisher_db = N’’’ + @publisher_db + ‘’’, @publication = N’’’ + @publication + ‘’’, @distributor = N’’’ + @Dist_Server + ‘’’, @distributor_security_mode = 0, @distributor_login = N’’’ + @publisher_login + ‘’’, @distributor_password = ‘’’ + @publisher_password+ ‘’’, @enabled_for_syncmgr = N’‘False’’, @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 = 20161009, @active_end_date = 99991231, @alt_snapshot_folder = N’’’’, @working_directory = N’’’’, @use_ftp = N’‘False’’, @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------结束: 要在订阅服务器“’ + @subscriber_server + '”上运行的脚本-----------------
’
else
set @sql_subscription = ‘’;
print @sql_subscription

	--订正标记
	update #t_publication set publication_flag = 1,table_flag = 1 where publication = @publication;

	--发布表过多时，无法显示
	--select @sql_folder , @sql_publication , @sql_article_all ,  @sql_subscription
	set @sql = @sql_folder +  @sql_publication + @sql_article_all +  @sql_subscription;
	--print @sql;

	--每次只处理一个发布项目()
	break;
end

END
GO
return

1. 后记，不知怎么回事，发布后文章分多段显示代码，其实上面全部是一个存储过程的内容，有知道怎么解决的请留言告知我
2. 使用
   1. 连接发布数据库
   2. 修改过程中部分参数（详见过程）
   3. 运行存储过程。见下面样例
   4. 按照打印出来的语句及其提示运行，如有异常请留言（请给出你的参数）
      declare @error_flag int,@error_desc varchar(1024);
      exec dbo.pro_add_repl
      @publication =‘repl_test’, --发布名称
      @article =‘test’, --发布项目名称
      @sour_table =‘table1’, --发布表名
      @dest_table =‘table2’, --订阅表名（为空时取发布表名）
      @filter_column =’’, --筛选列
      @filter_value =’’, --筛选条件
      @subscription_type =‘pull’, --订阅方式：push 推送订阅 pull 请求订阅 none = 匿名
      @pre_creation_cmd =‘truncate’, --初始化时预创建命令(初始化)： none drop delete truncate
      –@scriptflag =2, --1创建部署脚本 2检测并创建部署脚本（如果在生产库运行，可根据已部署情况检测自适应创建部署脚本）
      @error_flag =@error_flag output, --错误标记 0 正确
      @error_desc=@error_desc output --错误描述
      print @error_flag;print @error_desc