SQL Server uses scripts to create updatable subscriptions for distribution services and transaction replication, and SQL server scripts

SQL Server uses scripts to create updatable subscriptions for distribution services and transaction replication, and SQL server scripts

[Create a local distributor]

/************************ [Use local distribution server configuration release ]******* */-- SqlServer 2008 R2 -- https://technet.microsoft.com/zh-cn/library/ms151860 (v = SQL .105 ). aspxuse mastergo -- whether the distributor is installed on the server -- https://msdn.microsoft.com/zh-cn/library/ms190339 (v = SQL .105 ). aspxexec master. dbo. sp_get_distributorgo -- configure the distributor -- https://msdn.microsoft.com/zh-cn/library/ms176028 (v = SQL .105 ). aspxexec master. dbo. sp_adddistributor @ distri Butor = 'kk '-- distributor name, @ heartbeat_interval = 10 -- the maximum number of minutes that a proxy can run without recording progress messages, @ password = n' 201card113 @ app' -- distributor password go -- configure distributor database -- https://msdn.microsoft.com/zh-cn/library/ms189755 (v = SQL .105 ). aspxexec master. dbo. sp_adddistributiondb @ database = n' distribution '-- Name of the Distribution database to be created, @ data_folder = n' E: \ TempFile \ distribution' -- directory of the Distribution database data file, @ data_file = n' distribution '-- Name of the database file, @ data_file_size = 5 -- Initial data file size, in MB (MB), @ log_folder = n' E: \ TempFile \ Distribution '---- directory for distributing database log files, @ log_file = n' distribution _ log ', @ log_file_size = 5 -- Initial log file size, in MB, @ min_distretention = 0 -- minimum retention period before deleting a transaction from the distribution database, in hours, @ max_distretention = 72 -- maximum retention period before the transaction is deleted, in the unit of hour, @ history_retention = 48 -- retention time of historical records, in the unit of hour, @ security_mode = 1 -- security mode connecting to the distributor during synchronization. The default value is 1: Windows verification, 0: SQL verification, @ 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 Publishing Server, @ distribution_db = n' distribution' -- Name of the distribution database, @ security_mode = 1 -- security mode; default value: 1: windows verification, 0: SQL verification, @ login = n' KK \ SqlReplicator', @ Password = n' 123456 ', @ working_directory = n' E: \ TempFile \ repldata' -- default: UNC shared directory of the snapshot folder, @ thirdparty_flag = 0 -- whether the publisher is SQLServer, default Value: 0: Yes, 1: No, @ publisher_type = N 'mssqlserver '-- publisher type: MSSQLSERVER (default)/ORACLE GATEWAYgo/***** the configuration is complete !! *****/

/***** [View distribution attributes] ******/-- check the distribution database directory exec master. dbo. xp_subdirs N 'e: \ TempFile \ Distribution '-- attributes of the publisher (executed in any database on the distributor) -- https://technet.microsoft.com/zh-cn/library/ms190323 (v = SQL .105 ). aspxexec master. dbo. sp_helpdistpublisher N 'kk '-- attributes of the Distribution Database (executed on the Distribution Database of the distribution server) -- https://msdn.microsoft.com/zh-cn/library/vstudio/aa238917.aspxexec master. dbo. sp_helpdistributiondb N 'distribution '-- lists information about the distributor (executed in any database on the distributor) -- https://msdn.microsoft.com/zh-cn/library/ms177504 (v = SQL .105 ). aspxexec master. dbo. sp_helpdistributor

/***** [Delete distribution] ******/-- this is not required! -- Delete a distributor (executed in any database on the distributor) -- 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 Distribution Database (executed in any database on the distribution server) -- https://msdn.microsoft.com/zh-cn/library/ms188355 (v = SQL .105 ). aspxexec master. dbo. sp_dropdistributiondb N 'distribution '; -- uninstall 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 updatable transaction Publishing]

