We have transactional replication set up on our production database. Two publications point to the same database and share one Log Reader Agent. The two pull subscriptions also share one Distribution Agent, whose schedule is set to continuously. We want to create a third publication that points to the same database. This publication contains articles that change less frequently, so we want to update them only once a day. However, this publication will use the shared Log Reader Agent and the Distribution Agent that's scheduled continuously. How can we create individual Log Reader and Distribution Agents for each publication that point to the same database?
You can't create an individual Log Reader Agent because SQL Server allows only one Log Reader Agent per transactionally replicated database. SQL Server can't clear the log until all published changes have been pulled out and put into the distribution database, so running the Log Reader Agent continuously is a good idea to prevent excessive log growth. However, SQL Server does permit one Distribution Agent per publication. You can create a separate Distribution Agent for your third publication after you create the publication. To create the Distribution Agent, right-click the publication, then select Properties. Go to the Subscription Options tab and select the Use a distribution agent that is independent of other publications from this database option.
When you create a subscription through Enterprise Manager, both the pull and push options let you set a specific replication schedule, so you can implement your own less-frequent schedule specifically for the third subscription. Note that many installations need to retain a record of changes against production systems. This requirement can be hard to meet when you run a GUI tool such as Enterprise Manager directly against the production database, so if you want to retain the scripts for change-control purposes, simulate the action in a nonproduction environment. Then, you can use SQL Server Profiler to capture the SQL code that Enterprise Manager uses and modify the code to match the needs of your production environment.
