Optimizing Merge Replication Performance with Download-Only Articles
Merge replication offers two different article types to address different application needs. Publications can contain one or more of each of these article types as appropriate for the application:
Download-only articles provide performance advantages over standard articles and should be used where appropriate.
|To use download-only articles, the compatibility level of the publication must be at least 90RTM. For more information, see the "Compatibility Level for Merge Publications" section in the topic Using Multiple Versions of SQL Server in a Replication Topology.|
Standard articles are the default, offering the full range of merge replication features, including rich conflict detection and resolution. Standard articles are appropriate for tables that are updated by multiple Subscribers; objects other than tables, such as stored procedures and views, are always published as standard articles.
Download-only articles are designed for applications with data that is not updated at Subscribers, such as a set of articles that are contained in a product catalog. A product catalog is typically updated at the Publisher, but not at the Subscribers. Because download-only articles cannot be updated at the Subscriber, tracking metadata is not sent to Subscribers. This can lead to reduced storage on the Subscribers and a performance benefit, especially if the network connection is slow.
Download-only articles work in conjunction with client subscriptions: if an article is designated as download-only, rows for that article cannot be inserted, updated, or deleted at Subscribers who use client subscriptions. Publishers and Subscribers that use the server subscription type (typically Subscribers that republish data to other Subscribers) can insert, update, and delete data. For more information about client subscriptions, see Subscribing to Publications.
To specify that an article is download-only
SQL Server Management Studio: How to: Specify That a Merge Table Article is Download-Only (SQL Server Management Studio)
Replication Transact-SQL programming: How to: Specify That a Merge Table Article is Download-Only (Replication Transact-SQL Programming)
By understanding the requirements of your application, you can make tradeoffs between maximum flexibility and optimal performance. For example, applications with numerous conflicts and changes at both the Publisher and Subscribers will use a publication made up of standard articles. Some applications, such as a sales force automation application, might have articles with a potential for conflicts, and other articles that function as lookup tables, which can be specified as download-only. Data entry applications, such as point of sales systems and field force automation applications, often strictly partition data in a way that conflicts are eliminated, and data from one Subscriber never goes to another. In these situations, a combination of nonoverlapping partitions, download-only articles and precomputed partitions provides maximum performance and scalability. For more information about nonoverlapping partitions and precomputed partitions, see Parameterized Row Filters.