SqlServer 使用指令碼建立分發服務及事務複製的可更新訂閱，sqlserver指令碼

SqlServer 使用指令碼建立分發服務及事務複製的可更新訂閱，sqlserver指令碼

【建立使用本機散發者】

/************************【使用本機散發者配置發布】***********************/--SqlServer 2008 R2--https://technet.microsoft.com/zh-cn/library/ms151860(v=sql.105).aspxuse mastergo--伺服器上是否已安裝散發者--https://msdn.microsoft.com/zh-cn/library/ms190339(v=sql.105).aspxexec master.dbo.sp_get_distributorgo--配置散發者--https://msdn.microsoft.com/zh-cn/library/ms176028(v=sql.105).aspxexec master.dbo.sp_adddistributor  @distributor = 'KK'--散發者名稱,@heartbeat_interval = 10--代理在不記錄進度訊息的情況下可以啟動並執行最長分鐘數,@password = N'201card113@app'--散發者密碼go--配置散發資料庫--https://msdn.microsoft.com/zh-cn/library/ms189755(v=sql.105).aspxexec master.dbo.sp_adddistributiondb  @database = N'distribution'--要建立的散發資料庫的名稱,@data_folder = N'E:\TempFile\Distribution' --散發資料庫資料檔案的目錄,@data_file = N'distribution'--資料庫檔案的名稱,@data_file_size = 5--初始資料檔案大小，以MB (MB) 為單位,@log_folder = N'E:\TempFile\Distribution' ----散發資料庫記錄檔的目錄,@log_file = N'distribution_log' ,@log_file_size =  5--初始記錄檔大小，以MB (MB) 為單位 ,@min_distretention = 0--從散發資料庫中刪除事務前的最小保持期，以小時為單位,@max_distretention = 72--刪除事務前的最大保持期，以小時為單位 ,@history_retention = 48--記錄的保留時間，以小時為單位 ,@security_mode = 1--同步時串連到散發者的安全模式。預設值為1:Windows驗證,0: SQL驗證 ,@login = N'KK\SqlReplicator' ,@password = N'123456' ,@createmode = 1--1:建立或使用現有資料庫(instdist.sql) go--配置發行伺服器以使用指定的散發資料庫--https://msdn.microsoft.com/zh-cn/library/ms173807(v=sql.105).aspxexec master.dbo.sp_adddistpublisher  @publisher = N'KK'--發行伺服器的名稱,@distribution_db = N'distribution'--散發資料庫的名稱 ,@security_mode = 1--安全模式,預設1:Windows驗證,0: SQL驗證 ,@login = N'KK\SqlReplicator' ,@password = N'123456' ,@working_directory = N'E:\TempFile\ReplData'--預設快照檔案夾的UNC共用目錄,@thirdparty_flag  = 0--發行伺服器是否是SQLServer,預設0:是,1:否,@publisher_type  = N'MSSQLSERVER'--發行伺服器類型:MSSQLSERVER(預設)/ORACLE/ORACLE GATEWAYgo/*****配置完成!!*****/

/*****【查看分發屬性】******/--檢查散發資料庫目錄exec master.dbo.xp_subdirs N'E:\TempFile\Distribution'--發行伺服器的屬性（在散發者任何資料庫執行）--https://technet.microsoft.com/zh-cn/library/ms190323(v=sql.105).aspxexec master.dbo.sp_helpdistpublisher N'KK'--散發資料庫的屬性（在散發者的散發資料庫上執行）--https://msdn.microsoft.com/zh-cn/library/vstudio/aa238917.aspxexec master.dbo.sp_helpdistributiondb N'distribution'--列出有關散發者相關資訊（在散發者任何資料庫執行）--https://msdn.microsoft.com/zh-cn/library/ms177504(v=sql.105).aspxexec master.dbo.sp_helpdistributor

/*****【刪除分發】******/--這裡不必執行！--刪除分發發行伺服器（在散發者任何資料庫執行）--https://technet.microsoft.com/zh-cn/library/ms188411(v=sql.105).aspxexec master.dbo.sp_dropdistpublisher @publisher =  N'KK',@no_checks = 0,@ignore_distributor = 0 --檢查對象;串連分發;--刪除散發資料庫（在散發者任何資料庫執行）--https://msdn.microsoft.com/zh-cn/library/ms188355(v=sql.105).aspxexec master.dbo.sp_dropdistributiondb N'distribution';--卸載散發者（除散發資料庫之外的任何資料庫中執行）--https://technet.microsoft.com/zh-cn/library/ms173516(v=sql.105).aspxexec master.dbo.sp_dropdistributor @no_checks = 0,@ignore_distributor = 0 --檢查對象;串連分發;

