2

I have a publisher with the distributor on the same server which is transactional replication. There is also an active subscriber using push method.

I need to set a new subscriber up going to a different server using the same articles as the current subscriber I have. The database is huge and cannot be taken offline so I was going to do an initialize from backup instead of a new snapshot.

If I execute exec sp_addsubscription on the publisher do I still need to run the command exec sp_addpushsubscription_agent as well? Will this create 2 distribution agents on the publisher or can only 1 be used.

Also will any more agents be created adding this extra subscriber?

3 Answers 3

1

Every subscriber needs it's own distribution agent.

With transactional replication, there are three different types of agents involved, and each type of agent has a different "scope".

  • Log Reader Agent -- One per published(replicated) database -- This agent reads the transaction log, and inserts commands for replication into the distribution database.
    • If you create the first publication in a database, you will need to set up a new log reader agent.
  • Snapshot Agent -- One per publication -- This agent creates the initial snapshot used to initialize new subscribers.
    • If you create a new publication, you will need to set up a new Snapshot Agent.
  • Distribution Agent -- One per subscriber/publication combination -- This agent delivers replication commands from the distribution database to the subscriber database.
    • If you create a new subscription, you will need to set up a new Distribution Agent.

So, if you have one published database, with two publications, and each publication has two subscriptions, then you'll have a total of four distribution agents. Even if there is overlap in having multiple subscriptions go into the same database--that doesn't change the number of distribution agents.

So yes, create a new distribution agent, but you won't need to create any additional agents, since it is not a new publication.

1

The database is huge and cannot be taken offline so I was going to do an initialize from backup instead of a new snapshot.

While initialize from backup is appropriate for this scenario, it's not strictly necessary. You can create the replication snapshot from a Database Snapshot which uses copy-on-write sparse files to create a point-in-time consistent view of a database with no locking or blocking on the source database. To do this specify "database snapshot" as the @sync_method of sp_add_publication.

Initialization from a snapshot is required if the subscribers have tables other than those replicated from the publisher as re-initializing from a backup would wipe those out.

0

If I execute exec sp_addsubscription on the publisher do I still need to run the command exec sp_addpushsubscription_agent as well?

Yes, you need to execute the sp_addpushsubscription_agent right after sp_addsubscription. if not, you will not be able to replicate the data to your new subscriber db.

  1. sp_addsubscription will only add the new subscription on your existing publication.
  2. sp_addpushsubscription_agent will create a new distribution agent job for your new subscription.

The database is huge and cannot be taken offline so I was going to do an initialize from backup instead of a new snapshot.

You can add the new big subscriber db to the existing publication without doing the re-initialization (by initialize from lsn). Check the replication blog post of Jonathan Kehayias. Even though the blog post was for migration/upgrade, it can also be use for adding a new subscription to the existing publication. Make sure to test in lower environment before doing it in production.

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.