Replication Limitations

When you use merge replication with Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) subscribers, there are several limitations on the publication. This section describes these limitations.

Schema Change Restrictions at the Subscriber

You can make some schema changes at the Subscriber, but not all. The following table describes the possible schema changes that can and cannot be performed at the Subscriber.

Additional Requirements

The following rules apply to SQL Server Compact Edition subscribers:

• Memory Consumption
  During the synchronization after reinitialization has been requested, the SQL Server Compact Edition database might temporarily grow to double the size of the actual database, but will shrink after synchronization is complete (based on your auto-shrink policy.)

• Subscription Filter Validation
  Filter validation lets the Publisher to verify a Subscriber's filter value before synchronizing data. If the filter value has changed on the Subscriber, the Publisher requires a reinitialization at the Subscriber. When synchronizing data with SQL Server 2005, SQL Server Compact Edition can use filter validation.

  Note

  The HostName property specifies the dynamic filter value for the subscription. If you want to change the dynamic filter value for the Subscriber by specifying a new HostName property, you must call the Reinitialize method before you synchronize.

• Case sensitivity
  SQL Server Compact Edition databases are not case-sensitive. Objects and data are treated differently in a SQL Server Compact Edition database than they are treated in a SQL Server database. For example, two tables, MYTABLE and mytable, are treated as different objects in a case-sensitive SQL Server database. The same table names cause a naming conflict in a SQL Server Compact Edition database. Similarly, two values, MYVALUE and myvalue, would cause a primary key violation in SQL Server Compact Edition, because they are treated as the same value, whereas they do not cause a violation in a case-sensitive SQL Server database.

• Number of columns in a table
  SQL Server Compact Edition allocates seven system columns for tracking. However, these system tables do not count against the limit on the number of columns.

• NOT FOR REPLICATION constraints
  SQL Server Compact Edition does not support the NOT FOR REPLICATION option. Do not create constraints using this option. If constraints in a database have the NOT FOR REPLICATION option, remove the constraint and then re-create it. If the NOT FOR REPLICATION option is specified, the constraint is still created on the SQL Server Compact Edition Subscriber, but it does not include the NOT FOR REPLICATION syntax.

• Connection time-out
  SQL Server Compact Edition subscriptions recover from communication failures by restarting from the last successfully transmitted block of data, as long as the connection is restored within the user-configurable time-out period. This makes synchronization possible even if the underlying transport is not reliable or is temporarily unavailable.

• Snapshots
  SQL Server Compact Edition supports both the default and the alternative snapshot location option. To specify an alternative snapshot location, you can set this option by using either the sp_addmergepublication stored procedure or by using SQL Server Management Studio to change the properties of an existing publication.

  • To use the sp_addmergepublication stored procedure
    Set the @snapshot_in_defaultfolder option to FALSE and @alt_snapshot_folder= location of the alternative folder for the snapshot.
  • To change the Publication Properties of an existing publication
    On the Snapshot section of the Properties dialog box for the publication, clear the Put files in the default folder check box, select the Put files in the following folder check box, and then specify the new location.

  Note

  SQL Server Compact Edition does not support the compressed snapshot option.

For more information about snapshots, see Configuring the Snapshot Folder.

Information that is not propagated to a SQL Server Compact Edition Subscriber

You can include the following items in a SQL Server publication, but they are not propagated to the SQL Server Compact Edition Subscriber:

• CHECK constraints
• Extended properties
• Stored procedures
• Views
• User-defined functions
• Triggers

Because SQL Server Compact Edition replication cannot propagate these items, you must implement equivalent logic in a SQL Server Compact Edition-based application. Doing this ensures that the SQL Server Compact Edition database remains consistent with the SQL Server database. For example, if the SQL Server database includes a CHECK constraint, the SQL Server Compact Edition-based application should implement the corresponding check in application code.

Creating Indexes on a Local SQL Server Compact Edition Database

You can create unique indexes on a local SQL Server Compact Edition database, even if that database is a subscriber to a Publication. This is useful, but it might cause problems if the Publisher, which does not have a unique constraint on a column, sends non-unique data to the subscriber. For example, you might have a table T with columns C1 and C2, and the primary key exists on C1.

An initial synchronization takes place on SQL Server Compact Edition database. After this synchronization, a unique index is added to C2 on the subscription database. No change is made to the publication database.

During the next synchronization, SQL Server inserts following rows:

In this case, synchronization fails because the publisher attempted to insert non-unique values for C2. Synchronization will continue to fail until the unique constraint is removed from the subscription database.