【建立可更新訂閱的事務發布】

/*************************************【建立發布】**************************************//*【執行個體:可更新訂閱】* A 為發行集資料庫,id為每個表的主鍵* B,C 為訂閱資料庫* A中符合條件 [id % 2 = 0] 的同步到B中* A中符合條件 [id % 2 = 1] 的同步到C中* 以下以 B 建立發布訂閱  當前發行集資料庫:[mytest]  當前發行集資料庫:[mytestA]    發布名稱,(發行集資料庫名),(訂閱資料庫名),篩選條件*/--作為發布的資料庫use [mytest]--設定指定資料庫的複製資料庫選項（發行伺服器或訂閱伺服器執行）--http://msdn.microsoft.com/zh-cn/library/ms188769.aspxexec sys.sp_replicationdboption @dbname = N'mytest', @optname = N'publish', @value = N'true'go--為給定散發者添加佇列讀取器代理程式（在分發庫或發布庫執行）（每個執行個體預設只1個，已存在可不須再執行）--http://msdn.microsoft.com/ZH-CN/LIBRARY/ms189517exec sys.sp_addqreader_agent @job_login = N'KK\SqlReplicator', @job_password = N'123456', @job_name = null, @frompublisher = 1go--為給定資料庫添加記錄讀取器代理程式（在發行集資料庫執行）（每個資料庫預設只1個，已存在可不須再執行）--http://technet.microsoft.com/zh-cn/library/ms189516.aspxexec sys.sp_helplogreader_agent @publisher = null --查看當前資料庫存在的日誌代理exec sys.sp_addlogreader_agent @job_login = N'KK\SqlReplicator', @job_password = N'123456', @publisher_security_mode = 1, @job_name = nullgo/***************************【建立發布:以下用於1庫多發布】******************************/--添加（可更新訂閱）事務發布（在發行集資料庫執行）--http://msdn.microsoft.com/zh-cn/library/ms188738(v=sql.100).aspxexec sys.sp_addpublication @publication = N'tran_repl',--【指定發布名稱】@description = N'來自發行伺服器“”的資料庫“mytest”的具有可更新訂閱的事務發布。', @sync_method = N'concurrent',--同步模式:原生模式大量複製程式輸出@retention = 0,--訂閱活動的保持期（小時）:預設值為336小時;0:訂閱永不到期@allow_push = N'true',--發送訂閱 @allow_pull = N'true',--允許建立提取訂閱@allow_anonymous = N'false',--不可建立匿名訂閱 @enabled_for_internet = N'false',--非Internet發布@snapshot_in_defaultfolder = N'false',--不指定快照預設資料夾,須設定@alt_snapshot_folder@alt_snapshot_folder = N'E:\TempFile\ReplData',--指定快照的待命資料庫檔案夾的位置@compress_snapshot = N'false',--不壓縮快照@ftp_port = 21,--預設散發者的FTP服務的連接埠號碼:21@ftp_login = N'anonymous',--預設用於串連到 FTP 服務的使用者名稱: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'true',--允許使用【立即更新訂閱】@autogen_sync_procs = N'true',--在發行伺服器上產生更新訂閱的【同步預存程序】@allow_queued_tran = N'true',--在訂閱伺服器中啟用更改的隊列@allow_dts = N'false',--不允許資料轉換@conflict_policy = N'sub wins',--排隊更新訂閱伺服器選項時所遵從的衝突解決方案策略:【訂閱入選】@centralized_conflicts = N'true',--在發行伺服器上儲存衝突記錄@conflict_retention = 14,--衝突保持期（天）@queue_type = N'sql',--使用的隊列類型:預設SQL Server儲存事務@replicate_ddl = 1,--【支援架構複製】@allow_initialize_from_backup = N'false', --不允許用備份初始化訂閱@enabled_for_p2p = N'false',--非點對點複寫@enabled_for_het_sub = N'false'--只支援SQL Server訂閱伺服器go--為指定的發布建立快照代理（在發行集資料庫執行）--http://msdn.microsoft.com/zh-cn/library/ms174958(v=sql.100)exec sys.sp_addpublication_snapshot @publication = N'tran_repl',@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 = N'KK\SqlReplicator', @job_password = N'123456', @publisher_security_mode = 1go--更改發布屬性--https://msdn.microsoft.com/zh-cn/library/ms188413(v=sql.105).aspx--exec sys.sp_changepublication ……

