by Rodney Guzman

Summary: Applications that are occasionally connected have a reputation for being difficult to implement. The challenges are layered, and the heart of the problem is how data is managed. Each occasionally connected client consumes and produces data at random intervals. Islands of data are produced when disconnected, and must be reconciled later when connectivity is restored. Technologies exist to move data to and from the clients, however, only the simplest of data reconciliation tasks are handled without custom code intervening. For example, SQL Replication is a fantastic technology for moving new data from one system to another. But when a record is updated by more than one client, should the last one in always win? Also, what happens when new data entities are created on multiple clients, but they each represent the same unique entity instance? Unfortunately, data models are usually too complex to be handled by generic conflict resolution processes.

This article will discuss how to build your data model to support an occasionally connected application. The data model supports the introduction of a complex conflict resolution process built in .NET. A point-of-service sample application is introduced with steep requirements, including: Web and smart client entry points; smart clients will be occasionally connected; data entities that span multiple tables; and a complex conflict resolution process which includes support for new records that can be created on the Web application and/or smart client application instances.

Contents

Scenario
When You Are Connected
Complexity of Never Knowing When You Are Connected
Relying On Key Connected Moments
Conflict Resolution
Logical Records
Table History
Logical Transactions
Updated Records
Inserted Records
Conflict Resolution with .NET
Conclusion
About the Author

Scenario

For the purposes of this article, a sample event management application is introduced. This application is a hosted Web site with a robust data model. The business demands that this application be accessible in remote, disconnected areas where the events are held. At the events, the company’s products can be purchased and attendee information updated just as they can on the Web site. Each event lasts for potentially multiple days with thousands of attendees. During the event, there is no regularity at which anyone can assume to have network connectivity.

Shortly after the event is completed, the data on each client much be reconciled. The simplified database diagram shown in Figure 1 is referenced as a sample subset from this application.

Figure 1. Extremely scoped down version of the SQL tables

Re-use of existing components to minimize the cost of building a smart client application has been demanded by the business. The business does not want to rebuild their business objects already abstracted away correctly from their ASP.NET application. They want the same business objects to be deployed with the smart client Windows Presentation Foundation (WPF) application. SQL Express will be in use on the desktop, and the data schema will re-used from the Web application.

When You Are Connected

You can never rely on when occasionally connected applications will be connected, which imposes a tremendous burden on your application. An application must be connected at certain times to perform base functions. For our example scenario, to optimize the event experience for attendees, pre-registration information should be accessible on the disconnected client during the event, so the device is synchronized with the central data warehouse prior to the event. Regardless of how the information is interchanged, the device needs to be connected before the event to obtain this information.

The initial requirements for the application included a model that supported an application that never required the user, at any time, to be connected to perform a function. Ironically, the user has to be connected at some point in the life cycle of using the application in order for data to be synchronized to and from the server. The lack of control over when the application might be connected poses complexities that cannot be ignored. In the case of this application, requirements were modified to force the application to be connected at key moments in time.

Complexity of Never Knowing When You Are Connected

SQL Replication supports very well the concept of data interchange with occasionally connected clients. However, if you are managing a multistep process in which each step relies on some form of connectivity, knowing where you are in the process can be challenging. In the event management application, to rely exclusively on SQL Replication, the process flow would have to resemble the following:

1. The list of available events would be replicated to each client device. If a new event is added, or an existing one modified, it would need to be synchronized to each device.
2. Authorization for who can see which event would be replicated to each client device. If authorization changed, it would need to be replicated.
3. On the local device, the user selects the event to synchronize, placing a record in the EventDownload table in the local database which includes the host name of the client machine and the event to replicate.
4. The EventDownload table is replicated back to the server.
5. Filtered SQL Replication brings down the requested events to the user’s device.

This process flow seems simple enough but requires connectivity throughout the process. Imagine having a conversation with a nontechnical user about why he could not see the event on his device because the authorization granting him access changed after he synchronized his machine. Relying on a model where you cannot ever control connectivity can be complicated to debug and maintain.

Relying On Key Connected Moments

Ultimately, connectivity at some point is required. In contrast to the pure SQL replication model, a more direct approach can be taken to simplify the overall processing. Enforcing connectivity at key points avoids several of the round trips described in the previous section. For example, rather than synchronizing the list of events to the local database, a Web service can be invoked to retrieve a list of authorized events the user can synchronize with, changing the flow to:

1. The user launches the application and retrieves a list of events via a Web service call. Only authorized events are shown to the user.
2. The user picks an event and calls the Web service again. On the server a record is set in the EventDownload table with the host name of the user device.
3. Filtered SQL Replication rules associate events to client host names, specifying which events will be replicated to which client device (Figure 2).

Figure 2. EventDownload table specifies which events will download to which client hosts

A whole host of additional information can be obtained through the Web services, from the number of records to be synchronized to progress updates on whether a replication has occurred or is complete.

Conflict Resolution

SQL Replication and ADO.NET provide no silver bullet for conflict resolution. Most of our applications are simply too complicated to handled by out-of-the-box solutions. We cannot always rely on the “last one in wins”—typically, we need to wait for data to be accumulated from multiple sources before making a decision on what “the best” record is.

This outlook seems abysmal, but there are techniques to cut through this complexity. In the event management application, we rely on the strengths of SQL Replication as a cornerstone to the solution. A perceived strength of SQL

Replication is that it is fantastic for managing SQL inserts. For updates, however, there were too many scenarios where we needed fine control over the conflict resolution process at scheduled intervals.

Logical Records

In the context of the event management application, a logical record for an attendee is not only the information within the Attendee table, but all related information. Selected sessions, product purchases, and family members all make up the attendee logical record. If any one of these pieces of information is changed, regardless of what table the change was made in, then the attendee logical record has been changed.

A base requirement for this application is that if an attendee’s information is touched at the smart client, a validation of the entire attendee logical record has occurred. Therefore, even if only a single table was modified, each table’s data that represents an attendee logical record has been validated as being correct at that moment in time. This poses an interest dilemma in that standard SQL replication processing cannot automatically synchronize all table data related to a logical record if the data has not been touched. We managed this problem by forcing a change to occur in the database to trigger the appropriate set of events—what we call “logical transactions”—for SQL Replication.

Table History

The existing Web application’s database has a history table associated to every user modifiable table in the database. The purpose of these history tables is to keep a history of all changes, when the changes were made, and who made the changes. A requirement of the smart client application is that all of the local history changes are recorded and then replicated back to the central data warehouse. All of the history tables are populated by a trigger on the respective parent table. Any time an insert or update is performed on the parent table, a new history record is produced (Figure 3).

Figure 3. History tables on key parent tables allow “transactions” to be maintained

The history tables constitute a transaction history of change to each table, a fact which became important to us when considering how updates to a record can be replicated back to the central data warehouse. Instead of replicating a change to a record back to the central data warehouse, such as an update to the parent table, we could replicate the new inserted history records and reconcile all the information centrally in an intelligent manner.

In our design, we would always have at least one record in a history table. If a record is created on the smart client, then the trigger would fire and produce a history record. If a record was replicated centrally to the smart client, the trigger would fire, also ensuring that a history record would be present.

In order to take advantage of history records as transactions, and to support the concept of logical records where a record is represented across multiple tables, we needed to bind history records across history tables. This requirement resulted in the concept of logical transactions.

Logical Transactions

A logical transaction is a representation of an attendee across all tables at a moment in time. What was missing from the history tables was a way to associate them together. When a change occurred in a parent table, the trigger will fire to populate the respective history table. This trigger would now need to be modified to perform the following functions (Figure 4):

• Make a copy of the parent table and insert it into the respective history table (as before)
• Obtain a new transaction ID that was date and time stamped
• For every history table that represents the logical record, update the last history record inserted with the new transaction ID

Figure 4. Logical transactions (AttendeeTransaction) keep history records related

The third point is important in that even though we made a change to a single table, and only one new history record was produced, in that moment of time all of the other tables that represent the logical record have been validated. The functionality of obtaining a new transaction ID and updating all the latest respective history records is encapsulated in an SQL user-defined function and inserted into each trigger.

Logical transactions solve a key problem we had when dealing with information being updated on multiple devices. This problem stems from the complexity of synchronization multiple devices at different times for the same information.

