A Technical Comparison of Replication and Remote Data Access Features in SQL Server 2005 Mobile Edition 3.0
Summary: Compare feature sets for the two Microsoft SQL Server 2005 Mobile Edition 3.0 (SQL Server Mobile) connectivity solutions, merge replication and remote data access (RDA), and understand which connectivity solution might be more beneficial for your project. (7 printed pages)
Connecting SQL Server Mobile to SQL Server
Introducing Merge Replication
Introducing Remote Data Access (RDA)
Data and Schema Definition
Types of Tables that Can Be Propagated
Microsoft SQL Server 2005 Mobile Edition 3.0 (SQL Server Mobile) supports two methods of exchanging data with a SQL Server database:
- Merge replication, which provides a robust full-featured solution that allows a mobile application to make autonomous changes to replicated data, and at a later time, merge those changes with a Microsoft SQL Server database, and resolve conflicts when necessary.
- Remote data access (RDA) provides a simple way for a mobile application to access (pull) and send (push) data to and from a remote Microsoft SQL Server database table and a local SQL Server Mobile database table. RDA can also be used to issue SQL commands on a server running SQL Server.
When using either RDA or replication, SQL Server Mobile connects to SQL Server through a Microsoft Internet Information Services (IIS) server. Because connectivity to SQL Server is handled by IIS, your device only needs the ability to connect to the IIS Web server using the HTTP or HTTPS protocol. This allows you to perform RDA and replication operations over any type of network connection that supports HTTP: local area networks (LANs), wide area networks (WANs), and Microsoft ActiveSync® connections.
Both RDA and replication are well suited to wireless transports. Compression is used to reduce the size of transmitted data. Encryption can be used to safeguard sensitive user data during transmission.
Both RDA and replication support Integrated Windows authentication and SQL Server authentication when connecting to SQL Server.
SQL Server Mobile replication is based on Microsoft SQL Server merge replication. Merge replication can be implemented by using a Microsoft SQL Server 2000 database or a Microsoft SQL Server 2005 database.
Merge replication is ideally suited to mobile applications because it allows data to be updated autonomously and independently on both the portable device and the server. The data on the device and the server are later synchronized to send changes from the client to the server and receive new changes from the server.
Although merge replication requires more configuration and maintenance at the server than RDA does, there are many advantages to using merge replication, including:
- Replication provides built-in and custom conflict resolution capabilities.
- Replication allows for the synchronization of data from multiple tables at one time.
- Replication provides rich data replication options, including:
- Selection of article types and filtering to improve performance
- Identity range management
- Microsoft SQL Server includes extensive tools for:
- Creating and synchronizing SQL Server Mobile subscriptions
- Monitoring subscribers for each publication
For general information about how merge replication works, see "How Merge Replication Works" in SQL Server Books Online. For specific replication information regarding SQL Server Mobile subscribers, see "How Replication Works" and "Replication Architecture" in SQL Server Mobile Books Online.
Remote data access (RDA) provides the ability for mobile applications to access data from a remote SQL Server database table and store that data in a local SQL Server Mobile database table. The application can then read and update the local SQL Server Mobile database table. SQL Server Mobile can optionally track all changes that are made to the local table. The application can later update the changed records from the local table back to the SQL Server table.
In SQL Server Mobile, propagating the data from the SQL Server table to a local SQL Server Mobile table is called pulling the data. Propagating the changes that are made in the local SQL Server Mobile table back to the SQL Server table is called pushing the data.
RDA is appropriate when the full functionality of SQL Server Mobile merge replication, including conflict resolution, is not required.
Deciding between using remote data access (RDA) and replication in your SQL Mobile application depends on the purpose, function, scale, and requirements of your mobile application. Each connectivity data solution has different advantages and disadvantages. These advantages and disadvantages are discussed in the sections that follow.
This section briefly examines the differences in support for comparable features between replication and remote data access (RDA). This section is not meant to describe every feature available for replication or RDA; it only provides a comparison of those features that can be compared. For example, there are many additional replication features in which RDA has no comparable functionality; therefore they are not explored in this article.
The extensive features of merge replication in SQL Server Mobile come from the merge replication features of SQL Server. SQL Server Mobile takes advantage of these features by subscribing to SQL Server publications. Creating a publication on the server adds a number of system tables to the database being published and a Unique Identifier system column to each user table that is published. These system objects are added to the server database to manage replication. RDA does not require any changes to the database on the server. Because RDA is not invasive to your schema on the server, RDA's functionality is limited and is not as full featured as replication, but it allows you to transfer data between SQL Server and SQL Server Mobile without any server-side configuration work. In some cases, you might not have permission to alter the schema on the back-end system. If this is the case, RDA is a potential connectivity option to transfer data changes between SQL Server and SQL Server Mobile. Another option to consider, if the robust functionality of replication is desired and the back-end database schema cannot be altered, is to use a middle-tier SQL Server as your replication server, and then transfer data to the back-end system using another non-invasive technology, such as Data Transformation Services (DTS) or Web services.
In summary, replication does require some minor changes to the schema of your server database, but these changes allow for a very robust set of features, while RDA requires no changes to your server database, but is not a full-featured connectivity solution.
As discussed earlier in this document, you prepare for replication by creating a publication on the server. The data and schema to be replicated to the Subscriber are specified at the server when the publication is created. The schema is automatically defined at the client when the subscription is first created. The publication defines the tables (articles) to be replicated to the Subscriber, including both row filtering (limiting data flow using a WHERE clause) and column filtering (limiting the columns in a table to be replicated). A publication can contain one or many tables. The entire publication definition, including the data and schema of the tables being replicated, is created on the SQL Server Mobile database using one synchronization method.
When using RDA, the client controls which data is transferred from the server, including the data definition (including row filtering) and schema definition (including column filtering). Unlike replication, where multiple tables can be part of a single synchronization, RDA is limited to one table per RDA pull process.
In summary, the data and the schema to be initially created in the SQL Server Mobile database are defined at the server with replication and on the client with RDA. A replication publication can contain multiple tables, while RDA is limited to one table per download (RDA pull process).
When using replication, schema changes (such as adding or dropping columns, adding or dropping constraints, or altering a column definition) can be replicated to the Subscriber after the subscription is initially created. When a pending schema change needs to be replicated to a Subscriber, the schema change is first replicated, and then the resulting delta changes are exchanged between the Publisher and the Subscriber. Server schema changes do not necessarily cause the application to be changed and recompiled; for example if a column was added, or a column was removed which is not being used in the application. Minor schema changes on the subscription database are allowed. For more information, see "Replication Limitations" in SQL Server Mobile Books Online.
When using RDA, schema changes on the server are not supported. Depending on the schema change, a push from the client to the server may fail. If the server schema is changed, the client must drop the table and pull all of the data from the server again. Changing the server schema can cause you to change and recompile your application. Minor schema changes on the client are allowed. For more information, see "Remote Data Access (RDA) Limitations" in SQL Server Mobile Books Online.
In summary, when using replication, server schema changes are supported, resulting in no data loss on the Subscriber. However, server schema changes are not supported when using RDA and can result in data loss at the client. Minor schema changes are allowed on the SQL Server Mobile connectivity tables for both replication and RDA.
In many applications, you may want to use an incrementing number to manage records inserted at a client database. For example, if your user is entering new orders into a table, you may want to assign an automatically incrementing number to each order. When using such a system, you must ensure that the numbers do not conflict between clients. Replication supports automatic identity range management for both integer and bigint columns for publications and subscriptions. Using automatic identity range management ensures that no rows will conflict, no matter how many clients you have. For more information about using this replication feature, see "Replicating Identity Columns" in SQL Server Books Online.
When using RDA, automatic management of identity columns is not supported. To use this data type property you must manually manage the values for the entire system.
Constraints and Indexes
Because replication supports multiple tables in the publication definition, referential integrity constraints and indexes are automatically replicated from the server to the subscription database.
Because RDA only supports pulling a single table at a time, referential integrity constraints are not transferred, but indexes can be. Additional schema definitions must be defined at the client.
Exchange of Tracked Data Changes
When using replication, system objects are created in the publication database when the publication is created and in the SQL Mobile database when the subscription is created. These objects allow for a very robust data exchange model between SQL Server and SQL Server Mobile. Replication is designed to track changes in the publication and subscription databases. There are two tracking levels to control the data flow between the Publisher and Subscriber:
- Row-level tracking on a table infers that the entire row will be transferred during synchronization. This tracking level can be more costly for data transfer depending on the connection speed, but requires less tracking information to be stored on both the Publisher and Subscriber.
- Column-level tracking reduces the amount of data transferred by tracking changes at the column-level in addition to the row, and allowing only the changed column data to be transferred from the Subscriber to the Publisher. The Publisher will always send the entire changed row to the Subscriber, because it cannot be sure if the row actually exists yet at the Subscriber.
For more information about tracking levels, see "Using Row-Level and Column-Level Tracking" in SQL Server Mobile Books Online and "Row-Level Tracking and Column-Level Tracking" in SQL Server Books Online.
RDA uses system objects in the SQL Server Mobile database to track changes made to the data. With this information, RDA pushes only the changes, specifically the changed rows, from SQL Server Mobile to SQL Server. For SQL Server Mobile to retrieve data changes from SQL Server, a complete refresh of client data is required.
In summary, replication supports the bidirectional data exchange of delta changes, because data changes are tracked on the Publisher and the Subscriber. RDA only supports the data exchange of delta changes from SQL Server Mobile to SQL Server and requires a full refresh of data to receive changes from SQL Server to SQL Server Mobile because data changes are only tracked on the SQL Mobile database.
When adding a table to a replication publication, you can set properties on the table that control data flow. In addition to controlling data flow between the Publisher and the Subscriber, the table properties can also be used to enhance the performance of synchronization. For more information, see "Parameterized Row Filters" and "Optimizing Merge Replication Synchronization Performance with Download-Only Articles" in SQL Server Books Online.
RDA does not support any table properties to control data flow or enhance performance. The data to be pulled down to the client is controlled only by your application code on the client.
When using replication as your connectivity solution, potential conflicts include data changed by different users, and rows that failed to be applied due to an error. Replication fully supports the resolution and management of conflicts on the server, including built-in and custom conflict resolvers. For more information, see "Replication Conflict Detection and Resolution" in SQL Server Mobile Books Online and "Merge Replication Conflict Detection and Resolution" in SQL Server Books Online.
When using RDA, conflicts only include rows that failed to be applied due to an error. RDA does not detect if a different user changed the data. Therefore, RDA always has the behavior of "last writer wins" when pushing data to the server. Conflicts are not managed, but are optionally reported in an error table on the client. No conflict resolvers are supported, since conflicts are not resolved.
SQL Server Management Studio includes an extensive toolset to create and manage subscriptions, including monitoring tools to manage multiple subscriptions and watch performance and synchronization times.
RDA can only be used through code; no tools are available in SQL Server or SQL Server Mobile to pull and push changes, or to monitor clients.
In this article, you have learned about the key differences between the major features of merge replication and RDA. With this information, you can successfully choose the appropriate connectivity solution for your SQL Server Mobile application needs. For example, your application may benefit from the simplicity of RDA or the robust functionality of merge replication. In some cases, you may choose to combine the features of both RDA and merge replication in a single solution for non-conflicting data at the server.