SQL Server 2008 R2

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

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.


Permitted on a Replicated Table

Delete a table


Rename a table


Add/Drop/Remove a column


Change the seed or increment on an Identity column


Add or Drop default


Add or Drop the primary key


Add or Drop the foreign key


Add or Drop the index

It is recommended not to add/drop indexes on replication columns in user tables. This results in synchronization failure because replication columns are treated as system columns.


Rename the index


Schema changes cannot be made on system tables.

The following rules apply to SQL Server Compact 3.5 subscribers:

  • Memory Consumption

    During the synchronization after reinitialization has been requested, the SQL Server Compact 3.5 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 2008 R2, SQL Server Compact 3.5 can use filter validation.

    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

    Starting with the SQL Server Compact 3.5 Service Pack 1 release, SQL Server Compact supports setting the case-sensitive collations at the database level. However, object names, such as table names, language keywords, functions, and views, are not treated as case-sensitive in a SQL Server Compact database, even if the collation is case-sensitive.

    In other words, objects are treated differently in a SQL Server Compact 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 database. On the other hand, two values, MYVALUE and myvalue, would no longer cause a primary key violation in SQL Server Compact, because they are treated as the different value, just like they do not cause a violation in a case-sensitive SQL Server database.

  • Number of columns in a table

    SQL Server Compact 3.5 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 3.5 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 3.5 Subscriber, but it does not include the NOT FOR REPLICATION syntax.

  • Connection time-out

    SQL Server Compact 3.5 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 3.5 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 Snapshotsection 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.

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

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

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

  • CHECK constraints

  • Extended properties

  • Stored procedures

  • Views

  • User-defined functions

  • Triggers

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

You can create unique indexes on a local SQL Server Compact 3.5 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 3.5 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.