Data Synchronization techniques using SQL Server 2005
In a scenario where multiple applications hold the same sets of data and one of the users change the state of their shared object, this change will have to be propagated to the shared objects of the other users. The process of sending, receiving, and updating data between multiple systems is called Data Synchronization.
The mobile devices scenarioIn case of a business solution involving mobile devices, the chances of an update to a central database which is shared is very high. For example, if we consider a sales force automation application, it would have business entities like customers, estimates, invoices, sales receipts and payments. The information regarding each of them can be dynamically updated by different sales men and also by the operator at the corporate server end. In order for everyone to be in sync, data synchronization techniques would have to come into picture.
Though there are numerous databases today which support more than one data synchronization technique, here the emphasis will be on latest techniques available and widely used for Windows Mobile and Symbian based mobile devices.
For Windows Mobile, SQL Server provides very good synchronization techniques via Merge Replication (MR) and Remote Data Access (RDA).
For Symbian, J2ME is one of the widely used application development platforms. J2ME supports raw byte based record stores and does not have a true RDBMS in place. PointBase Micro Database is a database layer on top J2ME record store and provides many features including the ones to perform synchronization and filter.
Replication is a set of solutions that allow you to copy, distribute, and potentially modify data across your enterprise. SQL Server 2005 includes several methods and options for replication design, implementation, monitoring, and administration to give you the functionality and flexibility needed for distributing data and maintaining data consistency. The model is composed of Publisher, Distributor, Subscriber, Publications, articles, and subscriptions.
PublisherThe Publisher is a server that makes data available for replication to other servers. The Publisher can have one or more publications, each representing a logically related set of data. In addition to being the server where you specify which data is to be replicated, the Publisher also detects which data has changed during transactional replication and maintains information about all publications at that site.
DistributorThe Distributor is a server that hosts the distribution database and stores history data, and/or transactions and metadata. The role of the Distributor varies depending on which type of replication you implement. A remote Distributor is a server that is separate from the Publisher and is configured as a Distributor of replication. A local Distributor is a server that is configured to be both a Publisher and a Distributor of replication.
SubscribersSubscribers are servers that receive replicated data. Subscribers subscribe to publications, not to individual articles within a publication, and they subscribe only to the publications that they need, not all of the publications available on a Publisher. Depending on the type of replication and replication options you choose, the Subscriber could also propagate data changes back to the Publisher or republish the data to other Subscribers.
ArticleAn article is a table of data, a partition of data, or a database object that is specified for replication. An article can be an entire table, certain columns (using a vertical filter), certain rows (using a horizontal filter), a stored procedure or view definition, execution of a stored procedure, a view, an indexed view, or a user-defined function.
PublicationA publication is a collection of one or more articles from one database. This grouping of multiple articles makes it easier to specify a logically related set of data and database objects that you want to replicate together.
SubscriptionA subscription is a request for a copy of data or database objects to be replicated. A subscription defines what publication will be received, where, and when, synchronization or data distribution of a subscription can be requested either by the Publisher (a push subscription) or by the Subscriber (a pull subscription). A publication can support a mixture of push and pull subscriptions.
Snapshot AgentThe Snapshot Agent is a replication agent that makes snapshot files, stores the snapshot on the Distributor, and records information about the synchronization status in the distribution database. The Snapshot Agent is used in all replication types (Snapshot, Transactional, and Merge replications), and can be administered by using SQL Server Enterprise Manager.
Log Reader AgentThe Log Reader Agent is a replication agent that moves transactions marked for replication from the transaction log on the Publisher to the distribution database. This replication agent is not used in Snapshot replication.
Distribution AgentThe Distribution Agent is a replication agent that moves the snapshot jobs from the distribution database to Subscribers, and moves all transactions waiting to be distributed to Subscribers. The Distribution Agent is used in Snapshot and Transactional replications, and can be administered by using SQL Server Enterprise Manager.
Merge AgentThe Merge Agent is a replication agent that applies initial snapshot jobs from the publication database tables to Subscribers, and merges incremental data changes that have occurred since the initial snapshot was created. The Merge Agent is used only in Merge replication.
Merge replication is best used when there are few chances for changes to be made to the same records by different locations. Horizontally segmented tables are useful with merge replication. Separate publications are created and subscribed to, based on a region code or some other discriminatory mechanism.
For merge replication to work properly, some changes are made to the table schema as well as the distribution database. These changes are made to allow SQL Server to perform conflict resolution.
Merge replication is a sophisticated replication type that allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With Merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver that you create. Merge replication is best used when there is a need to support autonomous changes of the replicated data on the Publisher and on the Subscriber.

