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 !~