--建立項目並將其添加到發布中（在發行集資料庫執行）--http://msdn.microsoft.com/zh-cn/library/ms173857/*1. 添加可篩選的表(預設架構dbo)*/declare @tableNamenvarchar(100)declare @publNamenvarchar(100)declare @markbit-- 區分是否有sid的列，有則進行篩選declare @filterNumnvarchar(10)-- 一個資料庫多個發布加編號區別declare @filterClausenvarchar(100)declare @SQLaddarticlenvarchar(max)declare @SQLarticlefilternvarchar(max)declare @SQLarticleviewnvarchar(max)set @publName = N'tran_repl' --【指定發布名稱】set @filterClause = N'dbo.f_SIDTOInt(SID) % 2 = 0' --【指定發布名稱】select @filterNum = CONVERT(NVARCHAR(10),count(*)) from distribution.dbo.MSpublicationsdeclare cur_addTable cursor local fast_forwardfor /*有主鍵 並且 有SID列(用於篩選)*/select name,1 mark from sys.tables t1(nolock) where is_ms_shipped = 0and exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')and name in(select table_name from information_schema.key_column_usage(nolock) where objectproperty(object_id(constraint_name),'isprimarykey')=1 )union all/*有主鍵 並且 無SID列(不可篩選)*/select name,0 mark from sys.tables t1(nolock) where is_ms_shipped = 0and not exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')and name in(select table_name from information_schema.key_column_usage (nolock)where objectproperty(object_id(constraint_name),'isprimarykey')=1 )open cur_addTablefetch next from cur_addTable into @tableName,@markwhile @@fetch_status = 0beginif ( @mark = 1 ) /*可篩選的表對象*/beginset @SQLaddarticle = N'exec sp_addarticle @publication = N'''+@publName+''', @article = N'''+@tableName+''', @source_owner = N''dbo'', @source_object = N'''+@tableName+''', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x0000000008035CDF, @identityrangemanagementoption = N''none'', @destination_table = N'''+@tableName+''', @destination_owner = N''dbo'', @status = 24,@vertical_partition = N''false'''exec(@SQLaddarticle)/*添加項目篩選器*/set @SQLarticlefilter = N' exec sp_articlefilter @publication = N'''+@publName+''', @article = N'''+@tableName+''', @filter_name = N''FLTR_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''', @filter_clause = N'''+@filterClause+''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1'exec(@SQLarticlefilter)/*添加項目同步對象*/set @SQLarticleview = N'exec sp_articleview @publication = N'''+@publName+''', @article = N'''+@tableName+''', @view_name = N''SYNC_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''', @filter_clause = N'''+@filterClause+''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1'exec(@SQLarticleview)print '已篩選:'+@tableNameendelse if ( @mark = 0 )beginset @SQLaddarticle = N'exec sp_addarticle @publication = N'''+@publName+''', @article = N'''+@tableName+''', @source_owner = N''dbo'', @source_object = N'''+@tableName+''', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x0000000008035CDF, @identityrangemanagementoption = N''none'', @destination_table = N'''+@tableName+''', @destination_owner = N''dbo'', @status = 24,@vertical_partition = N''false'''exec(@SQLaddarticle)print '無篩選:'+@tableNameendfetch next from cur_addTable into @tableName,@markendclose cur_addTabledeallocate cur_addTable

