Merge Replication Updates on Initialise (SQLServer 2005)

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?

I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

Hi Steve,

When you initialize the client, you are probably using dynamic snapshot, and when those dynamic snapshots become old the initializing will also need to get the incremental changes which happened after the old dynamic snapshot generated.

You can choose to have the snapshots refreshed on a schedule so new Subscribers that subscribe to a partition for which a snapshot has been created will receive an up-to-date snapshot.

For more information, please refer to Books online topic: Snapshots for Merge Publications with Parameterized Filters

Hope it helps.

Wanwen

|||I assume you mean the snapshot agent running regularly - which it does every night.

Seems to be something else - I'll just have to keep looking|||

If you are using dynamic filtering, there are 2 snapshots to consider:

1. Regular snapshots

2. Dynamic snapshots

The regular snapshot do not have data in them.

The dynamic snapshots are the ones that have data in them.

You need to make sure that both are run before your subscriber syncs so that they can use the bcp files from the dynamic snapshot to complete the sync process in a much more timely manner (faster).

sql

Merge Replication Agent Process is blocikgn other users

Hi,
I have Merge replication on SQL 2000 since last 6 months.
Recently I have notice that process is blocing other users and than timeouts on its own. Query Timeout is 600.
This started recently and I'm trying to track this down. Any clue or suggestion to this problem I have.
Thanks
Nilay
Nilay,
to find out more information about the cause of your blocking issues you
might like to use the MS scripts:
http://support.microsoft.com/default...;en-us;271509. If it is
simply a matter of having a lot of data modifications being synchronized
then you need to optimize the merge agent. You could:
increase -DownloadGenerationsPerBatch
make sure -MetadataRetentionCleanup is 1 and even run
sp_mergemetadataretentioncleanup manually
run the merge agent more frequently, so as to not accumulate changes
decrease the -PollingInterval if you are running continuously
increase -QueryTimeOut
Alternatively, if you accept a high latency, you might run the merge agent
out of hours.
HTH,
Paul Ibison
|||Thanks This helps