September 16, 2014 at 9:01 am
I have several publications - transactional replication, push to a couple of subscribers.
One of the subscribers is being physically moved to a new datacenter, i.e., will need to be shutdown and brought online after two days. No other change.
How do I handle replication?
I first did the following:
Right-click on subscriber, View Sync Status, and STOP
Then Replication Monitor shows all subscriptions running except the one to the moving server which I stopped.
The System Administrator panicked and insist I delete the subscription and recreate it once the server is online again;
his reasoning, he insisted that the log file on the publishing database will not get truncated and will grow until the stopped subscription is started and the subscriber synched.
Does this make sense??
Thanks,
September 16, 2014 at 12:11 pm
dagnea (9/16/2014)
...he insisted that the log file on the publishing database will not get truncated and will grow until the stopped subscription is started and the subscriber synched.Does this make sense??
Thanks,
That does make sense. See: Considerations for Transactional Replication.
Is that a bad thing? Provided that you have allocated enough space for your log, the subscriber is not down for too long and you're not dealing with millions of transactions not there should not be a problem. If you are dealing with a high volume of transactions then dropping and re-creating the subscription is probably the way to go. That is the approach I would take. Scripting out your entire replication setup is a nightmare but scripting out a subscription is not a big deal.
-- Itzik Ben-Gan 2001
September 16, 2014 at 12:22 pm
Thank you for your response. However, since there are multiple subscribers for the same publication, and there is only one log reader, and the subscribers are sharing the same distributor (db), the log reader is doing its job regardless of how many subscribers and once log is read and copied to dist db by dist agent, its the dist agent that applies the changes to the respective subscribers, correct?
So, once the data is in the dist db, the log should be truncated, correct?
Or, am I missing something?
September 16, 2014 at 12:43 pm
dagnea (9/16/2014)
his reasoning, he insisted that the log file on the publishing database will not get truncated and will grow until the stopped subscription is started and the subscriber synched.Does this make sense??
None at all.
If were taking down the distributor, then yes, the log would grow. You're not. You're moving one of the subscribers. The publisher won't even notice because it doesn't care whether or not there are any subscribers at all, if they're up, down or anything else
Transactional replication is publisher -> distributor then distributor -> subscriber.
Providing the period that the subscriber will be disconnected is less than the retention period for the replication, when the subscriber is reconnected it will catch up. If it's disconnected for longer then the subscription will be invalidated and will need to be re-snapshotted.
So, no. There's no grounds for the sysadmin's concerns. Providing the distributor is up and the log reader running, the log on the publisher will be properly truncated at the usual points.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply