SQL Server 2005 - specific roles for replication agents
Whenever the replication is configured, a set of agent profiles is installed on the Distributor, thsi profile will have a set of parameters that are used each time an agent runs. In addition to this the replication provides a default profile for each agent and additional predefined profiles for the Log Reader Agent, Distribution Agent, and Merge Agent. Also there are few components out there in working together to replicate data from one SQL Server to another, they are replication agents.
SQL Server 2005 version has got 5 agents associated having them reside in distributor and subscriber. Inside the SQLEngine for Replication jobs run by SQL Server Agent service, so it is very important to ensure SQLAgent service is up and running in order to continue the Replication processes. If you dig deep further into Operating System path for SQL Server under C:\Program Files\SQL Server\90\COM directory you will see these executables for distribution agent (distrib.exe), log reader agent (logread.exe) and queue reader agent (qrdrsvc.exe) and so on.
Further process of these agent such as snapshot agent will take snapshot of schema and data for first time replication, it generates snapshot file and put entry in distribution database relating to the file on the Distributor instance. IN any case ensure that SQL Server Agent service account has full permission on these folders. Also the other important agent such as log reader agent will read transaction log of publishers and put the replicated entry into distribution database, important component for transactional replication. With this type of Transactional Replication the queue reader agent will have a job for queue of updateable transactional replication, this is important for conection between publisher and subscriber that will not have any continuous uptime. In this case the queue agent will keep the changes in subscriber in local queue, when there is connection established between them, queue reader agent will take the log from the entry and applies to publishers, this resides on Distributor side. The functionality for queue reader agent is similar to MSDTC service and you have to ensure this service is up and running for immediate updating subscriptions.
In case of push subscriptions distributor agent has important role to play for replicated data from publisher to subscriber. A bit of change you see is if you choose push subscription then the distributor agent will reside distributor server, and for pull subscriptions you will see it in subscriber server.
Similar to them Merge agent is import for merge replication that works similar fashion to distributor agent, the process include apply snapshot and subsequent changes from publisher to subscriber, and perform conflict resolution. Books Online refers the security permissions for the replication required where the authentication level necessary for common replication setup tasks:
| Setup task |
Membership requirement |
|
Enable a Distributor, Publisher, or Subscriber. |
sysadmin server role on the Publisher. |
|
Enable a database for replication. |
sysadmin server role on the Publisher. |
|
Create a publication. |
db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher. |
|
View publication properties. |
Member of the PAL at the Publisher, db_owner database role on the publication database at the Publisher, or sysadmin server role on the Publisher. |
|
Create a subscription. |
db_owner database role on the publication database at the Publisher or sysadmin server role on the Publisher.
db_owner database role on the subscription database at the Subscriber or sysadmin server role on the Subscriber. |
|
Configure agent profiles. |
sysadmin server role on the Distributor. |
By default a READER privileged user can view replication activity, errors, and history using Replication Monitor. In order to modify the agent profile or schedule that user must have SYSADMIN privilege on Distributor side. In the next series of this blog I will continue with Replication Security best practices.