Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

SQL Server 2000 Replication 101: Replication Agents : Page 3

Replication agents are SQL Agent jobs that often invoke external executables to do the work of the agent. Understanding how replication agents work is invaluable for understanding replication as a whole.


advertisement
Replication Agents: Overview
SQL Server 2000 comes with five major replication agents as well as a number of other miscellaneous agents for monitoring and cleanup. You can see the agents listed under the Replication Monitor/Agents node in Enterprise Manager. Each major replication agent is a separate executable located in the ..\Program Files\Microsoft SQL Server\80\COM folder. The major agents, along with the executables they call, include:

  • Snapshot Agent (snapshot.exe)
  • Distribution Agent (distrib.exe)
  • Log Reader Agent (logread.exe)
  • Queue Reader Agent (qrdrsvc.exe)
  • Merge Agent (replmerg.exe)
Miscellaneous agents call replication stored procedures located in the distribution database. SQL Server installs SQL Agent jobs that invoke the miscellaneous agents when you first configure replication, even before you define any publications. SQL Server's miscellaneous agents include:

  • Agent history clean up: distribution (sp_MShistory_cleanup)
  • Distribution clean up: distribution (sp_MSdistribution_cleanup)
  • Reinitialize subscriptions having data validation failures (sp_MSreinit_failed_subscriptions)
  • Replication agents checkup (sp_replication_agent_checkup)
SQL Server's three types of replication all use the miscellaneous agents. Table 1 shows you how the three types of replication use the major agents.

Table 1: Each major replication agent works with one or more of the types of replication.

Replication Agent

Snapshot replication



Transactional replication

Merge replication

Snapshot

Yes

Yes

Yes

Distribution

Yes

Yes

No

Log reader

No

Yes

No

Queue reader

No

Yes (optional)

No

Merge

No

No

Yes


In both transactional and merge replication, if the subscriber becomes significantly out of date, that is, it expires, SQL server will renew the subscription by generating a new snapshot for the subscriber.
In addition to the executables, there is a set of ActiveX controls associated with each major agent that you can use to create custom replication applications. You can find out more about these controls and their object models in SQL Server Books Online under the topic, "Replication ActiveX Control Reference."

Table 1 shows that all three types of replication use the Snapshot Agent. Both snapshot replication and transactional replication use the Distribution Agent, but transactional replication also uses the Log Reader Agent to read transactions from the publisher's database. Merge replication only requires Merge Agents in addition to the Snapshot Agent.

Also, note how similar snapshot and transactional replication are: transactional replication has two additional agents, but otherwise uses the same agents as snapshot.

The Snapshot Agent
The Snapshot Agent, a three-step SQL Agent job, does the work of generating a snapshot. The agent generates the schema and data files for the publication and places them in a snapshot folder. The SQL Agent job step that calls the snapshot.exe executable does most of the work. The other two steps update replication history tables.

The Distribution Agent
Both snapshot and transactional replication use the Distribution Agent?a three-step SQL Agent job that accesses the snapshot folder and applies the schema and data to the subscribers if they have not been initialized or need re-initialization. If you choose a push type of snapshot or transactional subscription, then the Distribution Agent will run on the distributor. If you choose a pull type of subscription, the Distribution Agent resides on the subscriber.

The Log Reader Agent
Transactional replication is the only type of replication that uses the Log Reader Agent. The Log Reader Agent, also a three-step SQL Agent job, calls the logread.exe executable and updates some history tables as well. The logread.exe executable reads the transaction log of the publisher database for transactions that have been marked for replication.

The Queue Reader Agent
SQL Server only uses the Queue Reader Agent when you add queued updating subscribers to transactional replication. The Queue Reader Agent runs on the distributor and reads either a queue stored in a table on the subscriber, or a Microsoft Message Queue.

The Merge Agent
The Merge Agent resides on either the publisher or subscriber server in a merge replication implementation. The Merge Agent applies the snapshot of data to the subscribers, so merge replication does not need a Distribution Agent. The Merge Agent's most important role is to read the merge system tables and apply and reconcile the recorded changes between the publisher and its subscribers. In a push subscription, the Merge Agent resides on the publisher; in a pull subscription, the Merge Agent resides on the subscriber.

Merge replication is more complex than either snapshot or transactional replication. It has a number of features that the others lack such as dynamic filtering, which allows you to filter the replicated data based on information that the subscriber sends when its Merge Agent connects to the publisher. Accordingly, the Merge Agent executable has more parameters than the other agents.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date