Partilhar via

Behavior Changes in SQL Server 2005 Replication

  • Artigo

This topic describes behavior changes in replication features that existed in Microsoft SQL Server 2000. For information about replication features enhancements, see Replication Enhancements.

Note

This topic is available in the Setup Help documentation and in SQL Server 2005 Books Online. Topic links that appear as bold text in the Setup Help documentation refer to topics that are only available in Books Online.

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. Microsoft SQL Server 2005 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 Considerations for Replication and Replication Agent Security Model. For information about how this change affects upgrade, see the "New Replication Agent Security Model" section of Upgrading Replicated Databases and Breaking Changes in SQL Server 2005 Replication.

Windows Synchronization Manager

In previous versions of SQL Server, 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:

  • 0: Uses SQL Server Authentication with the login specified in the stored procedure as login and password.
  • 1: Uses the security context (SQL Server Authentication or Windows Integrated Authentication) of the user making the change at the Subscriber.
  • 2: Uses an existing, user-defined linked server login.

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.

ms143733.note(en-US,SQL.90).gifNote:

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:

  • MSmerge_contents
  • MSmerge_tombstone
  • MSmerge_genhistory
  • Any before image tables, if present. Before image tables are present if the @keep_partition_changes synchronization optimization option is enabled on the publication (see the next section for more information about this option).

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.

See Also

Concepts

Replication Backward Compatibility

Other Resources

Replication Enhancements

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added information about behavior changes for schema options.