This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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?
asked 2021-09-15 by Mearsy
Answer
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.
answered 2021-09-16 by Andy Mallon