Exchanging Data with Mobile Users
Providing data to and collecting data from mobile users is a key part of many applications. Most applications that support mobile users fall into one of two broad categories:
Customer relationship management (CRM) and Sales Force Automation (SFA)
For example, a sales person can use an SFA application to enter order data while visiting a customer. This data is subsequently transmitted back to a central location, such as company headquarters or a data center.
Field force automation (FFA)
For example, workers in the field – delivery drivers, maintenance workers, inspectors, and others – can use an FFA application on a handheld device to collect and transmit data from remote locations. A delivery driver could enter data about package deliveries at delivery locations, and this data is subsequently transmitted back to a central location.
Both categories of applications require very similar replication features. The primary difference between the applications is whether or not data is updated by more than one user. This issue is addressed in the section "Common Requirements for This Scenario" in this topic.
The following diagrams illustrate two different approaches to delivering data to mobile users: one uses laptops and the other devices (which run Microsoft SQL Server Compact 3.5 SP2). The first approach is more commonly used with SFA and CRM applications, and the second approach is more commonly used with FFA applications. However, either approach could be used for either category of application.
The first diagram illustrates a scenario in which a set of users with laptops connects directly to a central site:
.gif "Replicating data from salespeople to headquarters")
The second diagram illustrates a scenario in which users with devices connect through Microsoft Windows Internet Information Services (IIS) servers to a central site. The IIS servers are required when using SQL Server Compact 3.5 SP2.
.gif "Replicating data to delivery drivers")
Adventure Works Cycles Examples
Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see AdventureWorks2008R2 Sample Databases.
Adventure Works Cycles has a large sales force that spends a considerable amount of its time in the field working directly with the company's primary customers, independent and franchise bicycle dealers. Teams of sales people are assigned to regions and each sales person typically handles his or her own customers. However, customer data can be shared by sales people and sales managers. Sales people enter order data on their laptops and transmit this data to the central office when it is convenient.
Adventure Works Cycles uses A-1 Shipping for its deliveries of parts and complete bicycles. The delivery drivers from A-1 Shipping all use devices that run SQL Server Compact 3.5 SP2. The drivers enter data on each delivery after a delivery is complete. This data is replicated to the A-1 Shipping central office and deleted from the device. The data is then made available to Adventure Works Cycles through a customer extranet.
Common Requirements for This Scenario
CRM, SFA, and FFA applications typically have the following characteristics, which an appropriate replication solution must address:
Data synchronization should be programmable, so that an application on a laptop or device can be customized to include synchronization without requiring end-user knowledge of replication.
In most mobile applications, data can be entered and updated at a central site and at remote sites. In FFA applications, most data is entered at remote sites.
Remote users enter and update data using a laptop, device, or Tablet PC.
Remote users must be able to make updates independently, without requiring a connection to the central site.
Because multiple users might update the same data independently, data conflicts can arise and must be handled.
Some data should only be updated at the central site, for example data in product pricing tables.
Users should be able to synchronize data on demand, rather than only at scheduled times.
The application must control how long a remote site can remain unsynchronized.
Some tables require filtering so that each user receives different data for one or more tables. For example, each sales person receives contact information only for his or her customers.
Some data must be treated as a unit when it is transferred between sites. For example, if an order is sent from a remote user to the central site, the order header must be committed prior to the order details.
The application might require custom business logic to be executed when data is synchronized.
The application might require that data be synchronized over the Internet rather than through a VPN or IPSEC dialup network connection.
The primary difference between CRM and SFA applications and FFA applications in regards to replication is whether or not data is updated by more than one user (updates by more than one user can result in conflicts). How much data is updated by more than one user depends on the extent to which data is filtered. For example, if data is filtered so that all users only update their own set of data, conflicts would not occur between users:
In CRM and SFA applications, data is often filtered, but some of the data is still updated in more than one place. Some data is updated only at headquarters, some of it by a single remote user, and some by more than one remote user. The following diagram illustrates the filtering common to CRM and SFA:
.gif "Filtering for sales force automation applications")
In FFA applications, it is common for data to be collected primarily in the field and subsequently uploaded to headquarters without conflict, because a single remote user is updating a given piece of data. The following diagram illustrates the filtering common to FFA applications:
.gif "Filtering for field force automation applications")
The Type of Replication to Use for This Scenario
SQL Server uses a publishing industry metaphor to describe the components of the replication system. The components include the Publisher, Subscribers, publications and articles, and subscriptions. In first two diagrams above, the central site is the Publisher. The data at the central site is the publication, with each table of data being an article (articles can also be other database objects, such as stored procedures). Each sales person's laptop and delivery driver's device is a Subscriber to the publication, receiving schema and data as a subscription. For more information on the components of the system, see Replication Publishing Model Overview.
SQL Server offers different types of replication for different application requirements: snapshot replication, transactional replication, and merge replication. This scenario is best implemented with merge replication, which is well suited to handle the requirements outlined in the previous section. For more information on merge replication, see Merge Replication Overview and How Merge Replication Works.
Merge Replication Options Relevant to This Scenario
Merge replication offers several options to address the requirements described earlier in this topic. The following list presents each requirement and the merge replication option(s) that address it.
Data synchronization should be programmable.
Replication offers programmability through stored procedures and Replication Management Objects (RMO). RMO is typically used for mobile applications. For more information on programming replication, see Developer's Guide (Replication) and Sales Orders Sample Scenario.
In most mobile applications, data can be entered and updated at a central site and at remote sites. In FFA applications, most data is entered at remote sites.
Merge replication provides this ability without specifying any separate options.
Remote users enter and update data using a laptop, device, or tablet.
Laptops and Tablet PCs can run SQL Server Standard and other editions (including SQL Server Compact 3.5 SP2), but Pocket PC devices require SQL Server Compact 3.5 SP2. Merge replication allows you to create publications and subscriptions that can be used by SQL Server Compact 3.5 SP2. For more information, see Replicating Data to SQL Server Compact.
Remote users must be able to make updates independently, without requiring a connection to the central site.
Merge replication provides this ability without specifying any separate options.
Because multiple users might update the same data independently, data conflicts can arise and must be handled.
Merge replication provides conflict detection and resolution for cases in which data conflicts are expected. It is best to design applications to avoid conflicts, but where this is not possible you can select the default conflict resolution mechanism (first in wins) or use custom conflict resolution. For more information, see Detecting and Resolving Merge Replication Conflicts.
Some data should only be updated at the central site, for example data in product pricing tables.
Merge replication provides download-only articles for those tables that should be updated only at the Publisher. For more information, see Optimizing Merge Replication Performance with Download-Only Articles.
Users should be able to synchronize data on demand, rather than only at scheduled times.
Replication offers two subscription types: push subscriptions and pull subscriptions. Pull subscriptions are better suited to on demand synchronization. For more information on subscription types and scheduling synchronization, see Subscribing to Publications and Synchronizing Data.
The application must control how long a remote site can remain unsynchronized.
Merge replication allows you to set a subscription expiration period to ensure that all Subscribers have synchronized within a certain amount of time. For more information, see Subscription Expiration and Deactivation.
Some tables require filtering so that each user receives different data for one or more tables. For example, each sales person might receive contact information only for her customers.
Merge replication allows you to filter columns and rows. Row filters can be static or parameterized. A static filter is applied only when a publication is created; it results in one data set. A parameterized filter is applied every time a Subscriber synchronizes; it results in a different data set for each Subscriber. CRM and SFA applications often use parameterized filters, but could also use static filters. For more information, see Filtering Published Data for Merge Replication.
Some data must be treated as a unit when it is transferred between sites. For example, if an order is sent from a remote user to the central site, the order header must be committed prior to the order details.
Merge replication allows you to specify that a set of related tables must be processed as a unit. This unit is referred to as a logical record. For more information, see Grouping Changes to Related Rows with Logical Records.
The application might require custom business logic to be executed when data is synchronized.
Merge replication allows you to specify code to be executed during synchronization. This code can respond to a wide range of events and has access to the data that is being synchronized. For more information, see Executing Business Logic During Merge Synchronization.
The application might require that data be synchronized over the Internet rather than through a dedicated connection.
When using (SQL Server Compact 3.5 SP2), data is synchronized over an HTTP or HTTPS connection. For other editions of SQL Server you can use Web synchronization, which requires HTTPS. For more information, see Web Synchronization for Merge Replication.
Steps for Implementing This Scenario
To implement this scenario, you must first create a publication and subscriptions, and then initialize each subscription. Click the links below for more information about each step:
After the subscription is initialized and data is flowing between the Publisher and Subscribers, you might need to consult the following topics for information on common administration and monitoring tasks: