Keep road warriors in sync with merge replication
Distributed data environments present a constant challenge. The idea of giving users in the field individual copies of data or subsets of data horizontally filtered for their areas of responsibility is easy to grasp but not necessarily easy to implement. Typically, field users must periodically synchronize their data with a central database to maintain an enterprise data set. The ability of field and headquarters users to update the same records in different copies of the database exacerbates the complexity of such a distributed environment. Add slow modem connections from hotel rooms, and you have the potential for some big headaches.
SQL Server 7.0 adds merge replication to SQL Server 6.5's snapshot and transactional replication. Merge replication is more complicated than the other types but is potentially the most useful for distributed data environments. Of the three types, merge replication provides the highest level of autonomy. Publishers and subscribers can work independently and reconnect periodically to merge their results. If users at multiple sites create a conflict by trying to change the same data element, those changes are resolved automatically. These characteristics make merge replication an ideal solution for applications in which users need full read/write access to local replicas of data in a highly disconnected environment. This article describes a real-world solution we developed for a client using merge replication and shows you how to use the replication wizards to configure replication. (For information on how SQL Server incorporates merge replication, see "Behind the Scenes," page 29.)
The Show Must Go On
Our client operates a popular circus and other live entertainment performances. About 40 users are responsible for various engagements, which headquarters assigns. As headquarters changes assignments, users in the field must adjust their data sets to maintain only records for which they are responsible. The client's previous solution used Sybase's SQL Anywhere, which supports SQL code in a trigger that changes the user's subscription based on the assignments stored in a table in the headquarters database. The client decided to move to SQL Server 7.0 but needed to continue supporting the users' distributed data requirements.
The users need only the data they're responsible for. So when they synchronize their data, they might gain new data or give up data, based on their user ID in the field_user table. Therefore, we needed to filter the data in the field_user table based on user ID, and we needed to enable inserting and deleting of rows in related tables. The solution also had to support the synchronization of field and headquarters data modifications. We believed that dynamic and join filtering, combined with the conflict-resolution capabilities of merge replication, could meet these requirements.
To demonstrate the functionality that the client wanted, we used the Pubs sample database to simulate the operational environment. We added a table called field_user, which matches users with events. Screen 1 shows a partial database diagram of our modified Pubs database with the field_user table and related tables. Using this structure, we set out to demonstrate to our client the power of combining dynamic and join filtering in merge replication.
The Mechanics of Merge
The first step was to create the tables in the database. The SQL script in Listing 1 creates these tables. Before you designate a publisher, you need to specify a distributor for that publisher. Either the local server or a remote server can act as the distributor for the publisher. In this case, use the Configure Publishing and Distribution Wizard to configure your server as both a publisher and a distributor. To start the Configure Publishing and Distribution Wizard, select and double-click a server group, then select and double-click a server. On the Tools menu, point to Replication, then click Configure Publishing and Subscribers.
The next step prompts you to specify a location for the distribution database and the log file or accept the default values, which you see in Screen 2. Then click Finish in the next dialog box to enable both the distributor and the publisher. A message box tells you that the configuration process was successful. Note that designating a server as a distributor uses additional server disk space to store the distribution database and the snapshot of the publication. Also, the replication agents running on the server use additional processor time.
After you set up the distributor and publisher, you have a tool called Replication Monitor installed on your system. Using this tool, you can monitor details about the current activity and the task history of each replication agent. Now you're ready to create publications.
Prev. page
[1]
2
next page 
