I have a database in merge replication with about 25 subscribers, all accessing the publisher/distributor over a satellite connection (high latency, low bandwidth). Recently, as part of a planned system upgrade, each subscriber was taken offline, had its data merged with the publisher, then was deleted. The publisher was then updated, and each subscriber was to be recreated.

Possibly relevant info:

-Only about 50 of the 130 tables are being replicated. Of those 50, about 20 are marked as Download Only to Subscriber.

-To deploy the subscribers, I first use SSMS to generate scripts to create the publisher database schema, then run that script on each subscriber.

-I created a parameterized script to create the subscription on each subscriber. (In other words, the exact same script was run on each subscriber to create the database schema and subscription.)

-The plan is that the subscribers will be populated with data during initialization.

-Snapshot Agent job is run daily and is compressed to a .cab file in an alternate pickup directory.

-All Agents and SQL Server instances are running under the same domain service account.

-SQL Server 2012 Standard on all servers

Now for my issue:

On 20 of the subscribers, the initialization happened without any issues (although a couple took several attempts due to connection issues). Within 6 hours, the 20 subscribers were initialized and synchronizing hourly. Each sync ends with about 300-500 combined inserts, updates, and deletes. Each of the subscribers returned a message after initialization similar to: Applied the snapshot and merged 0 data change(s) (0 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).

5 of the subscribers were offline at the time, so they could not be initialized for about a week. During that week, the system was in use, and snapshots were being generated nightly.

When the remaining 5 subscribers were brought online and attempted initialization they did not initialize in the same way that the prior 20 had. Instead, each of them is downloading mass amounts of changes on top of the bulk inserts. In some cases, the changes are 2 or 3 times the number of bulk inserts that were originally copied to the subscriber. Instead of the ~6 hours that the original 20 took, these 5 can take upwards of 5-6 days, and given the slow/bad connection, they have failed every attempt to initialize.

The 20 subscribers that worked are still doing fine, so I do not believe the publication setup or method used is faulty, but maybe there is some hidden or non-obvious reason why the remaining subscribers are behaving as they are?

  • Is the snapshot being repeatedly applied to the 5 problematic subscribers? – Brandon Williams Mar 31 '16 at 17:12
  • The subscribers are initialized from a snapshot that is manually generated just before their attempted initialization (maybe a few minutes gap between generation and starting the initial sync). As they are taking a long time to initialize, there is usually a new snapshot generated by the scheduled job (runs nightly at 1:00am) during their initialization. So the next attempt at initialization will be from a different snapshot than the prior. However, the behavior of the many, many changes being applied in addition to the initial bulk insert begins happening even on the initial snapshot. – Agbullet Mar 31 '16 at 17:40
  • Did you ever perform data validation on the 5 problematic subscribers prior to recreating the subscriptions? – Brandon Williams Mar 31 '16 at 17:44
  • On the publisher run this query, select * from sysmergeschemachange where schematype = 66. Does it return any rows? – Brandon Williams Mar 31 '16 at 17:47
  • We have not performed any data validations, as prior to the upgrade cycle, those subscribers were performing nominally. The query you posted returned 0 rows. – Agbullet Mar 31 '16 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.