SQL Server uses scripts to create updatable subscriptions for distribution services and transactional replication

"Create using local Distributor"

/************************ "Publishing with local distributor configuration" ***********************/--sqlserver r2--https:// technet.microsoft.com/zh-cn/library/ms151860 (v=sql.105). Aspxuse mastergo--The Distributor is installed on the server--https:// msdn.microsoft.com/zh-cn/library/ms190339 (v=sql.105). aspxexec master.dbo.sp_get_distributorgo--Configuring the Distributor--https:/ /msdn.microsoft.com/zh-cn/library/ms176028 (v=sql.105). aspxexec master.dbo.sp_adddistributor @distributor = ' KK '--distributor name, @heartbeat_interval = The maximum number of minutes that the 10--agent can run without logging progress messages, @password = N ' [email protected] '-- Distributor Password go--Configure the distribution database--https://msdn.microsoft.com/zh-cn/library/ms189755 (v=sql.105). Aspxexec master.dbo.sp_ Adddistributiondb @database = n ' distribution '--the name of the distribution database to be created, @data_folder = N ' E:\TempFile\Distribution '-- The directory of the distribution database data file, @data_file = n ' distribution '--the name of the database file, @data_file_size = 5--Initial data file size, in megabytes (MB), @log_folder = N ' e:\ Tempfile\distribution '----distribution database log file directory, @log_file = N ' Distribution_log ', @log_file_size = 5--Initial log file size, in megabytes (MB), @ Min_distretention = 0--The minimum retention period before a transaction is removed from the distribution database,In hours, @max_distretention = 72--The maximum retention period before a transaction is deleted, in hours, @history_retention = 48--history, in hours, @security_mode = The security mode at which to connect to the Distributor when synchronizing. The default value is 1:windows authentication, 0:sql authentication, @login = N ' kk\sqlreplicator ', @password = N ' 123456 ', @createmode = 1--1: Create or use an existing database ( INSTDIST.SQL) go--Configure the Publisher to use the specified distribution database--https://msdn.microsoft.com/zh-cn/library/ms173807 (v=sql.105). aspxexec Master.dbo.sp_adddistpublisher @publisher = n ' KK '--name of the publisher, @distribution_db = n ' distribution '--name of the distribution database, @security_ mode = Safe Mode, default 1:windows authentication, 0:sql authentication, @login = N ' kk\sqlreplicator ', @password = N ' 123456 ', @working_directory = N ' E:\Temp File\repldata '--the UNC shared directory of the default snapshot folder, @thirdparty_flag = 0--Whether the publisher is SQL Server, default 0: Yes, 1: no, @publisher_type = N ' MSSQLSERVER ' --Publisher Type: MSSQLSERVER (default)/oracle/oracle gatewaygo/***** configuration complete!! *****/

/***** "View distribution Properties" ******/--Check distribution database directory exec master.dbo.xp_subdirs N ' E:\TempFile\ Distribution '--the properties of the publisher (performed at the Distributor on any database)--https://technet.microsoft.com/zh-cn/library/ms190323 (v=sql.105). Aspxexec master.dbo.sp_helpdistpublisher N ' KK '--the properties of the distribution database (performed at the Distributor's distribution database)--https://msdn.microsoft.com/zh-cn/ Library/vstudio/aa238917.aspxexec master.dbo.sp_helpdistributiondb N ' distribution '-- Lists information about the Distributor (performed at the distributor in any database)--https://msdn.microsoft.com/zh-cn/library/ms177504 (v=sql.105). aspxexec Master.dbo.sp_helpdistributor

/***** "Delete Distribution" ******/--no need to execute here! --Delete the distribution publisher (performed at the Distributor on any database)--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--Check object, connection distribution;-- Delete the distribution database (executed at the Distributor for any database)--https://msdn.microsoft.com/zh-cn/library/ms188355 (v=sql.105). Aspxexec master.dbo.sp_ Dropdistributiondb N ' distribution ';--Unload the Distributor (executed in any database except the distribution database)--https://technet.microsoft.com/zh-cn/library/ ms173516 (v=sql.105). aspxexec Master.dbo.sp_dropdistributor @no_checks = 0, @ignore_distributor = 0-check object; connection distribution;

