SQL Server 2005 Transactional Replication Agents
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Some of my previous articles discussed transactional replication setup, monitoring, troubleshooting, and maintenance with SQL Server 2005. This article explores the inner workings of replication agents: security, properties, and profiles. I will also introduce you to a few system procedures for managing replication agents programmatically.
If you need a refresher on what replication agents are and options available with previous versions of SQL Server please be sure to reference my previous article, "SQL Server Transactional Replication Agents."
This article will highlight the improvements introduced with SQL Server 2005 and teach how you can use them to your advantage.
Agent Security
Recall that replication agents are manifested through SQL Server Agent jobs, but they are simply executable files, so they can be called from the command line. Transactional replication uses snapshot, log reader, and distribution agents; if you allow queued updating subscribers, you will also employ the queue reader agent.
With previous versions of SQL Server, your replication agents typically ran under the SQL Server Agent account. In a common scenario, replication moves transactions across machines, not between databases on the same machine. In such cases, the SQL Server Agent must be running under the context of a domain (not local) account. Alternatively, you can use SQL Server authentication for connecting to publisher, distributor and subscriber servers.
With SQL Server 2005, replication agents’ security works similarly; you can still impersonate SQL Server Agent or specify a SQL Server account to be used for connecting to servers involved in replication. In addition, you can also specify a Windows account other than the account used for running SQL Server Agent. Why is this important? Well, it’s not uncommon to see environments in which SQL Server Agent runs under the domain administrator context for various reasons; replication agents do not need domain administrator permissions to perform their activities. The new option enables you to be more serious about security and to run the SQL Server Agent under an account with only those privileges necessary for implementing replication.
For example, the snapshot agent needs to have permissions to connect to the snapshot folder and read and write data within this folder. You can then create a Windows account that is a local administrator on the publisher but has no permissions at all on the subscriber and use it for running the snapshot agent. Similarly the account running the distribution agent for a pull subscription needs to have permissions to connect to the distributor, but doesn’t need permissions for connecting to the publisher. You can specify the Windows account used to run a particular replication agent within the Agent Security dialog box that follows.
If you change your mind and want to impersonate SQL Server Agent instead of using a Windows account, you can change your selection within the job step properties. Open the job step that runs the agent and choose SQL Agent Service Account in the Run As drop-down list, as shown in the following figure.