Synchronize Your Databases with .NET Web Services (Part II)

n the first part of this article series, you saw how to expose data via an ASP.NET Web service, in an XML format useful for all kinds of clients. By exposing a .NET DataSet object, you effectively deliver a standard format XML document containing a DataSet represented as a diffgram. This diffgram can be used to reconstruct an exact copy of the original DataSet—complete with tables, columns, data types, metadata, primary, and foreign keys, and even table relationships.

An Overview of the Synchronization Process
This article expands on the principles discussed in the previous article to build a utility that synchronizes the contents of two or more databases over the Internet, delivering to each the rows that have been added to any other. The principle is relatively simple:

You can assume that:

• All the tables to be synchronized exist in each database, and those tables have identical structures across all the databases.
• Each table has a column containing a value that identifies its parent database or machine, so that rows can be identified depending on which machine they originally came from.
• Each machine exposes the same Web service that can deliver a DataSet containing new rows to any of the other machines for synchronization.
• Each machine calls this Web service on each of the other machines or a regular basis, fetching new rows from each one and adding them to its own database.

The schematic in Figure 1 shows the overall process for two machines. Note that the process must avoid collecting all the new rows from the other machine(s). By specifying the value for the column that identifies each machine when querying the Web service, we ensure that only the new rows that have been added to that specific machine are returned. Without this restriction, the first machine would copy the new rows from the second machine, and then—when the second machine fetches new rows from the first machine it would also collect the rows it previously sent to the first machine, causing duplication of the new rows.

Figure 1. The Synchronization Process. The figure shows the flow of messages and data through the synchronization process.

Author's Note: You can usually connect to a database server running on a different machine from your Web server, particularly when they are on the same network segment. This means that you can store and extract the data you want to synchronize from separate database servers connected to the Web server that runs the Web service or the server that runs the custom client application.

Choosing a Client Platform for the Synchronization
The sample Web service is implemented in ASP.NET, but determining the best application type for the client utility that will consume the Web service and update the database on each machine is slightly more problematic.

You need a .NET-aware client to be able to handle the DataSet exposed by the Web service, so the choices are between an ASP.NET Web Forms page, a console application, a Windows service, or a Windows Forms application. In this case, you want the process to be "automatable"—executed at specified intervals without human intervention. This basically rules out an ASP.NET Web Forms page.

Although any of the other application types will work, the sample code implements the client as a Windows Forms application, which you can launch manually (for testing), or automatically on a schedule using Windows Task Scheduler, running it under any account that you specify (not necessarily the currently logged-on user).

In the scenario discussed in this article (synchronizing the Web server log file summary data), the synchronization process takes place only once a week and runs for only a couple of minutes, so having the application permanently loaded and running as a Windows service seems to be overkill. However, using the Task Scheduler does require some account to be logged on. If you choose to build the client as a Windows service instead, there is no requirement for a user to be logged on at the time that the application runs.