Control Replication with ActiveX

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.)

Step by Step

Listing 1, page 28, shows the Visual Basic .NET code that implements the Windows Form for the synchronization application. (For readability, I removed all the Visual Studio Designer-generated code that represents the UI objects on the Windows Form.) Let's walk through the process of building this sample application, including creating a Visual Studio .NET project, coding the application in Visual Basic .NET, and deploying the application.

Setting up the project. The main complicating factor of using the Merge control in a .NET application is that the Merge control is unmanaged code. To program against the control, you must use a System.Interop managed interface that the .NET Framework requires when accessing unmanaged code from managed environments. Fortunately, when you use Visual Studio .NET to build this sample, the development environment automatically generates the required System.Interop wrappers for this unmanaged ActiveX control.

After creating a new Visual Basic Windows Forms application project in Visual Studio .NET, you add to the project references for both the replication Merge and Error controls. (When SQL Server 2000 is installed, the ActiveX controls are also installed and registered with the Windows OS.) To add the references, click Add Reference from the Project menu, then in the Add Reference menu, select Microsoft SQL Merge Control 8.0 and Microsoft SQL Replication Errors 8.0 from the list of available COM components.

Building the UI. After setting up the project, you need to lay out the form by adding the following eight Windows Forms controls:

Setting the control properties. When you're laying out the form, Visual Studio automatically creates in the form's code behind page the code that implements the UI components. To this Visual Basic .NET code in the project, you must add the synchronization code that Listing 1 shows. When referencing objects in the code, you would normally have to fully qualify each object's name with the object's namespace reference. But you can save a lot of code by adding Imports directives that reference these namespaces, as the code at callout A in Listing 1 shows.

At callout B, the code instantiates a SQLMerge object and a SQLReplError object. You use the WithEvents keyword to enable callback functionality on the SQLMerge object. The code at callout C defines a handler for this callback functionality, which I'll explain in a moment. Note that you need to declare the SQLMerge object at the module level if you want to use the callback functionality.

In the sample application, the code that synchronizes the subscription is in the cmdSync_Click method and executes in response to the cmdSync button's Click event. In this method, you define the SQLMerge object properties for the pull subscription, as the code at callout D shows.

Activating the Merge Agent. After setting the Merge Agent properties through the SQLMerge object, you need to tell the agent to synchronize the subscription. You do that by using the SQLMerge object's Initialize, Run, and Terminate methods, as the code at callout E shows. In Visual Basic .NET, you should call these methods within a Try/Catch statement to properly handle any errors that occur.

Handling errors. Replication provides a separate error-handling control that you can use to give users detailed error information. The Microsoft SQL Replication Errors 8.0 control implements both a SQLReplError class and a SQLReplErrors collection. When a replication error occurs, the SQLMerge object adds a reference to the error collection in the ErrorRecords property. The code at callout F in Listing 1 shows how to handle replication errors that occur during synchronization and how to iterate through the errors in the collection. This section of code checks the SQLMerge object for any SQLReplError objects in the ErrorRecords collection, which would indicate that errors occurred during the synchronization process. If any errors exist, the code queries each error and uses the information to build error messages that it displays in the UI.

Using the Status event. The replication ActiveX controls provide information about the synchronization process's progress and result by using a callback function known as the Status event. This functionality provides both a progress bar that shows the percent-complete value during long-running processes, as Figure 2 shows, and a message string that describes the result of the synchronization process, as Figure 3 shows. To implement the Status event in a managed-code environment such as Visual Basic .NET, you need to be running SQL Server 2000 Service Pack 3 (SP3).

Callout G in Listing 1 shows the method that handles the Status event. As I mentioned earlier, to use the Status event, you need to declare myMergeObj at the module level by using the WithEvents keyword. This routine returns a SUCCESS code to the Merge control. Setting the return value of this event handler to CANCEL will cancel the synchronization process. Calling the DoEvents method updates the progress bar. Note that you need one more line of code to make the event handler at callout G work; callout C shows the code that adds a handler for the Status event.

Deploying the application. After coding the application, you compile it into a .NET executable by using Visual Studio .NET's build functionality. In addition to the executable, Visual Studio .NET creates two .dll files—Interop.SQLMERGXLib.dll and Interop.REPLERRXLib.dll—which contain the Merge and Error ActiveX controls and their managed-code wrappers. To deploy this application, simply copy these three files into the same directory on a computer that's running SQL Server 2000.

Customization and Control

Replication lets you distribute and synchronize data across your enterprise. And merge replication, in particular, is an ideal solution when you need to distribute copies of data to many users and for applications in which users modify data in a disconnected and highly autonomous state. When you want to let users have some control over replication, such as when to synchronize their copies of the data with a central Publisher, the replication ActiveX controls provide a powerful programmatic tool.