"Create a transactional publication for updatable subscriptions"

/************************************* "Create publication" **************************************//* "instance: Updatable Subscriptions" * A is the publication database, The ID is the primary key for each table * B,c to the synchronization of the eligible [ID% 2 = 0] in the subscription database * A to a in B that matches the condition [id% 2 = 1] to C * The following to create a publication subscription to the current publication database: [MyTest] The current publication database: [My TestA] Publish name, (publication database name), (subscription database name), filter condition */--as published database use [mytest]--Set replication database option for specified database (publisher or subscriber execution)--http:// Msdn.microsoft.com/zh-cn/library/ms188769.aspxexec sys.sp_replicationdboption @dbname = N ' mytest ', @optname = n ' publish ', @value = N ' true ' go--add Queue Reader Agent for a given distributor (executed in the distribution library or publishing library) (only 1 per instance default, already exists and can no longer be executed)--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--Adds the Log Reader Agent (executed at the publication database) for a given database (only 1 per database default, which already exists and can no longer be executed)--http://technet.microsoft.com/ zh-cn/library/ms189516.aspxexec sys.sp_helplogreader_agent @publisher = null--View the log Agent exec sys.sp_ that exists in the current database Addlogreader_agent @job_login = n ' kk\sqlreplicator ', @job_password = N ' 123456 ', @publisher_security_mode = 1, @job_Name = nullgo/*************************** "Create publication: The following for 1 library multiple publications" ******************************/-- Add (updatable subscription) transactional publication (executed at publication database)--http://msdn.microsoft.com/zh-cn/library/ms188738 (v=sql.100). Aspxexec sys.sp_ Addpublication @publication = n ' tran_repl ',--"specifies the publication name" @description = N ' A transactional publication with updatable subscriptions from the database "MyTest" of the publisher ". ', @sync_method = N ' concurrent ',--synchronous mode: Native mode bulk copy program output @retention = 0,--Subscription activity retention period (hours): Default value is 336 hours; 0: Subscription never expires @allow_push = N ' true ',--push subscription @allow_pull = N ' true ',--allow create pull Subscription @allow_anonymous = N ' false ',--cannot create an anonymous subscription @enabled_for_internet = N ' false ' ,--Non-internet Publishing @snapshot_in_defaultfolder = N ' false ',--do not specify a snapshot default folder, you must set @alt_snapshot_folder@alt_snapshot_folder = N ' E : \tempfile\repldata ',--Specify the location of the alternate folder for the snapshot @compress_snapshot = N ' false ',--do not compress the snapshot @ftp_port =,--The port number of the FTP service for the default distributor: 21@FTP _login = n ' anonymous ',--Default user name to connect to the FTP service: Anonymous@allow_subscription_copy = N ' false ',--disable replication Subscribe to this publication's subscription database @add_to_ Active_directory = N ' false ',--(deprecated) @repl_freq = n ' continuous ',--the type of replication frequency: output of log-based transactions @status = N ' Active ',-- Publish data can be used immediately for subscribers @independeNt_agent = N ' true ',--independent Distribution Agent @immediate_sync = N ' false ',--no synchronization files are created for the publication each time the Snapshot Agent runs @allow_sync_tran = N ' true ',--allow use of " Update subscription Now "@autogen_sync_procs = N ' true ',--" synchronous stored procedure "that generates an update subscription at the publisher @allow_queued_tran = N ' true ',--queue enabled for change at the subscriber @allow_ DTS = n ' false ',--do not allow data conversion @conflict_policy = N ' Sub wins ',--The conflict resolution policy that is followed when queued updating subscriber options: "Subscription wins" @centralized_conflicts = N ' True ',--store conflict Records at the publisher @conflict_retention =,--conflict retention period (days) @queue_type = N ' sql ',--the type of queue used: Default SQL Server storage transaction @replicate_ddl = 1 ,--"Support schema Replication" @allow_initialize_from_backup = N ' false ',--do not allow subscriptions to be initialized with a backup @enabled_for_p2p = N ' false ',--non-peer copy @enabled_for_ Het_sub = N ' false '--only supports SQL Server Subscriber go--to create Snapshot Agent for the specified publication (executed at the publication database)--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--Change publication Properties--https://msdn.microsoft.com/zh-cn/library/ms188413 (v=sql.105). Aspx--exec sys.sp_changepublication ... ...

