In these days of distributed computing, getting the right data to and collecting it from the remote parts of your enterprise is crucial to the success of your business. And keeping all this data synchronized is no easy task, particularly across low-bandwidth or inconsistent network connections. The situation is even worse for mobile users, such as your salespeople, who often work in disconnected mode, then upload their changes to the database all at once. SQL Server, however, offers a flexible replication solution that lets you distribute data to users across your enterprise. These users can then modify that data and synchronize their changes among all participating servers in your replication topology.
SQL Server replication is a set of solutions whose terminology is based on a publishing-industry model. Replication data resides in one database (the publication database) on a central publication server (the Publisher). A distribution server (the Distributor) then distributes the data to a subscription database that resides on one or more subscribing servers (Subscribers). In this model, publications consist of one or more database objects (i.e., tables, stored procedures, and views) called articles. Subscribers receive the articles by subscribing to a publication. (For a quick review of replication concepts and terms, see the sidebar "Replication Basics," page 27.)
A set of replication agents—hosted by the SQL Server Agent—handles the movement of data within a replication topology. And replication ActiveX controls give you an object-oriented interface for programmatically managing the most commonly used replication agents: Distribution, Snapshot, and Merge. A separate control supports the replication agent that runs on SQL Server 2000 Windows CE Edition. As with any ActiveX control, you can access these replication controls programmatically from your applications—even those embedded in Web pages. When you use ActiveX controls along with the administration functionality that SQL Distributed Management Objects (SQL-DMO) provides, you can programmatically administer and control an entire replication topology.
Although you can manage replication and control the replication agents from Enterprise Manager, accessing replication functionalities programmatically through ActiveX controls has its advantages. For example, you might want to write a custom application to let a remote administrator control replication agents. You can also use the Merge ActiveX control to provide on-demand synchronization from client applications running on the Subscriber, letting users manually synchronize pull subscriptions (subscriptions managed by the Subscriber), decide which Publisher to synchronize to, and even add subscriptions. So by adding replication controls to your applications, you can give users some control over replication without giving them access to the full set of functionalities that Enterprise Manager provides.
To see how you can use the replication ActiveX controls in your applications, let's look at a sample application that uses the Merge ActiveX control to manually synchronize merge subscriptions and the replication Error control to handle replication errors.
Classic Sales Scenario
The classic merge-replication business scenario is a sales order-entry application that runs on a salesperson's laptop or device. After making sales-related entries in a local (Subscriber) copy of the database, the user connects to the Publisher, and the application synchronizes the subscription to upload the user's changes and download any changes from the Publisher.
My example, which uses the Merge ActiveX control from a Visual Basic .NET application, lets users specify at runtime the names of the Publisher, publication database, publication, Subscriber, and subscription database. However, for testing purposes, I used a publication based on SQL Server's Northwind sample database, which contains three articles—one each from the Customers, Orders, and OrderDetails tables—using the same computer as both Publisher and Subscriber. Figure 1 shows the application's single UI element, a Windows Form.
Although the sample application lets a user specify most of the information that the Merge Agent requires to synchronize the pull subscription, in practice, you'd configure this synchronization information at installation and store it in a file or in the Windows registry. When you store the information, the user can just click the Synchronize button without needing to know anything about the underlying replication topology.
Because this example uses an anonymous pull subscription (see "Replication Basics" for a definition of this term), the Merge Agent runs at the Subscriber, and the application is responsible for manually initiating synchronizations. You could also use the Merge control to register new subscriptions with the Windows Synchronization Manager or even add subscriptions, but these functionalities are outside the scope of this example. The sample application uses Windows Authentication to make all the server connections and assumes you've already created an anonymous pull subscription at the Subscriber and applied the snapshot for the publication at the Subscriber. (For information about how to create an anonymous pull subscription, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replimpl_26lv.asp.)