I need to add a new subscriber with snapshot and transactional replication and I'm trying to determine if I need to add the subscriber after-hours or not.

This particular database is already being replicated on a different server, so there is an existing publication.

  1. Does adding a subscriber to an existing publication generate a new snapshot?
  2. If not, will the publisher DB have any locks while the subscriber is being created?

Thank you for your help.

1 Answer 1

  1. No it directly does not, but a new snapshot needs to eventually be generated in case the data or schema has changed since the last snapshot was generated before replication begins for that subscriber, and regardless a snapshot needs to be initialized in the new Subscriber database as a starting point for further transactions that occur. BUT it doesn't have to happen at the point that you add the Subscriber. There's an option you can select for when to initialize and instead of selecting "Immediately" you can select "Manual". Then at your convenience (off-hours if needed) you can right click on the Publication and manually generate a new snapshot for that subscriber.

  2. The locks on the Publisher from initializing a snapshot should be rather minimal as replication is a fairly efficient feature of SQL Server. But the part that adds the actual Subscriber doesn't cause any locks to the Publisher.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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