--Create the project and add it to the publication (executed at the publication database)--http://msdn.microsoft.com/zh-cn/library/ms173857/*1. Add a filterable table (default schema dbo) */declare @tableNamenvarchar (+) declare @publNamenvarchar (DECLARE) @markbit--distinguish between columns that have SIDS, Filter declare @filterNumnvarchar (10)--a database of multiple publications plus a number difference declare @filterClausenvarchar () DECLARE @ Sqladdarticlenvarchar (max) declare @SQLarticlefilternvarchar (max) declare @SQLarticleviewnvarchar (max) Set @publName = N ' tran_repl '--"Specify publication name" Set @filterClause = N ' dbo.f_sidtoint (SID)% 2 = 0 '--"Specify Publication name" Select @filterNum = CONVERT (NVARCHAR (ten), COUNT (*)) from Distribution.dbo.MSpublicationsdeclare cur_addtable cursor local fast_forwardfor/* has a primary key and has a SID column ( For filtering) */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/* has a primary key and no SID column (not filterable) */ Select Name,0 mark from Sys.tables T1 (nolock) where is_ms_shipped = 0and no exists (select 1 from sys.columns T2 (NOLOCK) where T1.obje ct_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)/* filterable Table object */beginset @SQLaddarticle = N ' exec sp_addarticle @p ublication = N ' ' [email protected]+ ', @article = n ' [email protected]+ ', @source_owner = n ' dbo ', @ source_object = N ' ' [email protected]+ ', @type = N ' logbased ', @description = null, @creation_script = NULL, @pre_ Creation_cmd = n ' Drop ', @schema_option = 0x0000000008035cdf, @identityrangemanagementoption = N ' None ', @destination _table = n ' [email protected]+ ', @destination_owner = n ' dbo ', @status = @vertical_partition = N ' false ' EXEC (@SQLaddarticle)/* Add Project Filter */set @SQLarticlefiLter = n ' exec sp_articlefilter @publication = N ' ' [email protected]+ ', @article = N ' ' [email protected]+ '] , @filter_name = N ' fltr_ ' [email protected]+ ' _ ' [email protected]+ ' __ ' +rtrim (LTrim (str (@ @spid))) + ', @ filter_clause = N ' ' [email protected]+ ', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 ' EXEC (@ Sqlarticlefilter)/* Add Project Synchronization object */set @SQLarticleview = n ' exec sp_articleview @publication = N ' ' [email protected]+ ' ', @article = N ' ' [email protected]+ ', @view_name = n ' sync_ ' [email protected]+ ' _ ' [email protected] + ' __ ' +rtrim (LTrim (str (@ @spid)) + ", @filter_clause = N" "[email protected]+", @force_invalidate_snapshot = 1 , @force_reinit_subscription = 1 ' exec (@SQLarticleview) print ' Filtered: ' [email protected]endelse if (@mark = 0) beginset @SQLaddarticle = n ' exec sp_addarticle @publication = N ' ' [email protected]+ ', @article = N ' ' [email  Protected]+ ', @source_owner = n ' dbo ', @source_object = n ' [email protected]+ ', @type = N ' logbased ', @description = null, @creation_script = null, @pre_creation_cmd = n ' Drop ', @schema_option = 0x0000000008035cdf, @identityrangemanagementoption = n "None", @destination_table = n "' [email  Protected]+ ', @destination_owner = n ' dbo ', @status =, @vertical_partition = N ' false ' EXEC (@SQLaddarticle) print ' No filter: ' [Email protected]endfetch next from Cur_addtable to @tableName, @markendclose cur_addtabledeallocate cur_ AddTable

