Replication Agents

Replication Agents

In a replication environment, there are seven replication agents that handle tasks such as moving data from the Publisher to the Distributor and then from the Distributor to the Subscriber. Replication agents are replication processes that enable SQL Server to perform the different types of replication processes talked about in the preceding section. Each replication process contains a minimum of three agents. These agents are used to move data along to the Publisher and Subscriber via the Distributor and keep the entire replication process running smoothly. The following list provides a summary of the agents:

• SQL Server agent. The SQL Server agent hosts and schedules all other agents used in a replication process as well as provides an easy way to run replication agents.

• Log Reader agent . Located on the Distributor, the Log Reader agent's job is to monitor the transactional logs of published databases and copy new transactions onto the Distributor.

• Distribution agent. The Distribution agent moves the transactions from the Distributor to the Subscriber.

• Snapshot agent . The Snapshot agent is responsible for creating the snapshot files on the Distributor.

• Merge agent . The Merge agent converges records from multiple sites and resolves any conflicts in merge replication.

• Queue Reader agent . The Queue Reader agent is used when "update queue" has been enabled and runs on the Distributor.

• Miscellaneous agents . Cleanup agents listed under the Miscellaneous Agents folder in Replication Monitor complete scheduled and on-demand maintenance of replication.

Each agent plays an important part in replication, although not every agent is used in all types of replication. Each of the agents involved directly in the replication process are discussed in more detail in the next several sections.

The Snapshot Agent

The Snapshot agent helps in preparation of the schema and initial data files of published tables and stored procedures. Not only does the Snapshot agent deal with snapshot replication, but it also helps in merge and transactional replication, as seen in Figure 11.7.

Figure 11.7. Role of Snapshot agent.

Note that before incremental changes can be sent to a Subscriber, tables need to be created on the Subscriber. This is done through the receiving of scripts, which are run at the Subscriber to create the necessary structure. The Snapshot agent also runs BCP to export the initial copy of the data and store this in files. All replication types begin with a snapshot. Each publication created has its own Snapshot agent, which runs at the Distributor. The Snapshot agent can be managed and configured easily using SQL Server Enterprise Manager.

The Log Reader Agent

The Log Reader agent moves transactions from the transaction log of the published database on the Publisher to the distribution database or server, as shown in Figure 11.8.

Figure 11.8. Log Reader agent.

The Log Reader agent is used only in transactional replication. The Log Reader agent moves transactions that are marked for replication.

The Distribution Agent

The Distribution agent moves transactions and snapshot jobs held in the distribution database out to the Subscribers; it can be administered using the SQL Server Enterprise Manager. The Distribution agent can run on either the Distributor's server or the Subscriber's server. It is not required to run on the Distributor only, which is new functionality in SQL 2000 called Remote Activation. Merge publications do not have a Distribution agent at all; rather, they rely on the Merge agent, which is discussed in the following section.

The Merge Agent

The Merge agent moves the initial snapshot jobs found in the publication database tables to the Subscriber as seen in Figure 11.9. Each Merge publication has a Merge agent that connects to the publishing server and the subscribing server, updating both.

Figure 11.9. Role of the Merge agent.

In a complete merge scenario, the Merge agent first uploads all changes from the Subscriber to the Publisher. Changes are then brought over from the subscribing machines and applied to the Publisher. The Merge agent is also responsible for resolving conflicts that can occur at the row or column level. Whether or not conflicts are diagnosed at the row/column level depends upon the configuration. In row conflicts, changes were made to the data for a single record. In a column configuration, the conflict does not occur unless the data changed is within the same field as well as the same data record. The agent resolves conflicts by reversing the process and downloading any changes needed back to the Subscribers. The Merge agent is used only in merge replication, so snapshot and transactional publications do not use them whatsoever.

The Queue Reader Agent

The Queue Reader agent is responsible for taking messages from a queue and applying them to the appropriate publication. This agent is used only if Immediate Queued Update is defined as either a main or failover replication process.

Only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given Distributor.

A lot of programmatic pieces all work together to form a replication scenario. Along with software, hardware and network infrastructure all participate in this activity. Each type of replication performs differently and provides different challenges. Now it's time to look into the specifics of implementation for each of the replication types.