Replicating Data to SQL Server Compact

Merge replication allows you to replicate data to SQL Server Compact 3.5 SP1, providing a convenient way for mobile users to synchronize data changes with a central SQL Server database. Many applications require data to be available to remote users, including sales people, delivery drivers, and so on. These applications include customer relationship management (CRM), sales force automation (SFA), and field force automation (FFA) applications. For more information about these scenarios, see Exchanging Data with Mobile Users

Components Required for Replication to SQL Server Compact

Merge replication synchronizes data with SQL Server Compact 3.5 SP1 using Web synchronization. The format in which data changes are delivered to SQL Server Compact 3.5 SP1 is different from other SQL Server editions, but the architecture is essentially the same: a Subscriber sends its changes to the Publisher through Microsoft Internet Information Services (IIS) server, and receives its changes from the Publisher through the same IIS server. For more information about the architecture for SQL Server Compact 3.5 SP1 synchronization, see Replication Architecture. For more information about configuring the IIS server for connectivity, see Configuring and Securing Server Environment.

SQL Server 2008 supports synchronization for Subscribers that are running SQL Server Compact versions 3.0, 3.1, and 3.5. For all three versions, you must use the SQL Server Compact 3.5 IIS components and SQL Server 2008 IIS components on the IIS server. You must set the publication compatibility level to 90RTM or 100RTM. For more information about compatibility levels, see Using Multiple Versions of SQL Server in a Replication Topology.

Installing Components for a New Installation

To install components for a new installation, follow these steps:

  1. Install SQL Server 2008 on the Publisher.

  2. Install SQL Server Compact 3.5 SP1 on each Subscriber.

  3. Install SQL Server 2008 Web Synchronization Wizard and client connectivity components on the IIS server.

  4. Install SQL Server Compact 3.5 SP1 Server Tools on the IIS server.

  5. Configure Web Synchronization. This copies the SQL Server Compact 3.5 SP1 Server Agent to the virtual directory that Web synchronization uses.

Installing Components for an Existing Installation

To upgrade a topology from SQL Server Compact 3.5 3.0 or 3.1 to SQL Server Compact 3.5 SP1 3.5, and SQL Server 2005 to SQL Server 2008, follow these steps:

  1. Upgrade SQL Server Compact 3.5 on each Subscriber.

  2. Upgrade SQL Server on the Publisher.

  3. Uninstall SQL Server Compact 3.5 Server Tools from the IIS server.

  4. Uninstall the Web Synchronization Wizard and client connectivity components from the IIS server.

  5. Remove the virtual directories that were configured.

  6. Install SQL Server 2008 Web Synchronization Wizard and client connectivity components on the IIS server.

  7. Install SQL Server Compact 3.5 SP1 Server Tools on the IIS server.

  8. Configure Web Synchronization. This copies the SQL Server Compact 3.5 SP1 Server Agent to the virtual directory that Web synchronization uses.

Creating Publications and Subscriptions for SQL Server Compact

Publications are created by using the New Publication Wizard or the sp_addmergepublication stored procedure. Publications that support SQL Server Compact 3.5 SP1 subscriptions have a number of requirements and limitations (for example, character mode snapshot is required):

SQL Server 2008 supports synchronization for Subscribers that are running SQL Server Compact versions 3.0, 3.1, and 3.5. Subscriptions are created using the New Subscription Wizard or programmatically. For more information, see Creating a Subscription.

SQL Server Management Studio and Replication Monitor

Like standard SQL Server subscriptions, SQL Server Compact 3.5 SP1 subscriptions are displayed in SQL Server Management Studio and Replication Monitor. For information about synchronizing subscriptions in SQL Server Management Studio, see Synchronous Data Synchronization.