Supporting Multiple Subscriptions

  • Article

Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) subscribers can subscribe to multiple publications.

Multiple Subscriptions

By supporting multiple subscriptions in SQL Server Compact Edition, you gain the following advantages:

  1. You can reduce the number of articles in each publication.
  2. You can control when data from each publication is synchronized.
  3. You can query across multiple tables in each publication.

For example, suppose your application displays customer information. Half of the data in your subscription is a list of customers, stored as read-only data. The other half is order data. Your users expect to be able to query across both tables to look up orders by customer, for example, and to add new order information.

For performance reasons, you want these different data types to be synchronized at different frequencies. For data integrity reasons, you only want to bring down changes to the customer list that were made at the Publisher. By subscribing to two different publications, these requirements are easily met. Each publication is configured to meet your performance and data integrity needs, and your single SQL Server Compact Edition database will contain the tables from each publication.

Note

You can create different subscriptions to multiple publications on the same database but if the publications exist on the same server in the same publisher database, the publication names must be different.

Requirements for Multiple Subscriptions

For a single database to contain multiple subscriptions, there are two restrictions:

  1. All objects (tables, constraints, and indexes) in each publication must be non-intersecting.
  2. No referential integrity (R/I) can exist between tables from different subscriptions. However, you can implement indexing to improve performance.
  3. The R/I changes on the articles at the publication database are sent to SQL Server Compact Edition subscribers. If you have two subscriptions inside one SQL Server Compact Edition database, and if the database has a local table with the same name as the other tables on the publisher, then this will cause unexpected schema changes.