3

Assuming I have a transactional publication with a single article. There is a Publisher (PUB1) with two Subscribers (SUB1 AND SUB2), and a separate Distributor (DIST1) box is used. I would like to stop replication to one subscriber (SUB1), while the other continues to process commands (SUB2). Additionally, I would like to be able to start replication back up to SUB1 and have it pick up where it left off.

Based on this MSDN page, I would have Snapshot, Log Reader, and Distribution agents. I know I can stop the Distribution agent for SUB1 only and SUB2 will continue to get/process replication commands.

The thing that is unclear is what happens to the commands destined for SUB1. From my test, the commands seem to be applied when I start the Distribution agent. I'm concerned that they could get deleted from DIST1 after a set amount of time, even though all subscribers have not received the commands (yet).

So would the commands build up on DIST1 until both SUB1 and SUB2 are up to date and running, or is there the potential for commands to be lost? Also, is there a point at which I can't simply start the Distribution agent back up (i.e. does SQL Server decide at a certain point that it must re-initialize the articles in order to start synchronizing again)?

5

Yes, the transactional replication data will build up while waiting for distribution to start again. Of course, if not tracked it is possible that a long delayed set of transactional replication data can fill the log space that you have available, thus causing you problems.

Replication is hostage to the resources available, including the log space since the replication data is read from the transaction log.

For what it is worth, we once kept a replication open for a couple of days until a network problem could be resolved. (Plenty of log space at that time.)

If the replication process fails (for any reason) that would cause loss of data then you need to redo the steps to establish the replication landscape.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.