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

Source: Internet
Author: User
Tags mssqlserver rtrim safe mode

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.