/*2. 添加視圖/預存程序/函數對象(預設架構dbo)*/declare @publNamenvarchar(100)declare @ObjectNamenvarchar(100)declare @Typenvarchar(30)declare @ObjectTypenvarchar(30)declare @SQLaddObjectnvarchar(max)set @publName = N'tran_repl' --【指定發布名稱】declare cur_addObject cursor local fast_forwardfor select name,type from mytest.sys.objects where type in(N'P') and is_ms_shipped = 0union allselect name,type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0and exists(select 1 from mytest.sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 0)union allselect name,N'B' as type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0 and exists(select 1 from sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 1)/*架構綁定的索引檢視表*/union allselect name,type from mytest.sys.objects where type in(N'TF',N'FN') and is_ms_shipped = 0order by type,nameopen cur_addObjectfetch next from cur_addObject into @ObjectName,@ObjectTypewhile @@fetch_status = 0beginSET @Type = (CASE WHEN @ObjectType = N'V' THEN N'view schema only'WHEN @ObjectType = N'B' THEN N'indexed view schema only'WHEN @ObjectType = N'P' THEN N'proc schema only'WHEN @ObjectType in(N'TF',N'FN') THEN N'func schema only'END)set @SQLaddObject = N'exec sp_addarticle @publication = N'''+@publName+''', @article = N'''+@ObjectName+''', @source_owner = N''dbo'', @source_object = N'''+@ObjectName+''', @type =  N'''+@Type+''', @description = null, @creation_script = null, @pre_creation_cmd = N''drop'', @schema_option = 0x0000000008000001, @status = 16,@destination_owner = N''dbo'', @destination_table = N'''+@ObjectName+''''exec(@SQLaddObject)print @ObjectType+ '：' + @ObjectNamefetch next from cur_addObject into @ObjectName,@ObjectTypeendclose cur_addObjectdeallocate cur_addObject

【建立訂閱】

/***********************************【建立訂閱】***************************************//*【要在發行伺服器上啟動並執行指令碼】*/use [mytest]--將訂閱添加到發布並設定訂閱伺服器的狀態--(警告: distribution 代理作業隱式建立，並將在 SQL Server Agent服務帳戶下運行)--http://technet.microsoft.com/zh-cn/library/ms181702(v=sql.100).aspxexec sys.sp_addsubscription @publication = N'tran_repl',--指定發布名稱@subscriber = N'KK',--訂閱伺服器@destination_db = N'mytestA',--訂閱資料庫@subscription_type = N'Push',--發送訂閱@sync_type = N'automatic',--預設,發行表的架構和初始資料將首先傳輸到訂閱伺服器@article = N'all',--發布所訂閱的項目@update_mode = N'queued failover',--將訂閱啟用為排隊更新訂閱，並允許更改為立即更新模式@subscriber_type = 0--訂閱伺服器的類型：SQL Server訂閱伺服器go--添加新的預定分發代理作業，以使發送訂閱與事務發布同步--http://msdn.microsoft.com/zh-cn/library/ms175006(v=SQL.100)exec sys.sp_addpushsubscription_agent @publication = N'tran_repl',--指定發布名稱@subscriber = N'KK',--訂閱伺服器@subscriber_db = N'mytestA',--訂閱資料庫@job_login = N'KK\SqlReplicator', @job_password = N'123456', @subscriber_security_mode = 1,--Windows 身分識別驗證@frequency_type = 64,--分發代理計劃的頻率：自動啟動(預設)@frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'go

/*【要在訂閱伺服器上啟動並執行指令碼】*/use mytestA--設定在串連到發行伺服器時立即更新訂閱的同步觸發器所使用的配置和安全資訊--http://msdn.microsoft.com/zh-cn/library/ms174991(v=sql.100).aspxexec sys.sp_link_publication @publisher = N'KK', @publisher_db = N'mytest', @publication = N'tran_repl', @distributor = N'KK', @security_mode = 1,--SQL Server 身分識別驗證或 Windows 身分識別驗證@login = N'KK\SqlReplicator', @password = N'123456'go

【啟動快照並初始化】

/**********************************【啟動快照並初始化】**************************************/--發行伺服器發布資料use [mytest]--對其提取訂閱中的新項目的訂閱添加到發布中（在發行伺服器的發行集資料庫中執行）--https://technet.microsoft.com/zh-cn/library/ms181680(v=sql.100).aspx--exec sys.sp_refreshsubscriptions @publication = N'tran_repl'--將訂閱標記為要重新初始化--https://msdn.microsoft.com/zh-cn/library/ms189469(v=sql.100).aspxexec sys.sp_reinitsubscription @publication = N'tran_repl', @subscriber = N'KK', @destination_db = N'mytestA', @article = N'all'go--啟動可為發布產生初始快照集的快照代理作業（在發行伺服器的發行集資料庫中執行）--http://msdn.microsoft.com/zh-cn/library/ms176026(v=sql.105).aspxexec sys.sp_startpublication_snapshot @publication = N'tran_repl'go--為所有表項目編寫自訂 sp_MSins、sp_MSupd 和 sp_MSdel 過程的指令碼--https://msdn.microsoft.com/zh-cn/library/ms187946(SQL.100).aspx--exec sys.sp_scriptpublicationcustomprocs 'tran_repl'--go--至此,完成發布訂閱!!!~ 初始化完成!!~

之前測試了N遍！！！~~今晚有空終於成功處理了！~