Consumer Point of Sale (POS) Applications
Consumer point-of-sale (POS) applications include those applications that consumers encounter directly or indirectly at the point of sale. Examples include terminals used by cashiers, ATM machines, and in-store kiosks. These applications collect data at remote sites and transmit it back to a central location, such as headquarters or a data center. It is common in these applications for data to be collected primarily at the point of sale and subsequently uploaded to headquarters without conflict, because a single remote user (typically a customer or sales clerk) is updating a given piece of data.
The following diagram illustrates a typical scenario with data flowing in two directions between a central site and remote locations:
.gif "Replicating data from stores to headquarters")
Adventure Works Cycles Example
Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see AdventureWorks Sample Databases.
Many of the retail outlets that sell Adventure Works Cycles products use point-of-sale systems that receive data from and transmit data to a central site. Typically, read-only product pricing and warehouse inventory data is sent to the retail outlet whenever updates to this data occur. Customer purchasing information is transmitted from each retail outlet to the central site.
Common Requirements for This Scenario
POS applications typically have the following characteristics, which an appropriate replication solution must address:
Most data is entered and updated at the remote sites.
Remote users must be able to make updates independently, without requiring a connection to the central site.
Data updated at a remote site is not updated at any other sites; therefore conflicts typically do not occur.
Some data should only be updated at the central site; for example, data in product description tables.
Users synchronize data at scheduled times (such as the end of the business day).
The application must control how long a remote site can remain unsynchronized.
Some tables require filtering so that each store receives different data for one or more tables. For example, each store receives information only for products it stocks.
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 dedicated connection.
The following diagram illustrates the filtering associated with this scenario:
.gif "Filtering for point-of-sale applications")
The Type of Replication to Use for This Scenario
Microsoft 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 the diagram 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 point of sale terminal 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 options that address it.
Most data is entered and updated at the remote sites.
Merge replication provides this ability without specifying any separate options.
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.
Data updated at a remote site is not updated at any other sites; therefore conflicts typically do not occur.
In POS applications, conflicts are often avoided because a single user updates a given piece of data. Because data doesn't overlap between users, it is possible to optimize performance with the non-overlapping partitions option. For more information, see the "Setting 'partition options'" section of the topic Parameterized Row Filters.
Merge replication provides conflict detection and resolution for cases in which data conflicts are expected. 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.
Most tables require filtering so that each user receives different data for one or more tables.
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. POS applications often use parameterized filters, but could also use static filters. For more information, see Filtering Published Data for Merge Replication.
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 SP1, 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: