Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2000 Replication 101: Replication Agents : Page 4

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Figure 4: When you define a publication, you must first choose the publication database.
Replication Agents in Action: Defining a Snapshot Publication
To see a replication agent at work, let's take a simple example of defining a snapshot publication on a single server. In this example, you'll define a publication consisting of the authors table in the pubs database. To begin, make sure that you have the pubs sample database with the authors table on your SQL Server. If you don't, you can substitute another table on another database accordingly in the following steps. We'll first configure the server for replication. In Enterprise Manager, from the main menu choose Tools, then choose Replication, and then select the "Configure Publishing, Subscribers, and Distribution" option. In the Configure Publishing and Distribution wizard, make your server its own distribution server. Choose the default location for the snapshot folder. Use the default settings for publisher and distributor. Because you are replicating all on the same server, the wizard will automatically enable your server as a distributor, publisher, and subscriber.

Now let's set up a simple snapshot replication and examine the two resulting agents. Defining the Publication

Figure 5: Defining a publication also requires that you specify the publication articles.
In Enterprise Manager, you define a publication by choosing the Tools menu, choosing Replication, and then selecting "Create and Manage Publications." In the opening dialog, choose the pubs database and click Create Publication. You'll next see Create Publication Wizard dialog box. Click Next and you'll see the Choose Publication Database dialog box as shown in Figure 4. Make sure you have selected the pubs database, then click Next. In the Select Publication Type dialog box, choose Snapshot replication and click Next. In the Specify Subscriber Types dialog box, choose SQL Server 2000 because this replication example takes place on one instance of SQL Server 2000.

The next dialog box, Specify Articles, lets you choose the tables, views, stored procedures, and functions that you want to replicate, as shown in Figure 5. Choose the authors table. Note that there are no user-defined functions in the pubs database, so you don't see them listed as an option in the left pane.

Figure 6: You can modify some of the table articles properties of a snapshot publication.
Now click the Browse button (the icon with three dots) to the right of the authors table. The resulting Article Properties dialog box has two tabs. On the General tab you could change the owner and table name on the subscriber database. Leave these as the defaults and click the Snapshot tab. Here you can see further options for what objects to send during the snapshot, as shown in Figure 6. By default, SQL Server will send the primary key and clustered index on the table, but not the other indexes, triggers, and referential integrity (foreign key) constraints. You can also send extended properties of the object, as well as the collations on individual columns. If you were expecting any of these objects and they didn't show up, you can return to this properties dialog after replication is set up and modify them.

Click OK to accept the default settings and then click Next. In the next series of dialogs, give the publication a name of PubsSnapshot, accept the default properties, and click the Finish button. You have now defined a publication on the publisher server. Inspecting the Snapshot Agent
Now drill down in Enterprise Manager to the Replication Monitor, opening the tree control to the Agents node, and then select the Snapshot Agents node. You'll see the Snapshot "agent" in the right pane. Right-click on the agent and choose Agent Properties from the pop-up menu. You'll see the SQL Agent job that implements the Snapshot Agent. If you click the Steps tab, you'll see the Snapshot Agent's job steps, as shown in Figure 7.

Figure 7: The Snapshot Agent job steps are shown by the Snapshot Agent properties, and the second step runs the snapshot executable.
Select the second job step, Run Agent, and click Edit. This will launch the Edit JobStep dialog box, shown in Figure 8. In the Command field you will see how SQL Agent calls the Snapshot Agent executable. Scroll the navigation bar to the right and you can see the parameters for the call to snapshot.exe. The snapshot.exe executable has three required and 24 optional parameters. SQL Server uses the optional parameters to specify any values other than the default values. When SQL Server uses the Snapshot Agent for snapshot replication, the ReplicationType parameter will be blank. When SQL Server uses the Snapshot Agent in transactional replication, the parameter value of the ReplicationType will be 1; and in merge replication, the value of the ReplicationType parameter will be 2.

Cancel out of these dialog boxes and right-click again on the Snapshot Agent and choose Agent Profiles. You'll see the Agent Profiles dialog box that will show you the default profile for this Snapshot Agent. Click View Details to inspect the options for this. As you can see in Figure 9, some additional parameters for the snapshot are stored in the registry. These parameters control the bulk copy extract from the publisher database: the batch size, query timeout, and so on. The Importance of Agents
You've now seen how defining snapshot replication results in one agent, the Snapshot Agent, and how SQL Server implements it as a SQL Agent job. When you subscribe to the publication, you'll notice the addition of a Distribution Agent. Using similar steps to those defined in the previous example, you can figure out how to view or modify any agent's properties and its profiles. You can modify properties and profiles to inspect, troubleshoot, and tune replication behavior. If you just keep in mind that when you choose any type of replication, the action taking place behind the scenes is actually done by a set of replication agents, you'll have mastered an important step in understanding how replication really works.

Figure 8. The Snapshot Agent's second step makes a call to snapshot.exe with the parameters contained in the Command box.
Figure 9. You can examine a Snapshot Agent's default profile to see additional parameters for the snapshot executable.

Ron Talmage is a principal mentor with Solid Quality Learning, and also heads Prospice LLC, a database consulting firm based in Seattle. He is a SQL Server MVP, PASS newsletter co-editor, and current president of the Pacific Northwest SQL Server Users Group. He also writes for SQL Server Professional and SQL Server Magazine.
Comment and Contribute






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



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