Using Multiple Versions of SQL Server in a Replication Topology

Replication supports replicating data to different versions of Microsoft SQL Server. This topic covers:

• SQL Server versions supported
• Mapping SQL Server 2005 data types for previous versions
• Restoring a replicated database from a previous version
• Compatibility level for merge publications

For information about replicating data to Microsoft SQL Server 2005 Express Edition and Microsoft SQL Server Compact Edition, see Replicating Data to SQL Server Express and Replicating Data to SQL Server Compact Edition. For information about the features supported by each edition of SQL Server, see Features Supported by the Editions of SQL Server 2005.

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.

SQL Server Versions Supported

SQL Server version 7.0 Service Pack 4 (SP4) is the earliest version that can participate in a replication topology with SQL Server 2005. If you use SQL Server 2000, SP3 or later is required.

When you replicate between or among different versions of SQL Server, you are often limited to the functionality of the earliest version used. For example, if you upgrade a Distributor to an instance of SQL Server 2005, but you have a Publisher running an instance of SQL Server 2000, and a Subscriber running an instance of SQL Server 7.0, you are limited to the general functionality and replication functionality of SQL Server 7.0.

Note

Because the SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments, a replication topology can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.

For all types of replication, the Distributor version must be no earlier than the Publisher version (in many cases the Distributor is the same instance as the Publisher). You can use SQL Server 2005 as a remote Distributor for SQL Server 2005 and SQL Server 2000, but not for SQL Server 7.0. The following tables provide additional information about which versions of SQL Server can participate in the same topology. For more information about replication features supported in the various editions of SQL Server, see Features Supported by the Editions of SQL Server 2005.

Transactional Replication and Snapshot Replication with Read-Only Subscribers

Distributor	SQL Server 7.0	SQL Server 2000	SQL Server 2005
Publisher	SQL Server 7.0	SQL Server 7.0 SQL Server 2000	SQL Server 2000 SQL Server 2005
Subscribers	SQL Server 7.0 SQL Server 2000 SQL Server 2005	SQL Server 7.0 SQL Server 2000 SQL Server 2005	SQL Server 7.0 SQL Server 2000 SQL Server 2005

The above table shows that a read-only Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 7.0 Publisher can have SQL Server 2005 Subscribers, and a SQL Server 2005 Publisher can have SQL Server 7.0 Subscribers.

Transactional Replication with Updating Subscribers

Distributor	SQL Server 7.0	SQL Server 2000	SQL Server 2005
Publisher	SQL Server 7.01	SQL Server 7.01 SQL Server 20002	SQL Server 20002 SQL Server 20053
Subscribers	SQL Server 7.0	SQL Server 7.0 SQL Server 2000 SQL Server 2005	SQL Server 7.0 SQL Server 2000 SQL Server 2005

1 For a SQL Server 7.0 Publisher, only SQL Server 7.0 Subscribers are supported.

2 For a SQL Server 2000 Publisher, SQL Server 7.0, SQL Server 2000, and SQL Server 2005 Subscribers are supported.

3 For a SQL Server 2005 Publisher, SQL Server 2000 and SQL Server 2005 Subscribers are supported.

Merge Replication

Distributor	SQL Server 7.0	SQL Server 2000	SQL Server 2005
Publisher	SQL Server 7.0	SQL Server 7.0 SQL Server 2000	SQL Server 2000 SQL Server 2005
Subscribers	SQL Server 7.0	SQL Server 7.0 SQL Server 2000	SQL Server 7.0 SQL Server 2000 SQL Server 2005

The above table shows that a Subscriber to a merge publication can be any version no later than the Publisher version. For more information about compatibility for previous versions, see "Compatibility Level for Merge Publications" later in this topic.

SQL Server 7.0 and SQL Server Management Studio

SQL Server Management Studio can connect to instances running SQL Server 2000 or later. For Subscribers running SQL Server 7.0:

• Subscriptions and publications can be created with SQL Server 7.0 tools, SQL Server 2000 tools, SQL Distributed Management Objects (SQL-DMO), or stored procedures.
• Agents for pull subscriptions cannot be started from Management Studio or Replication Monitor. Agents can be specified to run on a schedule when the subscription is created, or they can be run on-demand from the command prompt.

Subscriptions for Subscribers running SQL Server 7.0 do appear in Management Studio and Replication Monitor after they are created. For information about creating subscriptions and publications, and running agents, see SQL Server 7.0 Books Online.

Using a SQL Server 2005 Distributor with a Publisher Running SQL Server 2000

SQL Server 2005 can be used as a remote Distributor for Publishers running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures allow you to change properties that are new in SQL Server 2005:

• sp_MSchange_snapshot_agent_properties (Transact-SQL)
• sp_MSchange_logreader_agent_properties (Transact-SQL)
• sp_MSchange_distribution_agent_properties (Transact-SQL)
• sp_MSchange_merge_agent_properties (Transact-SQL)

If you have a Publisher and Distributor running SQL Server 2000, it is possible to change the credentials under which agents make connections using sp_changedistpublisher and sp_changesubscriber. However, if you upgrade the Distributor to SQL Server 2005, these procedures cannot be used to change credentials used in existing agent jobs (the procedures do affect agent jobs that are created after the procedure is called). In order to change the credentials for existing agent jobs, call one of the four procedures listed above.

Mapping SQL Server 2005 Data Types for Previous Versions

SQL Server 2005 has introduced a number of new data types. These new data types are mapped to compatible data types at the Subscriber if push subscriptions from a SQL Server 2005 Distributor are used.

SQL Server 2005 data type	SQL Server 2000 or SQL Server 7.0 data type
xml	ntext
CLR user-defined types (UDT)	image
varchar(max)	text
nvarchar(max)	ntext
varbinary(max)	image