/************************************ [Create publishing ]************************************** // * [instance: updatable subscription] * A is the Publishing Database and id is the primary key of each table * B, c: Synchronize [id % 2 = 0] that meets the condition in the subscription database * A to B * synchronize that meets the condition [id % 2 = 1] to C * below create a release and subscribe to the current database as B: [mytest] currently released Database: [mytestA] Release name, (Release Database Name), (subscription database name ), filter condition */-- as the published database use [mytest] -- set the replication database option for the specified database (executed by the publisher or subscription server) -- http://msdn.microsoft.com/zh-cn/library/ms188769.aspxexec sys. sp_replicationdboptio N @ dbname = n'mytest', @ optname = n'publish ', @ value = N 'true' go -- add a Queue Reader Agent (executed in the distribution database or release database) to the given distribution server (each instance has only one by default, there is no need to execute again) -- 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 -- add a log reader agent to a given database (executed in the published database) (each database has only one by default and already exists and does not need to be executed) -- http://technet.microsoft.com/zh-cn/library/ms189516.asp Xexec sys. sp_helplogreader_agent @ publisher = null -- view the log agent exec sys in the current database. sp_addlogreader_agent @ job_login = n' KK \ SqlReplicator ', @ job_password = n' 123456', @ publisher_security_mode = 1, @ job_name = nullgo/**************************** [Create a release: the following code is used to publish multiple databases: ****************************/-- add (updatable subscription) transaction Publishing (executed in the Publishing Database) -- http://msdn.microsoft.com/zh-cn/library/ms188738 (v = SQL .100 ). aspxexec sys. sp_addpublication @ Publication = n'tran _ repl ', -- [specify release name] @ description = n' refers to a transaction release with updatable subscription from the Database "mytest" of the Publishing Server. ', @ Sync_method = n' concurrent', -- synchronization mode: Local Mode large-capacity replication program output @ retention = 0, -- subscription activity retention period (hours): The default value is 336 hours; 0: subscription will never expire @ allow_push = N 'true', -- push subscription @ allow_pull = N 'true', -- allow creation of request subscription @ allow_anonymous = N 'false ', -- you cannot create an anonymous subscription @ enabled_for_internet = n'false', -- non-Internet publication @ snapshot_in_defaultfolder = n'false', -- the default snapshot folder is not specified, @ alt_snapshot_folder = n'e: \ TempFile \ ReplData 'must be set, -- specify the location of the backup folder of the snapshot @ compress_snapshot = N'false', -- Do not compress the snapshot @ ftp_port = 21, -- the default distribution server's FTP service port number: 21 @ ftp_login = n'anonus us ', -- Default User name used to connect to the FTP service: anonymous @ allow_subscription_copy = N 'false', -- Disable copying and subscribing to the published subscription database @ add_to_active_directory = n'false ', -- (not recommended) @ repl_freq = N 'continuous ', -- replication frequency type: Log-based transaction output @ status = N 'active ', -- publish data can be immediately used by the subscription server @ independent_agent = n'true', -- [independent distribution Agent] @ immediate_sync = n'false ', -- do not create a synchronization file for the release every time you run the snapshot agent @ allow_sync_tran = N 'True', -- allow [Update Subscription now] @ autogen_sync_procs = n'true ', -- generate the [synchronization Stored Procedure] @ allow_queued_tran = N 'true' for the updated subscription on the Publishing Server. -- enable the changed queue @ allow_dts = n'false' on the subscription server ', -- data conversion not allowed @ conflict_policy = N 'sub wins ', -- Conflict Resolution policy followed when the subscription server option is updated in queue: [subscription selected] @ centralized_conflicts = N 'true ', -- store the conflict record @ conflict_retention = 14 on the Publishing Server, -- conflict retention period (days) @ queue_type = N 'SQL', -- queue type used: the default SQL Server storage transaction @ replicate_ddl = 1, -- [supports schema replication] @ allow_initialize_from_backu P = n'false', -- backup initialization and subscription are not allowed @ enabled_for_p2p = n'false ', -- Non-peer replication @ enabled_for_het_sub = N 'false' -- only SQL Server subscription Server go is supported -- create snapshot proxy for the specified release (executed in the release 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, @ users = 0, @ users = 0, @ frequency_subday = 0, @ fr Equency_subday_interval = 0, @ active_start_time_of_day = 0, @ timeout = 235959, @ active_start_date = 0, @ active_end_date = 0, @ job_login = n'kk \ SqlReplicator ', @ job_password = n' 123456 ', @ publisher_security_mode = 1go -- change publishing properties -- https://msdn.microsoft.com/zh-cn/library/ms188413 (v = SQL .105 ). aspx -- exec sys. sp_changepublication ......

-- Create a project and add it to the release (run in the release database) -- http://msdn.microsoft.com/zh-cn/library/ms17#/#1. add a table that can be filtered (default architecture dbo) */declare @ tableNamenvarchar (100) declare @ publNamenvarchar (100) declare @ markbit -- to identify columns with sid, if yes, filter declare @ filterNumnvarchar (10) -- add numbers to multiple releases in a database and add the difference declare @ filterClausenvarchar (100) declare @ SQLaddarticlenvarchar (max) declare @ sqlarticlefilternvarch) declare @ SQLarticleviewnvarchar (max) set @ publName = n'tran _ repl '-- [specify the release name] set @ filterClause = n'dbo. f_SIDTOInt (SID) % 2 = 0' -- [specify release name] select @ filterNum = CONVERT (NVARCHAR (10), count (*) from distribution. dbo. MSpublicationsdeclare cur_addTable cursor local fast_forwardfor/* primary key and SID column (used 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 filtered) */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 = 0 beginif (@ mark = 1) /* Table objects that can be filtered */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) /* Add a project filter */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_subshot = 1' exec (@ SQLarticlefilter) /* Add a project synchronization object */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_subshot = 1' exec (@ SQLarticleview) print 'has been filtered: '+ @ 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 'no Filtering: '+ @ tableNameendfetch next from cur_addTable into @ tableName, @ markendclose cur_addTabledeallocate cur_addTable

