Behavior Changes in SQL Server Replication
Note
For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.
This topic describes behavior changes in SQL Server Replication. Behavior changes affect how features work or interact in SQL Server 2008 as compared to earlier versions of SQL Server.
Behavior Changes Made in SQL Server 2005
This section describes behavior changes made to replication features in SQL Server 2005.
Behavior Changes That Affect All Types of Replication
The following changes affect all types of replication.
Feature |
Description |
|---|---|
Replication agent security model |
In previous versions of SQL Server, agents ran, by default, under the context of the SQL Server Agent service account. SQL Server now allows fine-grained control over each account under which the replication agents run and make Microsoft Windows Integrated connections to databases and other resources; a different account can be specified for each agent. For more information, see Security and Protection (Replication) and Replication Agent Security Model. For information about how this change affects upgrade, see the "New Replication Agent Security Model" section of Considerations for Upgrading Replicated Databases and Breaking Changes in SQL Server Replication. |
Windows Synchronization Manager |
In versions of SQL Server prior to SQL Server 2005, the ability to synchronize subscriptions with Synchronization Manager was enabled by default. In SQL Server 2005, you must explicitly enable this option if you want to use Synchronization Manager. For more information, see How to: Synchronize a Subscription Using Windows Synchronization Manager (Windows Synchronization Manager). |
Replication Conflict Viewer |
In SQL Server 2000, the Replication Conflict Viewer was packaged for redistribution. In SQL Server 2005, the viewer is not packaged separately. To include the Replication Conflict Viewer in an application, you must install Microsoft .NET Framework 2.0 on the computer on which the application is deployed, and copy a number of files to the computer. For more information, see "Other Replication Upgrade Issues" in Upgrade Advisor Help. For more information about Upgrade Advisor, see Using Upgrade Advisor to Prepare for Upgrades. |
Schema option changes |
Schema options allow you to specify how attributes and objects associated with tables, such as indexes and constraints, are replicated. The behavior of a number of schema options has changed in SQL Server 2005. The next section of this topic provides more information. |
Schema Options Behavior Changes
The following table summarizes schema option changes in SQL Server 2005.
Note
If the 0x8000 schema option was set in SQL Server 2000, it is disabled during upgrade to SQL Server 2005. For schema options 0x10 or 0x40, replication might create a larger number of indexes in SQL Server 2005 than in SQL Server 2000.
Option |
Behavior if the option is set in SQL Server 2000 |
Behavior if the option is set in SQL Server 2005 |
|---|---|---|
0x80 |
Creates a constraint or index. If option 0x8000 is also enabled, the primary key is created as a constraint with an index. If option 0x8000 is not enabled, only the index on the primary key column is created. |
Creates a primary key constraint at the Subscriber. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled (these options control index creation for other cases). |
0x4000 |
Creates a constraint or index. If option 0x8000 is also enabled, the unique constraint is created as a constraint with an index. If option 0x8000 is not enabled, only the index on the column is created. |
Creates any unique constraints at the Subscriber. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enabled (these options control index creation for other cases). |
0x8000 |
Creates primary key constraints and unique constraints if options 0x80 or 0x4000 are also specified. If neither of these options is specified, option 0x8000 has no effect. |
The option has no effect. |
Behavior Changes for Transactional Replication
The following changes affect transactional replication.
Feature |
Description |
|---|---|
Subscriber object ownership |
When using the SQL Server 2005 New Publication Wizard to create a publication, the owner of objects created at the Subscriber defaults to the value of the owner of the corresponding object on the Publisher. In previous releases, the owner was not specified during the creation of the object on the Subscriber; it defaulted to the owner associated with the Distribution Agent account used to connect to the Subscriber. The behavior is unchanged for the stored procedure sp_addarticle (Transact-SQL). |
Updatable subscriptions security mode |
The @security_mode parameter of sp_link_publication governs how the triggers for immediate updating subscriptions execute calls at the Publisher. In SQL Server 2005, the options for this parameter are:
In previous versions of SQL Server, option 0 was used to specify a dynamic remote procedure call (RPC) from the Subscriber to the Publisher rather than a linked server. |
Behavior Changes for Merge Replication
The following changes affect merge replication.
Feature |
Description |
|---|---|
Publication compatibility level |
In previous versions of SQL Server, the compatibility level was automatically increased if you enabled a feature that required a higher level. In SQL Server 2005, you must manually set the compatibility level to 90RTM before enabling functionality that requires that compatibility level. For more information, see the "Compatibility Level for Merge Publications" section of Using Multiple Versions of SQL Server in a Replication Topology. |
Compensating actions |
In previous versions of SQL Server, compensating actions were taken if errors (such as constraint violations) were encountered during synchronization. In some cases this behavior is desirable, but in some cases it can be problematic; for example, one incorrectly configured Subscriber that generates an error can cause changes to be undone at the Publisher and all other Subscribers. In SQL Server 2005, the @compensate_for_errors parameter of sp_addmergearticle controls whether compensating actions are taken. When set to False (the default), compensating actions are disabled; however, the errors are still logged and subsequent merges will continue to attempt to apply the changes. Although data in the affected rows might appear to be out of convergence, as soon as you address the error, the change can be applied and data will converge. When set to True, a change that cannot be applied at a node during synchronization leads to compensating actions that undo the change at all other nodes. .gif "Note") Note
If the source table for an article is already published in another publication, the value of @compensate_for_errors must be the same for both articles. For pull subscriptions at Subscribers running SQL Server 2000 version 8.00.858 and earlier (which includes Service Pack 3), compensating actions will still occur even with @compensate_for_errors set to False.
|
Conflict tables |
In previous versions of SQL Server, merge replication created a single conflict table for each table article in a publication, with a name in the form conflict_<ArticleName>. In SQL Server 2005, the information is contained in two tables: MSmerge_conflicts_info and a table with a name in the form MSmerge_conflict_<PublicationName>_<ArticleName>. |
Retention-based metadata cleanup |
SQL Server 2005 uses retention-based metadata cleanup, which was introduced in SQL Server 2000 Service Pack 1. Metadata is periodically deleted from the following system tables:
|
The @keep_partition_changes parameter |
The @keep_partition_changes parameter was set to False by default in previous version of SQL Server, because it results in more data being stored at the Publisher. It is now set to True if the publication compatibility level is 90RTM or higher, and the @use_partition_groups parameter is set to False. For more information about these options, see Parameterized Row Filters. |