SQL Server replication additional subscriber

Question

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?

Answer 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.

Answer 2

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.

Answer 3

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.