You must verify that varchar(max), nvarchar(max), varbinary(max), xmlL, and CLR user-defined types are mapped appropriately if they are replicated to Subscribers running earlier versions of SQL Server (this is done by default for articles in merge publications). Mapping behavior for these types is controlled by the schema options 0x20, 0x10000000, and 0x20000000 for sp_addarticle and sp_addmergearticle. For more information about setting schema options, see How to: Specify Schema Options (SQL Server Management Studio) and How to: Specify Schema Options (Replication Transact-SQL Programming).

SQL Server 2000 introduced two data types that are mapped to compatible data types for SQL Server 7.0. These new data types are mapped to compatible data types at the Subscriber if push subscriptions from a SQL Server 2005 or SQL Server 2000 Distributor are used.

SQL Server 2005 or SQL Server 2000 data type	SQL Server 7.0 data type
SQL_VARIANT	IMAGE
BIGINT	DECIMAL

Restoring a Replicated Database from a Previous Version

You can retain replication settings when restoring a backup of a replicated database from a previous version. If you restore the backups to a server and database with the same names as the server and database at which the backup was taken, or if you specify the KEEP_REPLICATION option, replication settings are preserved. For more information, see RESTORE (Transact-SQL). After restoring the database, execute sp_vupgrade_replication (Transact-SQL) to upgrade schema and system data to support replication at the current product level.

Although preserving replication after restoring from backup from previous version is possible, it is rarely used as an upgrade option. It is more common to upgrade the replicated database as part of a product upgrade or to re-create the database and replication configuration from a set of scripts.

Compatibility Level for Merge Publications

Merge replication uses the publication compatibility level to determine which features can be used by publications in a given database. The values range from 70RTM (SQL Server 7.0 with no service packs installed) to 90RTM. The compatibility level is specified:

• With the @publication_compatibility_level parameter of sp_addmergepublication. For more information, see How to: Set the Publication Compatibility Level (Replication Transact-SQL Programming).
• On the Subscriber Types page of the New Publication Wizard. For more information about running this wizard, see How to: Create a Publication and Define Articles (SQL Server Management Studio).
• On the General page of the Publication Properties - <Publication> dialog box. For more information, see How to: Set the Compatibility Level for Merge Publications (SQL Server Management Studio).

The following features require a compatibility level of 90RTM or higher:

• Logical records. For more information, see Grouping Changes to Related Rows with Logical Records.
• Subscriber upload options. For more information, see Optimizing Merge Replication Performance with Download-Only Articles.
• Nonoverlapping partitions. For more information, see Parameterized Row Filters.
• Business logic handlers. For more information, see Executing Business Logic During Merge Synchronization.
• Schema changes using ALTER <OBJECT> statements. For more information, see Making Schema Changes on Publication Databases.

The following features do not depend on the compatibility level, but they do require the Merge Agent that ships with SQL Server 2005; Subscribers running previous versions of SQL Server function as though the feature is not enabled:

• Precomputed partitions. For more information, see Optimizing Parameterized Filter Performance with Precomputed Partitions.
• Web synchronization. For more information, see Web Synchronization for Merge Replication.
• Parallel phase agents (specifying -ParallelUploadDownload for the Merge Agent). For more information, see Replication Merge Agent.
• New snapshot features for publications that use parameterized filters, which include the ability:
  • For a Subscriber to request a snapshot if one is not available for its partition.
  • For an administrator to pre-generate and schedule generation of snapshots.
  • To deliver parameterized snapshots using FTP.
    For more information, see Snapshots for Merge Publications with Parameterized Filters and Transferring Snapshots Through FTP.
• Improved history logging and article-level statistics in Replication Monitor. For more information, see How to: View Information and Perform Tasks for a Subscription (Replication Monitor).

Publication Compatibility Level Behavior in SQL Server 2005

It is important to understand the behavior of the publication compatibility level:

• The publication compatibility level is not connected to the database compatibility level.
• If you create a publication with sp_addmergepublication, or through Replication Management Objects (RMO), the publication compatibility level is set to 80RTM by default. If you create a publication in the New Publication Wizard, the publication compatibility level is determined based on the options chosen on the Subscriber Types page of the wizard.
• In previous versions of SQL Server, the publication compatibility level automatically increased if you enabled a feature that required a higher level. In SQL Server 2005, you must manually set the publication compatibility level to 90RTM before enabling functionality that requires that compatibility level.
  If you upgrade a Publisher from SQL Server 7.0, and then select one or more features that require a compatibility level of 80RTM, the compatibility level is automatically increased.
• The publication compatibility level can be decreased only if the Snapshot Agent has not been started and there are no subscriptions to the publication.
• All publications in the same database must have the same compatibility level. This requirement has the following consequences:
  • If a database contains a publication with a lower compatibility level (such as 80RTM), and you want to add another publication in the same database with a level of 90RTM, you must manually increase the level of the first publication before the new publication is added.
  • If a database contains two or more publications with lower compatibility levels, and you want to add another publication in the same database with a level of 90RTM, you must drop all but one of the existing publications; increase the level of the remaining publication to 90RTM; re-create the dropped publications with a level of 90RTM; and create the new publication with a level of 90RTM.

See Also

Concepts

Replication Backward Compatibility
Upgrading Replicated Databases

Other Resources

Replication Enhancements

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
15 September 2007	New content: Added a note about the SQL Server on-disk storage format being the same in 64-bit and 32-bit environments.
14 April 2006	Changed content: Added tables to clarify which versions of SQL Server can be used in the same replication topology. Added information about schema options used to map data types. Removed information stating that SQL Server 7.0 can use an instance of SQL Server 2005 as a remote Distributor. This configuration is not supported.