Changes not replicated to subscriber
Hi,
I hope you can help me with this.
I am setting up a tranasctional publication with updateable subscriptions using:
use [LIMS_PUBLISHER_TEST]
exec sp_replicationdboption @dbname = N'LIMS_PUBLISHER_TEST', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [LIMS_PUBLISHER_TEST]
exec sp_addpublication @publication = N'LIMS_PUBLISHER_TEST', @description = N'Transactional publication with updatable subscriptions of database ''LIMS_PUBLISHER_TEST'' from Publisher ''RIVIERA''.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = N'\\RIVIERA\SNAPSHOT', @compress_snapshot = N'true', @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'true', @autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'LIMS_PUBLISHER_TEST', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @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
use [LIMS_PUBLISHER_TEST]
exec sp_addarticle @publication = N'LIMS_PUBLISHER_TEST', @article = N'AUD_AUTHORISED_TEST', @source_owner = N'dbo', @source_object = N'AUD_AUTHORISED_TEST', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000080314DF, @identityrangemanagementoption = N'manual', @destination_table = N'AUD_AUTHORISED_TEST', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false'
GO
I am adding a pull subscription with immediate with queued failover updating using:
/*at the publisher*/
use [LIMS_PUBLISHER_TEST]
exec sp_addsubscription @publication = N'LIMS_PUBLISHER_TEST', @subscriber = N'RIVIERA', @destination_db = N'LIMS_SUBSCRIBER_2', @sync_type = N'Replication Support Only', @subscription_type = N'pull', @update_mode = N'failover'
GO
/*at the subscriber*/
use [LIMS_SUBSCRIBER_2]
exec sp_addpullsubscription @publisher = N'RIVIERA', @publication = N'LIMS_PUBLISHER_TEST', @publisher_db = N'LIMS_PUBLISHER_TEST', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'failover', @immediate_sync = 1
exec sp_link_publication @publisher = N'RIVIERA', @publication = N'LIMS_PUBLISHER_TEST', @publisher_db = N'LIMS_PUBLISHER_TEST', @security_mode = 1, @login = null, @password = null
exec sp_addpullsubscription_agent @publisher = N'RIVIERA', @publisher_db = N'LIMS_PUBLISHER_TEST', @publication = N'LIMS_PUBLISHER_TEST', @distributor = N'RIVIERA', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = '', @enabled_for_syncmgr = N'False', @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 = 20051117, @active_end_date = 99991231, @alt_snapshot_folder = N'\\RIVIERA\TEMP', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
The publisher, subscriber and distributor all sit on the same server at present.
I can update a table at the publisher and the changes are replicated to the subscriber, I can update at the subscriber and the changes are replicated to the publisher. After a small number of updates the subscriber falls out of sync despite replication monitor saying the changes has been propagated from the publisher to the distributor and on to the subscriber.
If I run the subscription in a queued failover mode- all works well but unfortunately I need immediate updating as the primary method with queued failover.
What am I missing?
Thanks,
Barney