/*2. Add view/Stored procedure/Function object (default schema dbo) */declare @publNamenvarchar (DECLARE) declare @Typenvarchar (30) DECLARE @ObjectTypenvarchar () declare @SQLaddObjectnvarchar (max) Set @publName = N ' Tran_repl '--"Specify publication name" declare Cur_ AddObject cursor Local fast_forwardfor Select Name,type from mytest.sys.objects where type in (N ' P ') and is_ms_shipped = 0u Nion Allselect Name,type from Mytest.sys.objects a where type in (N ' V ') and is_ms_shipped = 0and exists (select 1 from Mytes T.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.sy S.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.objec  t_id and Is_schema_bound = 1)/* Schema-bound indexed view */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 to @ObjectName, @ObjectTypewhile @ @fe Tch_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 ' ' [email protected]+ ', @article = n ' [email protected]+ ', @source_owner = n ' dbo ', @source_object = N ' ' [email protected]+ ', @type = N ' ' [email protected]+ ', @description = null, @creation_script = null, @pre_creation_cmd = N ' drop ', @schema_option = 0x0000000008000001, @status = +, @destination_ow NER = n ' dbo ', @destination_table = N ' ' [email protected]+ ' EXEC (@SQLaddObject) print @ObjectType + ': ' + @ Objectnamefetch next from Cur_addobject to @ObjectName, @ObjectTypeendclose cur_addobjectdeallocate cur_addobject

"Create subscription"

/*********************************** "Create subscription" ***************************************//* "script to run at publisher" */use [ mytest]--adds a subscription to the publication and sets the status of the subscriber-(Warning: Distribution Agent job is created implicitly and will run under the SQL Server Agent service account)--http://technet.microsoft.com/ zh-cn/library/ms181702 (v=sql.100). aspxexec sys.sp_addsubscription @publication = N ' Tran_repl ',--specify the publication name @subscriber = N ' KK ',--subscriber @destination_db = N ' mytesta ',--subscription database @subscription_type = n ' push ',--push subscription @sync_type = n ' automatic ',--default, The schema and initial data of the published table are transferred first to subscribers @article = n ' all ',--publish subscribed items @update_mode = n ' queued failover ',--to enable subscriptions as queued updating subscriptions, and allow change to immediate update mode @subscriber_type = 0--Subscriber Type: SQL Server subscriber go--Add a new scheduled Distribution Agent job to synchronize the push subscription with the transactional publication--http:// msdn.microsoft.com/zh-cn/library/ms175006 (v=sql.100) exec sys.sp_addpushsubscription_agent @publication = N ' Tran_ Repl ',--Specify the publication name @subscriber = N ' KK ',--subscriber @subscriber_db = N ' mytesta ',--subscription database @job_login = n ' kk\sqlreplicator ', @job_ Password = N ' 123456 ', @subscriber_security_mode = 1,--Windows Authentication @frequency_type =,--The frequency at which the Distribution Agent schedules: auto-start (default) @frequency_ Interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interva L = 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

/* Script to run at Subscriber */use mytesta--sets the configuration and security information that is used to update the subscription's synchronization triggers immediately when connecting to the publisher--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 authentication or Windows authentication @login = N ' kk\sqlreplicator ', @passw Ord = N ' 123456 ' Go

"Start snapshot and Initialize"

/********************************** "Start snapshot and initialize" **************************************/--Publisher publish data use [mytest]-- A subscription to a new item in its pull subscription is added to the publication (executed at the publisher's publication database)--https://technet.microsoft.com/zh-cn/library/ms181680 (v=sql.100). Aspx--exec sys.sp_refreshsubscriptions @publication = N ' Tran_repl '--marks the subscription to be reinitialized--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--start the Snapshot Agent job (executed at the publisher's publication database) that generates the initial snapshot for the publication--http://msdn.microsoft.com/ zh-cn/library/ms176026 (v=sql.105). aspxexec sys.sp_startpublication_snapshot @publication = N ' Tran_ Repl ' go--scripts to write custom Sp_msins, SP_MSUPD, and sp_msdel procedures for all table items--https://msdn.microsoft.com/zh-cn/library/ms187946 (SQL.100 ). Aspx--exec sys.sp_scriptpublicationcustomprocs ' Tran_repl '--go--to this point, complete the Publish subscription!!! ~ Initialization complete!! ~

Tested n times before!!! ~ ~ Tonight is free finally successfully processed! ~

SQL Server uses scripts to create updatable subscriptions for distribution services and transactional replication