Consider this scenario: Device A updates Attendee X on Monday, and device B updates the exact same information for Attendee X on Tuesday. In this application, because the same information was updated, the information updated on device B is deemed more relevant than A because it was updated at a later time. However, device B is synchronized on Thursday and device A on Friday. In the Thursday evening conflict resolution processing, no updates from device A are known, so device B’s updates are taken. In Friday evening’s conflict resolution processing, device A’s information is reconciled. Without any date/time stamping of the logical transaction, it is difficult to understand when the change occurred and whether or not device A’s information is more or less relevant than device B’s. Logical transactions provided a clean representation of the information for all data entities in the system.

Updated Records

When an SQL table record is updated, SQL Replication can be used to move those changes from one database to another. Sounds simple enough when stated this way, however, knowing if you should overwrite the changes you have with an update from another system is not so trivial. The last-one-in-wins rule does not always apply. There may be parts of the record you wish to keep rather than just overwriting it.

Refining how one requires updated records to flow within your application is important in simplifying as much as possible the overall design. In the case of disconnected mobile applications, the flow of information flows from a central server out and lives and grows temporarily on each disconnected device, and eventually flows back to the central server. When examining this flow we made some assumptions and requirements on how the event management application would operate.

Information from the central server only flowed once to the mobile device. For example, when an event was requested to be synchronized, and all the attendees’ information was replicated down to the device, any updates made to the attendee information in the online Web application would not be replicated to the device. Even if we intended updates to be replicated, we could not rely on the users to re-synchronize their devices throughout the event. However, during the events, the attendees may update their information online. Our design allows multiple devices and the online system to update the same records and for reconciliation later by our .NET conflict resolution process. This is enabled by only replicating what has been inserted and not updated. Only SQL inserts (no updates) are communicated between systems. We can then pick a time of our choosing to reconcile all the information centrally.

In the event management application, after an event has been replicated to a device, a replicated attendee can be updated by the WPF application. When the attendee record is updated, a history record is produced. With the history record, a new logical transaction is created. Both of these are SQL inserts. What has been inserted is replicated back to the central server. The parent record itself is not replicated as this would overwrite the record on the server. The .NET conflict resolution process examines the logical transaction queue and determines new changes are present. It examines the history records that have been produced and determines the best approach to integrate those changes into the parent records.

Inserted Records

SQL inserts are much simpler to manage with SQL replication as there is, on the surface, no conflict resolution to handle. However, what happens when a new attendee is added to the disconnected device that already exists elsewhere? In our application, an attendee could have registered in the online application after the event information was replicated; at the event, the attendee could be added to the mobile application; and later, the attendee could be working with another user with their own instance of the application. Multiple instances of the same attendee record can easily be created.

We began to think of records that have been inserted on the client as not having the same rank as records inserted on the central server. When the inserted record on the client was replicated back to the server, the information contained in the record must be validated before becoming accessible to the application. To prevent these replicated records from being seen by the ASP.NET application, they would need to be tagged and excluded from SQL queries. The .NET conflict resolution process would examine these records, determine how to integrate the information, and decide whether or not to copy the newly inserted information into another record, retire the record, or make a new attendee available in the ASP.NET application (in our example).

For each SQL table that has records that can be replicated from the device to the central server, a new column was added to maintain the state of the record. Each record could have one of the following states: created on the server, not reconciled, merged, retired, or reconciled. If the record is created with the ASP.NET application, it would be tagged as “created on the server.” This state tag assists with filtered replication of records from the server to the client. If a record is created on the client, then it is “not reconciled.” When this record is replicated back to the server, then it is in the same “not reconciled” state, identifying the record to the .NET conflict resolution process as a record that needs to be processed. The ASP.NET Web application will ignore any records in the “not reconciled” state. Once processed, depending on the action taken by the conflict resolution process, the record’s state would be changed to “merged,” “retired,” “reconciled,” or “unknown.”

Conflict Resolution with .NET

The requirements of the event management mobile application to manage conflict resolution go beyond what can be handled with out-of-the-box technologies. With logical transactions and state associated to inserted records, our goal was to get the data all in one spot in order to be able to make intelligent decisions about how to handle the information. Part of the puzzle is to determine when to actually make these intelligent decisions. Standard out-of-the-box technologies make decisions at the time the records are moved. In contrast, we needed to have a complete picture of all the inserted records (with parent tables and history tables) before determining how to reconcile the information. A business process was put in place to run the .NET conflict resolution process at 1 A.M. server time to reconcile the previous day’s replicated information.

