Changes required for replication scripts

The replication agent security model has changed from 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 Considerations for 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

• Execute sp_helpdistpublisher at the Distributor. If the value in the trusted column is 1, you must change to a non-trusted connection.
  

To change to a non-trusted connection

1. Execute sp_changedistpublisher at the Distributor, specifying a value of 'trusted' for the parameter @property and a value of 'False' for the parameter @value.
   

   Note:
   Some versions of the SQL Server 2000 Books Online do not list 'trusted' as a valid value for @property. It is valid for all SQL Server 2000 releases.

2. Execute sp_changedistributor_password at both the Publisher and the Distributor, specifying a strong password for the parameter @password.
   

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 SQL Server Compact 3.5 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 SQL Server Express 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.

Initializing a transactional subscription from a backup¹

To initialize a subscription from a backup in SQL Server 2008, a user must be a member of the dbcreator server role. In SQL Server 2005, membership in the db_owner database role was sufficient.

For more information about how to initialize a subscription from a backup, see Initializing a Transactional Subscription Without a Snapshot.

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, which allowed values of sql and msmq (Message Queuing). 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 subscription queues if the Message Queuing service is running while SQL Server is being upgraded.

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 platforms.

If the Message Queuing service is not running, remove the queues manually after upgrade is complete. For more information about removing queues, see the Windows documentation.

Change to call format for updating subscriptions

By default, a set of stored procedures is used to apply changes to Subscribers in transactional replication. Each procedure has a call format. This format determines how parameters are passed to the procedure and the amount of data that is sent to the Subscriber. In SQL Server 2000, the default format is CALL. In SQL Server 2005 and SQL Server 2008, the default format is VCALL.

This change only affects topologies in which the stored procedures have been customized. After upgrade, you must change the signature of the customized procedure to include additional parameters. Otherwise, the Distribution Agent will fail.

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 in the publication database and check the pub_identity_range and identity_range columns.