skip to content »

Sql server replication queued updating

You could write your own application to call the agents directly outside of SQL Agent, but then you would lose the monitoring support built into Enterprise Manager.

Updatable subscriptions including immediate updating and queued updating with snapshot and transactional publications.Each publication consists of one or more articles, which are the database objects including tables, views, stored procedures, and user-defined functions that the publisher server will publish to the subscribers.A push subscription executes a SQL Agent job on the distributor or publisher and sends the data to the subscriber, while a pull subscription executes a SQL Agent job on the subscriber to retrieve the data.Before you settle on the type of replication you want in your application architecture, it is important to understand how each type of replication works and what agents they use.Since the different types of replication use different combinations of replication agents, understanding what those agents do helps clarify the resulting SQL Agent jobs when you install replication.Once you've defined the publication, you can set up one or more subscriptions to the publication from one or more servers.

The steps you take in defining a publication vary depending on the type of replication you choose.

Use snapshot replication to make a point-in-time distribution of a publication's articles, with all the defined objects and data, to one or more subscribers.

The distributed data is a copy of those database's objects as they exist at a certain point in time, and hence the term 'snapshot.' Each time the snapshot replication process runs, a new snapshot containing the objects and data is sent to the subscriber.

-- Enable Replication exec sp_replicationdboption @dbname = N'my Sql Replication', @optname = N'publish', @value = N'true' GO -- Adding the transactional publication exec sp_addpublication @publication = N'my Sql Replication Pub', @description = N'Transactional publication of database ''my Sql Replication'' from Publisher ''hostname''.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'false', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, -- @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 0, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'true' GO exec sp_addpublication_snapshot @publication = N'my Sql Replication Pub', @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 = null, @job_password = null, @publisher_security_mode = 1 --Add the table to be replicated exec sp_addarticle @publication = N'my Sql Replication Pub', @article = N'table_1', @source_owner = N'dbo', @source_object = N'table_1', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, -- @identityrangemanagementoption = N'manual', @destination_table = N'table_1', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbotable_1', @del_cmd = N'CALL sp_MSdel_dbotable_1', @upd_cmd = N'SCALL sp_MSupd_dbotable_1' GO --add all of the columns to the article exec sp_articlecolumn @publication = N'my Sql Replication Pub' ,@article = N'table_1' ,@refresh_synctran_procs = 1 GO --Add the subscriber, the error is coming when running sp_addsubscription exec sp_addsubscription @publication = N'my Sql Replication Pub', @subscriber = N'mysql-pc', --change to subscriber hostname @destination_db = N'mysql Db', --change to subscriber db name @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 1 exec sp_addpushsubscription_agent @publication = N'my Sql Replication Pub', @subscriber = N'mysql-pc', --change to subscriber hostname @subscriber_db = N'mysql Db', --change to subscriber db name @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'mssql', --change @subscriber_password = '', --change @subscriber_provider = N'MSDASQL', @subscriber_datasrc = N'my SQLdsn', --change @frequency_type = 64, @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 = 20110922, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' GO I believe this has something to do with the schema_option of sp_addarticle.

If you go look at the possible values for this, 0x08 deals with timestamps.

Ron Talmage is a mentor and co-founder of Solid Quality Mentors.