/* 2. add view/stored procedure/function object (default architecture dbo) */declare @ publNamenvarchar (100) declare @ ObjectNamenvarchar (100) declare @ Typenvarchar (30) declare @ ObjectTypenvarchar (30) declare @ SQLaddObjectnvarchar (max) set @ publName = n'tran _ repl '-- [specify the release name] declare cur_addObject cursor local fast_forwardfor select name, type from mytest. sys. objects where type in (N 'P') and is_ms_shipped = 0 union 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. 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. object_id = B. object_id and is_schema_bound = 1)/* index view bound to the architecture */union allselect name, type from mytest. sys. objects where type in (N 'tf', N 'fn ') and is_ms_shipped = 0 order by type, nameopen cur_addObjectfetch next from cur_addObject into @ ObjectName, @ ObjectTypewhile @ fetch_status = 0 beginSET @ Type = (case when @ ObjectType = n' THEN n' view schema only 'when @ ObjectType = n' THEN n' indexed view schema only 'when @ ObjectType = n' 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

[Create subscription]

/*********************************** [Create subscription] ***************************************/ /* [script to run on the Publishing Server] */use [mytest] -- add the subscription to the Publishing Server and set the status of the subscription server -- (warning: distribution proxy job is created implicitly and will run under the SQL Server proxy service account) -- http://technet.microsoft.com/zh-cn/library/ms181702 (v = SQL .100 ). aspxexec sys. sp_addsubication @ publication = n' tran _ repl ', -- specify the release name @ subscriber = n' KK', -- subscribe server @ destination_db = n' mytestA ', -- subscribe Database @ subscription_type = n'push', -- Push subscription @ sync_type = n'ic IC ', -- default, the architecture and initial data of the published table will be first transmitted to the subscription server @ article = N 'all', -- publish the subscribed Project @ update_mode = n' queued failover ', -- enable subscription to queue for updating subscription, and allow change to immediate update mode @ subscriber_type = 0 -- subscription Server type: SQL Server subscription Server go -- Add a new scheduled distribution agent job, to synchronize the push subscription with the transaction release -- 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 ', -- subscribe to Database @ job_login = n' KK \ SqlReplicator ', @ job_password = n' 123456', @ subscriber_security_mode = 1, -- Windows Authentication @ frequency_type = 64, -- distribution agent plan frequency: Automatic Start (default) @ frequency_interval = 1, @ frequency = 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

/* Scripts to run on the subscription server */use mytestA -- sets the configuration and security information used by the synchronization trigger that updates the subscription immediately when connected 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 ', @ password = n' 123456' go

[Start and initialize a snapshot]

/*********************************** [Start and initialize the snapshot. ]**************************************/ -- use [mytest] for publishing data on the Publishing Server -- add the subscription of the new item in the request subscription to the Publishing Server (execute in the Publishing Database of the Publishing Server) -- https://technet.microsoft.com/zh-cn/library/ms181680 (v = SQL .100 ). aspx -- exec sys. sp_refreshsubscriptions @ publication = N 'tran _ repl '-- mark the subscription as re-initializing -- https://msdn.microsoft.com/zh-cn/library/ms189469 (v = SQL .100 ). aspxexec sys. sp_reinitsubatio @ publicatio N = n' tran _ repl ', @ subscriber = n' KK', @ destination_db = n' mytestA ', @ article = n'all' go -- start a snapshot proxy job that can generate initial snapshots for a release (executed in the release database of the Publishing Server) -- http://msdn.microsoft.com/zh-cn/library/ms176026 (v = SQL .105 ). aspxexec sys. sp_startpublication_snapshot @ publication = n' tran _ repl 'go -- scripts for custom sp_MSins, sp_MSupd, and sp_MSdel processes for all table projects -- https://msdn.microsoft.com/zh-cn/library/ms187946 (SQL .100 ). aspx -- exec sys. sp_scriptpublicat Ioncustomprocs 'tran _ repl '-- go -- now, publish and subscribe has been completed !!!~ Initialization is complete !!~

I tested it N times before !!!~~ Finally, we can handle it tonight !~