Breaking Changes in SQL Server 2005 Replication
This topic describes replication feature changes that might require changes to applications.
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.
Breaking Changes That Affect All Types of Replication
The following breaking changes apply to all types of replication in Microsoft SQL Server 2005.
| Feature | Description |
|---|---|
Changes required for replication scripts |
The replication agent security model has changed from Microsoft SQL Server 2000. For detailed information about the security model, see Replication Agent Security Model. If you are a member of the sysadmin fixed server role in SQL Server 2005 and you run replication scripts created from SQL Server 2000 or SQL Server 7.0, the scripts execute properly. If you are a member of the dbo fixed database role or another role, the scripts fail and must be upgraded. For information about upgrading scripts, see How to: Upgrade Replication Scripts (Replication Transact-SQL Programming). Although it is not required to upgrade scripts that are executed by members of the sysadmin role, it is recommended in order to take advantage of security enhancements. |
Local connections for replication agents |
On upgrade to SQL Server 2005, any local connections that use SQL Server Authentication are modified to use Windows Authentication. Local connections are those connections made by an agent to an instance of SQL Server running on the same computer as the agent. For example, the Merge Agent for a pull subscription runs at the Subscriber, so the connections it makes to the Subscriber are local connections. In previous versions of SQL Server, agents ran, by default, under the context of the SQL Server Agent service account. After upgrade, local connections are made under the context of this account. SQL Server 2005 allows fine-grained control over each account under which the replication agents run and make Windows Integrated connections to databases and other resources; a different account can be specified for each agent. After upgrade, it is recommended to specify different accounts for each agent. For more information, see Upgrading Replicated Databases and Replication Agent Security Model. |
ActiveX controls |
All ActiveX controls are marked as unsafe for scripting and initialization. The Snapshot Agent ActiveX control is not available in SQL Server 2005. Use the new managed Snapshot Agent instead. For more information, see SnapshotGenerationAgent and How to: Create the Initial Snapshot (RMO Programming). |
Password for the distributor_admin account |
Trusted connections between a Publisher and a remote Distributor are no longer supported because they did not require a password (trusted connections were used by default in versions prior to SQL Server 2000 Service Pack 3). If you use a remote Distributor, before upgrading to SQL Server 2005, convert trusted connections to non-trusted connections (this issue does not affect Publishers that use a local Distributor). For more information about the distributor_admin account, see Securing the Distributor. To determine the type of connection being used
To change to a non-trusted connection
|
SQL Server Express does not include SQL Server Agent |
If you are upgrading to SQL Server Express, you must reconfigure replication synchronization because SQL Server Express does not include SQL Server Agent. If you want to use pull subscriptions, you must synchronize them using Replication Management Objects (RMO), Windows Synchronization Manager, or by running the replication agent at the command line. For more information, see Replicating Data to SQL Server Express. If you want to continue to use SQL Server Agent to run replication agent jobs, you must use push subscriptions or upgrade to a different version of SQL Server (all versions except SQL Server Express and Microsoft SQL Server 2005 Compact Edition include SQL Server Agent). With push subscriptions, the Distribution Agent or Merge Agent runs at the Distributor, so SQL Server Agent is available (SQL Server Express cannot be a Distributor). |
Microsoft Access (Jet 4.0) Subscribers |
Jet is the underlying database used by Access, and replication supported subscriptions to Jet databases in SQL Server 2000. These subscriptions are no longer supported. It is recommended to use Microsoft SQL Server 2005 Express Edition instead. Access can use a SQL Server database as a backend, and SQL Server databases are not affected by this issue. For more information, see Replicating Data to SQL Server Express. |
Breaking Changes for Transactional Replication
The following breaking changes apply to transactional replication in SQL Server 2005.
Feature
Description
Message Queuing option for queued updating subscription
With queued updating subscriptions, changes from Subscribers are written to a queue; changes are then read from the queue and delivered to the Publisher by the Queue Reader Agent. In SQL Server 2000, subscriptions could use a SQL Server queue or Message Queuing to queue changes. The type of queue was specified with the @queue_type parameter of sp_addpublication (Transact-SQL), which allowed values of sql and msmq. In SQL Server 2005, only a value of sql is allowed. Existing publications that use Message Queuing are modified during upgrade to use a SQL Server queue. If you have applications that depend on queued updating using Message Queuing, these applications will need to be rewritten to accommodate a SQL Server queue. For more information on queued updating subscriptions, see Updatable Subscriptions for Transactional Replication.
Upgrade will remove the existing Message Queuing (MSMQ) subscription queues if the Message Queuing service is running and SQL Server is being upgraded.
.gif "ms143470.note(en-US,SQL.90).gif")
Important:
In Windows 2000 and Windows XP, the Microsoft Distributed Transaction Coordinator (MSDTC) service must also be running, because Message Queuing requires MSDTC on those operating systems.
If the Message Queuing service is not running, remove the queues manually after the upgrade is completed. For more information about how to remove queues, see the Windows documentation.
Breaking a Changes for Merge Replication
The following breaking changes apply to merge replication in SQL Server 2005.
| Feature | Description |
|---|---|
Publishing from SQL Server Express |
SQL Server MSDE could serve as a Publisher for merge publications. SQL Server Express, the replacement for MSDE, cannot server as a Publisher. It can subscribe to merge, transactional, and snapshot publications. Merge replication and transactional replication with updating subscriptions both allow changes to be propagated from Subscribers back to the Publisher. For more information about replicating to SQL Server Express, see Replicating Data to SQL Server Express. |
Batching of changes |
In previous versions of SQL Server, changes made by the Merge Agent were performed on a row-by-row basis. In SQL Server 2005, changes are batched to improve performance; therefore, more than one row can be inserted, updated, or deleted within a single statement. If any published tables in the publication or subscription databases have triggers, ensure that the triggers can handle multi-row inserts, updates, and deletes. For more information, see Multirow Considerations for DML Triggers. |
Re-creation of conflict tables |
On upgrade to SQL Server 2005, conflict tables are re-created with DBO as their owner. If any of the tables were owned by other users in SQL Server 2000, your application might need to be modified. Merge replication creates a conflict table for each article in a publication, with a name in the form conflict_PublicationName_ArticleName. All metadata tables are re-created on upgrade, and all conflict tables are created in the DBO schema. |
New identity ranges assigned |
For tables that use automatic identity range management, replication might assign new identity ranges during upgrade. If any tables have a larger identity range assigned to the Subscriber than to the Publisher, replication assigns a range to the Publisher equal to that of the Subscriber. To determine ranges being used for each article, execute sp_helpmergearticle (Transact-SQL) in the publication database and check the pub_identity_range and identity_range columns. |
See Also
Concepts
Replication Backward Compatibility