1. The snapshot agent (which lives on the distribution server) takes an initial snapshot of the data and moves it to the subscribers. Remember that the subscribers must first be synchronized with the publishers for replication to begin (with the exception of snapshot replication).
2. A distribution working folder is created on the distribution server to handle merges. 3. Replication now begins.
4. The merge agent takes modifications from the publishers and applies them to the subscribers.
5. The merge agent takes modifications from the subscribers and applies them to the publishers.
The merge agent receives any update conflicts and takes the appropriate action.
SQLServer Mobile Database Engine
The SQL Server Mobile Database Engine manages the local database on a device. For subscription databases, the SQL Server Mobile Database Engine tracks all database records that are inserted, updated, or deleted by maintaining change tracking information with each record.
SQLServer Mobile Client Agent
The SQL Server Mobile Client Agent is the primary SQL Server Mobile replication component on a device. The SQL Server Mobile Client Agent implements the SQL Server Mobile Replication object interface. Applications call this interface to programmatically control replication.
SQLServer Mobile Server Agent
The SQL Server Mobile Server Agent is the component responsible for managing the communication between SQL Server and SQL Server Mobile. The SQL Server Mobile Server Agent resides on the server that is running Microsoft Internet Information Services (IIS), and handles all HTTP requests made by the SQL Server Mobile Client Agent.
SQLServer Reconciler and SQLServer Mobile Replication Provider
The SQL Server Reconciler invokes the SQL Server Mobile Replication Provider when synchronization is performed. Both the SQL Server Reconciler and SQL Server Mobile Replication Provider reside on the server that is running IIS, on which SQL Server Mobile Server Tools is also installed. When the SQL Server Reconciler is started, a Merge Agent at the Publisher is associated with the subscription.
Internally...
* System tables are added to the distribution working folder. These are used to track changes for use during synchronization as well as for conflict resolution.
* SQL Server creates triggers on both the publishing servers and the subscription servers involved in merge replication. These triggers are fired when a data modification occurs in one of the tables involved in replication. Information about the change is stored in the system tables added to the distribution working folder. These saved changes allow you to track changes to each row or column of modified information.
* SQL Server creates a new “uniqueidentifier” column for each row in tables being replicated. A GUID or ROWGUID is then added to uniquely identify that row. In this fashion, when a record is updated at different sources, the different updates can be differentiated.
Remote Data Access (RDA) techniqueRDA provides a powerful yet simple way for a Windows Mobile application to access SQL Server data located in a remote SQL Server 2000 SP3a, SQL Server 2000 SP4, or SQL Server 2005 database. RDA can be used whether the Windows Mobile device is continuously connected or intermittently connected to the SQL Server system.
Applications use RDA when they do not need the full functionally of merge replication. You can use RDA without configuring SQL Server replication or creating publications.
Applications can use RDA in two ways. The application can submit a SQL Data Manipulation Language (DML) statement that is forwarded to the SQL Server system for execution.

Alternatively, the application can submit a SQL query that returns a row set. The resulting row set is transmitted to the Windows Mobile device where it is stored in a table. All changes made by the application will be tracked, and at the request of the application, the updated rows will be sent back to the server where they are applied to the SQL Server database.
RDA uses the authentication, authorization, and encryption services of Microsoft Internet Information Server (IIS). It supports anonymous and basic authentication as well as Secure Sockets Layer (SSL) encryption.
Remote data access (RDA) uses three components of Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile): the SQL Server Mobile Database Engine, the SQL Server Mobile Client Agent, and the SQL Server Mobile Server Agent.