We are currently doing a proof of concept using SQL 2005 merge replication. However we are currently encountering the following errors:
"The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up."
"The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3."
We can reproduce the errors by doing the following:
- Setup merge replication between server and client.
- We generate a snapshot on the server and apply it on the pull subscriber
- Synchronization the client. At this point everything is being replicated
- We then update a record on the subscriber
- Before synchonizing we generate a new snapshot on the server and use the "upload changes from the subscriber before applying snapshot" option.
- We then synchronize the subscriber which successfully uploads the changes to the server and applies the snapshot (with the old data) to the subscriber. In other words at this point the subscriber now has the snapshot data and the server has the updated data.
- SQL replication then gives us the errors described above.
Since there are multiple subscribers we cannot regenerate the snapshot at this point to return the replication process to normal operation because we could encounter the same issues with other subscribers while doing so.
Does anyone know how to solve this Is this possibly a bug in SQL 2005
< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Watermark Generation is Invalid Error
Ashok Bodddeda
Oozle_Finch
Hi,
Eden and I are working together on the proof of concept with SQL 2005 replication.
Our solution will be 1 master standard SQL 2005 with more than a 100 web synch merge pull subscription SQL express subscribers. Each subscriber will only downlaod its specific slice of data, as per a dynamic data filter. The replication process work fine, but at spesific times, like 2 times a year we will be doing bulk uploading of data at the master server, which we have seen just create to much transactional data to synch afterwards with normal merge replication, thus our approach have been to pause replication durting that period of bulk changes and then afterwards set the publication to "Reinitialise All Subscriptions", but we used the option to first upload the changes from the subscriber before applying the new snapshot, ie we do not want to loose the changes that might have been done on the subscriber while there link to the main server have been down. Because in our scenario, All subscribers will never connected simultationsly, thus we have to let them connect at there schedule and bring there changes to the master server and then retrieve the new snapshot and download and apply the changes.
I am trying to post the scripts but its not working, I get a forum error, please advise me on how to get it to you/......
Regards
Gert Cloete
stand__sure
I am also running into the same issue. I will gladly contribute any info to help solve this.
Thanks
irsprint
rockronie
Hi sorry , but that was the only way that I could get the contents of the scripts posted.....
We are gladly awaiting your feedback
Regards
G
Siriwat
thanks gertus for the wealth of information
. Now that we have the scripts and that you mention web sync, it might give us a better chance at trying to reproduce the problem. We'll let you know shortly.
masterbuyerseller
sami27913
I am trying to reproduce the issue from this posting, and have yet to run into this error. At step 5 above you seem to be reinitializing the subscription to the pull subscriber Is this the intention If you did reinitialize, did you reinitialize all subscriptions to the publication or just this particular pull subscription Could you please provide more details around the last three steps. Providing more details about the publication and articles may be helpful as well.
Are you testing reinitialize subscription If not, you could skip step 5 and the merge agent will automatically upload changes to the publisher first resulting in the publisher and subscriber being in sync without reinitializing the subscription. In other words, the merge agent will process all delta changes between the publisher and subscriber by default and not require a snapshot to be regenerated.
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
Michael Dyrnaes
--Pull Subscription
use
[ConcordPOC]exec
sp_addmergepullsubscription @publisher = N'OBDB01', @publication = N'ConcordPOC 0.2', @publisher_db = N'CONCORDPOC', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @sync_type = N'Automatic'exec
sp_addmergepullsubscription_agent @publisher = N'OBDB01', @publisher_db = N'CONCORDPOC', @publication = N'ConcordPOC 0.2', @distributor = N'OBDB01', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'True', @frequency_type = 4, @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, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'remote_user', @publisher_password = N'', @use_interactive_resolver = N'False', @dynamic_snapshot_location = N'', @use_web_sync = 1, @internet_url = N'https://obweb01/concordpoc/replisapi.dll', @internet_login = N'gcloete', @internet_password = null, @internet_security_mode = 0, @internet_timeout = 3600, @hostname = N'OB001'GO
svincoll4
- Adding the merge articles
use
[ConcordPOC]exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'CCOOffice', @source_owner = N'dbo', @source_object = N'CCOOffice', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'HostName = HOST_NAME()', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 2, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
use
[ConcordPOC]exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'SurveyQuestion', @source_owner = N'dbo', @source_object = N'SurveyQuestion', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 2, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
use
[ConcordPOC]exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'PollingDistrict', @source_owner = N'dbo', @source_object = N'PollingDistrict', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 2, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
use
[ConcordPOC]exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'User', @source_owner = N'dbo', @source_object = N'User', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 2, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
Anthony Cook
use [ConcordPOC]
exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'Contact', @source_owner = N'dbo', @source_object = N'Contact', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000000, @identity_range = 1000000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
use
[ConcordPOC]exec
sp_addmergearticle @publication = N'ConcordPOC 0.2', @article = N'SurveyResponse', @source_owner = N'dbo', @source_object = N'SurveyResponse', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000000, @identity_range = 1000000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0GO
-- Adding the merge article join filters
use
[ConcordPOC]exec
sp_addmergefilter @publication = N'ConcordPOC 0.2', @article = N'PollingDistrict', @filtername = N'PollingDistrict_CCOOffice', @join_articlename = N'CCOOffice', @join_filterclause = N'[CCOOffice].[CCOOfficeID] = [PollingDistrict].[CCOOfficeID]', @join_unique_key = 1, @filter_type = 1, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1GO
use
[ConcordPOC]exec
sp_addmergefilter @publication = N'ConcordPOC 0.2', @article = N'Contact', @filtername = N'Contact_PollingDistrict', @join_articlename = N'PollingDistrict', @join_filterclause = N'[PollingDistrict].[PollingDistrictID] = [Contact].[PollingDistrictID]', @join_unique_key = 1, @filter_type = 1, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1GO
use
[ConcordPOC]exec
sp_changemergepublication N'ConcordPOC 0.2', N'status', N'active'GO
-- Adding the merge subscriptions
use
[ConcordPOC]exec
sp_addmergesubscription @publication = N'ConcordPOC 0.2', @subscriber = N'OB001\SQLEXPRESS', @subscriber_db = N'ConcordPOC', @subscription_type = N'Pull', @sync_type = N'Automatic', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @use_interactive_resolver = N'False'GO
-- Adding merge partitions for the publication
use
[ConcordPOC]exec
sp_addmergepartition @publication = N'ConcordPOC 0.2', @suser_sname = N'', @host_name = N'OB001'GO
-- Adding dynamic snapshot jobs for the publication
GO
use
[ConcordPOC]exec
sp_adddynamicsnapshot_job @publication = N'ConcordPOC 0.2', @suser_sname = N'', @host_name = N'OB001'Junrei
--RMO Application Messages
xterminal
--Publication script1
-- Enabling the replication database
use
masterexec
sp_replicationdboption @dbname = N'ConcordPOC', @optname = N'merge publish', @value = N'true'GO
-- Adding the merge publication
use
[ConcordPOC]exec
sp_addmergepublication @publication = N'ConcordPOC 0.2', @description = N'Merge publication of database ''ConcordPOC'' from Publisher ''OBDB01''.', @sync_mode = N'native', @retention = 4, @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'\\OBDB01\ReplDataComp', @compress_snapshot = N'true', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'true', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @validate_subscriber_info = N'HOST_NAME()', @max_concurrent_merge = 3, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'true', @publication_compatibility_level = N'90RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'true', @allow_web_synchronization = N'true', @web_synchronization_url = N'https://obweb01/concordpoc/replisapi.dll', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0GO
exec
sp_addpublication_snapshot @publication = N'ConcordPOC 0.2', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'openbox\gcloete', @job_password = null, @publisher_security_mode = 1exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'sa'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'NT AUTHORITY\SYSTEM'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'BUILTIN\Administrators'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'OPENBOX\gcloete'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'OBDB01\SQLServer2005SQLAgentUser$OBDB01$MSSQLSERVER'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'OBDB01\SQLServer2005MSSQLUser$OBDB01$MSSQLSERVER'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'remote_user'GO
exec
sp_grant_publication_access @publication = N'ConcordPOC 0.2', @login = N'distributor_admin'GO
Caga