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

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

"Create using local Distributor"

/************************ "Publishing with local distributor configuration" ***********************/--SQL Server R2--https:// technet.microsoft.com/zh-cn/library/ms151860 (v=sql.105). aspx use master go---whether the Distributor is installed on the server--Https://msdn.micro soft.com/zh-cn/library/ms190339 (v=sql.105). aspx exec master.dbo.sp_get_distributor Go--Configuring the Distributor--HTTPS://MSDN.M icrosoft.com/zh-cn/library/ms176028 (v=sql.105). aspx exec master.dbo.sp_adddistributor @distributor = ' kk-pc '--distribution service  Name, @heartbeat_interval = 10--The maximum number of minutes the agent can run without logging progress messages, @password = N ' 123456 '--distributor password Go--Configure the distribution database-- https://msdn.microsoft.com/zh-cn/library/ms189755 (v=sql.105). aspx exec 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 ' distributio N '--the name of the database file, @data_file_size = 5--The initial data file size, in megabytes (MB), @log_folder = N ' E:\TempFile\Distribution '---- Directory of distribution database log files, @log_file = N ' Distribution_log '  , @log_file_size = 5-The initial log file size, in megabytes (MB), @min_distretention = 0-The minimum retention period, in hours, before a transaction is removed from the distribution database , @max_distretention = 72-the maximum retention period before a transaction is deleted, in hours, @history_retention = 48--The history retention time, in hours, @security_mod E = 1--The Safe mode of connecting to the distributor when synchronizing. The default value is 1:windows authentication, 0:sql authentication, @login = N ' kk-pc\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) . aspx exec master.dbo.sp_adddistpublisher @publisher = n ' kk-pc '--the name of the publisher, @distribution_db = n ' distribution '--Name of the distribution database, @security_mode = 1--Safe mode, default 1:windows authentication, 0:sql authentication, @login = N ' Kk-pc\sqlreplicator ', @pa ssWOrd = N ' 123456 ', @working_directory = N ' E:\TempFile\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 GATeWAY go/***** 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-pc '--the properties of the distribution database (executed 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-pc ', @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 subscription database: [My TestA] */--as a published database use [mytest]--Sets the replication database option for the 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 the Queue Reader Agent for a given distributor (executed in the distribution library or the 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-pc\sqlreplicator ', @job_password = N ' 123456 ', @job_name = null, @frompublisher = 1go--adds the Log Reader Agent for a given database (performed at the publication database) (only 1 per database default, already exists and no need to execute)--http://technet.microsoft.com/zh-cn/library/ ms189516.aspxexec sys.sp_helplogreader_agent @publisher = null--View the log Agent exec sys.sp_addlogreader_agent that exists in the current database @job_ Login = N ' kk-pc\sqlreplicator ', @job_password = N ' 123456 ', @publisher_security_mode = 1, @job_name = nullgo/*******"Create publication: The following for 1 library multiple Publications" ******************************/--Add (Updatable subscriptions) transactional publications (performed at the publication database)--http:// msdn.microsoft.com/zh-cn/library/ms188738 (v=sql.100). aspxexec sys.sp_addpublication @publication = N ' Tran_repl ',-- "Specify publication name" @description = N ' Transactional publication with updatable subscriptions from database "MyTest" from 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 ',--"Stand-alone Distribution Agent" @immediate_sync = N ' false ',--do not create a synchronization file for the publication each time the Snapshot Agent runs @allow_sync_tran = N ' true ',--allows the use of immediate updating subscriptions @autogen_sync_ procs = N ' true ',--"synchronous stored procedure" that generates an update subscription at the publisher @allow_queued_tran = N ' true ',--the 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 record at Publisher @conflict_retention =,--conflict retention period (days) @queue_type = N ' sql ',--queue type used: Default SQL Server storage transaction @replicate_ddl = 1,--" Support for schema Replication "@allow_initialize_from_backup = N ' false ',--not allowed to initialize subscription with backup @enabled_for_p2p = N ' false ',--non-peer copy @enabled_for_het_ Sub = N ' false '--supports only 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-pc\sqlreplicator ', @job_password = N ' 123456 ', @publisher_security_mode = 1go--Change publication Properties--h ttps://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 Row filter" Select @filterNum = CONVERT (NVARCHAR ( ), 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) WH ere 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 NOT EXISTS (select 1 from sys.columns T2 (NOLOCK) where T1.OBJEC t_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&Nbsp;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-pc ',--subscriber @destination_db = N ' mytesta ',--subscription database @subscription_type = n ' push ',--push subscription @sync_type = n ' Automatic ',-- By default, the schema and initial data for published tables 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-pc ',--subscriber @subscriber_db = N ' mytesta ',--subscription database @job_login = n ' kk-pc\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_subda Y_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

/* 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-pc ', @publisher_db = N ' mytest ', @publication = N ' tran_repl ', @distributor = N ' kk-pc ', @security_mode = 1,--SQL Server authentication or Windows authentication @login = N ' kk-pc\sqlreplicator ', @password = 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-pc ', @ 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