When the process begins, it obtains all logical transactions from the day that process last successfully executed to the day previous to the current date. An attendee may be represented by multiple logical transactions as the information could have been updated multiple times on one or many machines. For multiple updates for the same attendee on the same machine, only the last one entered is processed. From the remaining records, logical transactions are grouped by attendee and sorted by date and time entered.

As each logical transaction is processed, it is examined for whether or not the attendee record was originally replicated from the central server or entered on the client machine. If originally replicated, then the history records have already been replicated back and associated correctly. We are now free to implement as much detail as necessary in determine what business rules should be executed on the information. Each logical record referenced by the logical transaction has one or more tables associated to it. We can decide to simply overwrite more recent information across all tables, or one or more of the tables, or specific columns within any of the tables. This all depends on the business rules and can be as complex as necessary.

If a logical transaction is associated to an attendee record that was created on the client, then a determination must be made as to whether the attendee already exists in the system. At this point in time, this record is not viewable by the ASP.NET application as it is in a “not reconciled” state. A composite key is constructed from the attendee records to uniquely identify them. The existing attendees are examined to see if there is a match to the new record. One of three pathways is taken at this point: the new attendee record already exists, is brand new, or this is not enough information to make a decision. If the attendee already exists, then all of the history records associated to the attendee record in the “not reconciled” state are updated to point to the pre-existing attendee record. The same rules then apply as if the pre-existing record had been updated. If the attendee record definitely is new, then its state is changed to “reconciled” and it is made available to the ASP.NET application. If no determination can be made, then the record’s state is changed to “unknown” and an exception management Web application is run to determine what to do with the record.

This process is repeated for each logical transaction. All the work that has been done was in preparation for this process to simplify it as much as possible and to centralize all complexity into a single spot.

Figure 5. RecordState column added to remember where the record came from and how it was processed

Conclusion

Mobile applications are challenging to build. There is no magic framework we can all use to enable disconnected scenarios. We have powerful plumbing, but without customization, it only solves the simplest scenarios. The approach described in this article is generic enough to be applied to across different disconnected applications. Relying on the power of SQL Replication to perform only inserts allowed us to focus conflict resolution in the .NET process. Although much data work had to be done to augment the database schema to support it, we believe it simplified, as much as possible, the conflict resolution process.

This approach is not new, and it has limitations that make it appropriate for only a subset of mobile applications. Many mobile scenarios, for example, require real-time resolution of data as soon as it has been replicated to the central server compared to the many hours of delay proposed in this solution. Unidirectional replication of only inserted records may not be feasible, as our choice here was to centralize when and where we perform conflict resolution. Data loss due to conflict resolution has also been relegated to manual intervention in this solution. We took advantage of the requirement to retain and replicate all changes to records centrally, which became the transactions we based our conflict resolution process on. If you have no such requirement, then a burden is placed on replicating more data than you may prefer or be able to do. Ultimately, the business process driving your solution will dictate how cavalier you can be with your choices.

About the Author

Rodney Guzman is the CTO and cofounder of InterKnowlogy. He got his start in software systems working on submarine sonars during college. For seven years at SAIC, Rodney was the lead developer and architect on such projects as a large Java SOA HTTP/XML based Web portal on military hospitals throughout the country. In 1998 Rodney moved to Stellcom to work on more Microsoft projects, including Site Server implementations and an enterprise security framework for Pacific Life, that allowed custom policies derived from AD groups and attributes to drive personalization and security on ASP Web sites. At InterKnowlogy, Rodney steers the technology direction, acting as lead architect on its largest projects, such as a large SOA implementation with a smart client framework, creating large Microsoft Web properties (CommNet and Partner Campaign Builder), and large MOSS implementations. Rodney architected the WPF/MOSS Scripps Cancer application. Rodney has spoken in numerous Microsoft events and has written numerous articles. He has sat on the Commerce PAC and Microsoft Architectural Advisory Board, and is a Solution Architect MVP.

This article was published in the Architecture Journal, a print and online publication produced by Microsoft. For more articles from this publication, please visit the Architecture Journal Web site.