Training
Learning path
Implement finance and operations apps - Training
Plan and design your project methodology to successfully implement finance and operations apps with FastTrack services, data management and more.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server
This topic explains how to specify various properties for your merge replication.
Download-only articles are designed for applications with data that is not updated at Subscribers. For more information, see Optimize Merge Replication Performance with Download-Only Articles.
On the Articles page of the New Publication Wizard select a table, and then select the checkbox Highlighted table is download-only.
On the Articles page of the New Publication Wizard or the Publication Properties - <Publication> dialog box, select a table, and then click Article Properties.
Click Set Properties of Highlighted Table Article or Set Properties of All Table Articles.
In the Destination Object section of the Properties tab of the Article Properties - <Article> dialog box, specify one of the following values for Synchronization direction:
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.
Execute sp_addmergearticle, specifying a value of 1 or 2 for the parameter @subscriber_upload_options
. The numbers correspond to the following behavior:
Note
If the source table for an article is already published in another publication, the value of @subscriber_upload_options
must be the same for both articles.
To determine if an article is download-only, execute sp_helpmergearticle and verify value of upload_options for the article in the result set.
If the value returned in step 1 is 0, execute sp_changemergearticle, specifying a value of subscriber_upload_options for @property
, a value of 1 for @force_invalidate_snapshot
and @force_reinit_subscription
, and a value of 1 or 2 for @value
, which corresponds to the following behavior:
1 - Changes are allowed at the Subscriber, but they are not uploaded to the Publisher.
2 - Changes are not allowed at the Subscriber.
Note
If the source table for an article is already published in another publication, the download-only behavior must be the same for both articles.
Microsoft SQL Server replication provides an Interactive Resolver, which allows you to resolve conflicts manually during on-demand synchronization in Microsoft Windows Synchronization Manager. After interactive resolution is enabled, resolve conflicts interactively during synchronization, using the Interactive Resolver. The Interactive Resolver is available through the Microsoft Windows Synchronization Manager. For more information, see Synchronize a Subscription Using Windows Synchronization Manager (Windows Synchronization Manager).
You can programmatically specify that a Subscriber will use this graphical interface to resolve article conflicts when a pull subscription to a merge publication is created. Only conflicts in articles that support this option will be displayed in the Interactive Resolver.
@publication
. Note the value of allow_interactive_resolver for each article in the result set for which the Interactive Resolver will be used.
@publication
, @article
, a value of allow_interactive_resolver for @property
, and a value of true for @value
.@publisher
, @publisher_db
(the published database), and @publication
.@enabled_for_syncmgr
.@use_interactive_resolver
.@publication
, a name for the article for @article
, the database object being published for @source_object
, and a value of true for @allow_interactive_resolver
. For more information, see Define an Article.This topic describes how to specify the conflict tracking and resolution level for merge articles in SQL Server by using SQL Server Management Studio or Transact-SQL.
When a subscription to a merge publication is synchronized, replication checks for conflicts caused by changes to the same data made at both the Publisher and the Subscriber. You can specify whether conflicts are detected at the row-level, where any change to the row is considered a conflict, or column-level, where only changes to the same row and column are considered a conflict. Conflict resolution for articles is performed at the row-level. For more information about conflict detection and resolution when logical records are used, see Detecting and Resolving Conflicts in Logical Records.
Specify row- or column-level tracking for merge articles on the Properties tab of the Article Properties dialog box, which is available in the New Publication Wizard and the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a Publication and View and Modify Publication Properties.
At the Publisher on the publication database, execute sp_addmergearticle and specify one of the following values for @column_tracking
:
To determine the conflict tracking options for a merge article, execute sp_helpmergearticle. Note the value of the column_tracking option in the result set for the article. A value of 1 means that column-level tracking is being used, and a value of 0 means that row-level tracking is being used.
At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of column_tracking for @property
and one of the following values for @value
:
Specify a value of 1 for both @force_invalidate_snapshot
and @force_reinit_subscription
.
Note
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
By default, merge replication synchronizes DELETE commands between the Publisher and Subscriber. Merge replication enables you to retain rows in the subscription database even when they have been deleted from the publication, and vice versa. You can programmatically specify that DELETE commands be ignored when creating a new article or you can enable this functionality at a later time using replication stored procedures.
Important
Enabling this functionality will result in non-convergence, which means that data present at the Subscriber will not accurately reflect data at the Publisher. You must implement your own mechanism for manually removing deleted rows.
At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify a value of false for @delete_tracking
. For more information, see Define an Article.
Note
If the source table for an article is already published in another publication, the value of delete_tracking must be the same for both articles.
To determine if error compensation is enabled for an article, execute sp_helpmergearticle (Transact-SQL) and note the value of delete_tracking in the result set. If this value is 0, deletes are already being ignored.
If the value from step 1 is 1, execute sp_changemergearticle (Transact-SQL) at the Publisher on the publication database. Specify a value of delete_tracking
for @property
, and a value of false for @value
.
Note
If the source table for an article is already published in another publication, the value of delete_tracking must be the same for both articles.
Merge replication enables you to specify the order in which articles are processed by the Merge Agent during the synchronization process. You can assign an order to each article programmatically when creating an article using replication stored procedures. Articles are processed in order from lowest to highest value. If two articles have the same value, they are processed concurrently.
During merge synchronization, articles are, by default, processed in the order required by the dependencies between objects, including the declarative referential integrity (DRI) constraints defined on the base tables. Processing involves enumerating the changes to a table and then applying those changes. If no DRI is present but join filters or logical records exist between table articles, the articles are processed in the order required by the filters and logical records. Articles not related to any other article through DRI, join filters, logical records, or other dependencies are processed according to the article nickname in the sysmergearticles (Transact-SQL) system table.
Consider a publication that includes the tables SalesOrderHeader and SalesOrderDetail with a primary key column SalesOrderID in the SalesOrderHeader table and a corresponding foreign key column SalesOrderID in the SalesOrderDetail table. During synchronization, merge replication prevents foreign key violations by inserting any new rows in SalesOrderHeader before inserting associated rows in SalesOrderDetail. Similarly, rows are deleted from SalesOrderDetail before the associated row is deleted from SalesOrderHeader.
However, in some applications referential integrity is enforced through database triggers, or at the application level, rather than through DRI. Given the publication described above, instead of DRI, the SalesOrderDetail table could have an insert trigger that ensures the associated row in the SalesOrderHeader table exists before allowing an insert. SalesOrderHeader could have a delete trigger that ensures there are no associated rows in SalesOrderDetail before allowing a delete. Merge replication does not take into account triggers when determining processing order of articles because it cannot determine what the result of the trigger will be until it has fired. Similarly, replication cannot take into account constraints defined at the application level.
When referential integrity is maintained through triggers or at the application level, you should specify the order in which the articles should be processed. In the example with triggers, you would specify that the SalesOrderHeader table should be processed before SalesOrderDetail, because article ordering is based on insert order. Merge replication will automatically reverse the order for deletes. Merge replication will not fail without article ordering, because the Merge Agent continues to process articles if a constraint violation occurs; it then retries any operations that failed after other articles have been processed. Specifying article order simply avoids retries and the additional processing associated with them. If you specify an incorrect order (for example, one that results in detail records being processed before header records), merge replication will retry processing until it succeeds.
At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify an integer value that represents the processing order for the article for @processing_order
. For more information, see Define an Article.
Note
When creating ordered articles, you should leave gaps between the article order values. This makes it easier to set new values in the future. For example, if you have three articles for which you need to specify a fixed processing order, set the value of @processing_order
to 10, 20, and 30 rather than 1, 2, and 3, respectively.
@property
and an integer value that represents the processing order for @value
.Training
Learning path
Implement finance and operations apps - Training
Plan and design your project methodology to successfully implement finance and operations apps with FastTrack services, data management and more.