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

Source: Internet
Author: User
Tags mssqlserver rtrim

"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

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.