Using Multiple Versions of SQL Server in a Replication Topology

Microsoft Developer Network

Using Multiple Versions of SQL Server in a Repl...

SQL Server 2008 Books Online (June 2009)

Replication supports replicating data to different versions of SQL Server. This topic provides information about the following areas:

SQL Server versions supported
Mapping SQL Server 2008 data types for earlier versions
Restoring a replicated database from an earlier version
Compatibility level for merge publications

For information about how to replicate data to SQL Server Express and SQL Server Compact 3.5 SP1, see Replicating Data to SQL Server Express and Replicating Data to SQL Server Compact. For information about the features that are supported by each edition of SQL Server, see Features Supported by the Editions of SQL Server 2008.

Supported Versions of SQL Server

SQL Server 2000 and SQL Server 2005 can both participate in replication topologies with SQL Server 2008. For SQL Server 2000 the minimum version is Service Pack 3 (SP3). For SQL Server 2005 the minimum version is Service Pack 2 (SP2).

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

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. (Frequently, the Distributor is the same instance as the Publisher.)

For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

For merge replication, a Subscriber to a merge publication can be any version no later than the Publisher version. For more information about compatibility for earlier versions, see "Compatibility Level for Merge Publications" later in this topic. For more information about replication features that are supported in the various editions of SQL Server, see Features Supported by the Editions of SQL Server 2008.

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

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

If you have a Publisher and Distributor that are running SQL Server 2000, you can change the credentials under which agents make connections by using sp_changedistpublisher and sp_changesubscriber. However, if you upgrade the Distributor to SQL Server 2008, these procedures cannot be used to change the credentials that are used in existing agent jobs. The procedures do affect agent jobs that are created after the procedure is called. To change the credentials for existing agent jobs, call one of the four procedures listed previously.

Mapping New Data Types for Earlier Versions

SQL Server 2008 and SQL Server 2005 support several new data types. As shown in the following table, these new data types are mapped to compatible data types at the Subscriber if push subscriptions from a SQL Server 2005 or SQL Server 2008 Distributor are used. If new data types are replicated to Subscribers that are running earlier versions of SQL Server, you must verify that the data types are mapped appropriately:

Mapping is performed by default for articles in merge publications, but not for articles in snapshot or transactional publications. For merge publications, the way in which types are mapped is determined by the publication compatibility level. For example, if a column is of type geography and the compatibility level is 90RTM, the type is mapped to varbinary(max). If the compatibility level is 80RTM, the type is mapped to image.
Mapping behavior is controlled by the @schema_option parameter of sp_addarticle and sp_addmergearticle.
For more information about how to set schema options, see How to: Specify Schema Options (SQL Server Management Studio) and How to: Specify Schema Options (Replication Transact-SQL Programming).

SQL Server 2008 data type	SQL Server 2005 data type	SQL Server 2000 data type
Common language runtime user-defined type (UDT): 8000 bytes or less	UDT	image
UDT: more than 8000 bytes1	varbinary(max)	image
date 2, 3	nvarchar(10)	nvarchar(10)
datetime2 2, 3	nvarchar(27)	nvarchar(27)
datetimeoffset 2, 3	nvarchar(34)	nvarchar(34)
FILESTREAM attribute1, 4	varbinary(max)	Not supported
geography and geometry1, 3	varbinary(max)	image
hierarchyid 1, 5	varbinary(max)	image
nvarchar(max)	nvarchar(max)	ntext
time 2, 3	nvarchar(16)	nvarchar(16)
varchar(max)	varchar(max)	text
varbinary(max)	varbinary(max)	image
xml	xml	ntext

1 Mappings for UDT, FILESTREAM, geography, geometry, and hierarchyid types are not supported for transactional publications with updatable subscriptions. Only include these types if all updating Subscribers are running SQL Server 2008 or a later version.

2 Replication does not check the format of data inserted at the Subscriber. Therefore, your application must ensure that inserted data is of the correct format for columns of type date, datetime2, datetimeoffset, and time. This is typically done with a constraint. If the data is not of the correct format, inserts at the Publisher will fail.

3 SQL Server Compact 3.5 Subscribers convert these types after they are replicated to the Subscriber. For information about data type mappings for SQL Server Compact 3.5, see the SQL Server Compact 3.5 documentation.

If you map columns of type geography or geometry to varbinary(max) or image, you cannot replicate default constraints for these columns. This has the following consequences:

If you already have a default constraint at the Publisher, drop the constraint or specify that it should not be replicated. To specify that it should not be replicated, use the article schema option for default constraints:
- Select a value of False for the option Copy default value specifications. For more information, see How to: Specify Schema Options (SQL Server Management Studio).
- Disable the schema option 0x800. For more information, see How to: Specify Schema Options (Replication Transact-SQL Programming).
If you want to add a default constraint at the Publisher, first specify that schema changes should not be replicated. For more information, see How to: Replicate Schema Changes (SQL Server Management Studio) and How to: Replicate Schema Changes (Replication Transact-SQL Programming).

4 FILESTREAM is an attribute on a varbinary(max) column. For information about how to use FILESTREAM columns in replicated tables, see the "Replication" section of Using FILESTREAM with Other SQL Server Features. Columns that have the FILESTREAM attribute should not be included in publications that use a character mode snapshot.

5 Support for columns of type hierarchyid depends on the type of replication and the versions of SQL Server that are used. For more information, see the "Using hierarchyid Columns in Replicated Tables" section of hierarchyid (Transact-SQL). For merge replication, hierarchyid is mapped to image when the publication compatibility level is 100RTM and a character mode snapshot is used.

Replicating XML data types

When replicating XML data types to SQL Server Compact 3.5 SP1, merge replication maps them to Ntext. The XML data on SQL Server 2008 has prefix bytes for UTF-16 encoding. These bytes are preserved when replicating from SQL Server to SQL Server Compact 3.5 SP1 by using merge replication. These prefix bytes are not understood by SQL Server Management Studio when viewing the Ntext column of the SQL Server Compact 3.5 SP1 database. Therefore, these bytes are displayed as garbage characters.

The XML Schema collection in SQL Server 2008 has been updated.This has an effect when replicating the XML columns bound to XML schemas from SQL Server 2008 to SQL Server 2005.

Timezones are not mandatory for date, time and datetime XML Schema values in SQL Server 2008. This means that if no timezone is specified on the SQL Server 2008 publisher XML column, it will not apply the change on SQL Server 2005 subscribers, because SQL Server 2005 requires that a timezone is specified.

Timezone information about the date, time, and datetime XML Schema typed values of SQL Server 2008 Publisher will be converted to the UTC-0 timezone in SQL Server 2005. This is represented by the timezone indicator Z.

SQL Server 2008 XML Schema date, time, and datetime types support larger precision. Therefore, these values get rounded off when replicating to SQL Server 2005.

When replicating XML Schema date or datetime values from SQL Server 2005 to SQL Server 2008, values with a negative year will not apply on SQL Server 2008 because it is not supported on SQL Server 2008.

In these situations, the sp_table_validation and Validate methods in Replication agents might fail. For more information, see the "Upgrading Typed XML from SQL Server 2005 to SQL Server 2008" section in Typed XML Compared to Untyped XML.

Publishing Compressed Data

SQL Server 2008 supports both row and page compression for both tables and indexes. For information about replication support for compressed data, see "How Compression Affects Replication" in Creating Compressed Tables and Indexes.

Restoring a Replicated Database from an Earlier Version

You can keep replication settings when you restore a backup of a replicated database from an earlier version. If you restore the backups to a server and database that have 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 you restore the database, execute sp_vupgrade_replication to upgrade schema and system data to support replication at the current product level.

Although preserving replication after restoring from backup from earlier 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 80RTM (SQL Server 2000 that has no service packs installed) to 100RTM for SQL Server 2008. The compatibility level is specified by one of the following methods:

By using the @publication_compatibility_level parameter of sp_addmergepublication. For more information, see How to: Set the Compatibility Level for Merge Publications (Replication Transact-SQL Programming).
On the Subscriber Types page of the New Publication Wizard. For more information about how to run 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 that use ALTER <OBJECT> statements. For more information, see Making Schema Changes on Publication Databases.

The following features do not depend on the compatibility level; however, they do require the Merge Agent that is included with SQL Server 2005 and later versions. Subscribers that are running earlier versions of SQL Server function as if 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.
Snapshot features for publications that use parameterized filters. These features provide the following abilities:
- 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 2008

The following are some important behaviors of the publication compatibility level to consider:

The publication compatibility level is not connected to the database compatibility level.
If you create a publication by using 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 that you select on the Subscriber Types page of the wizard.
In versions of SQL Server earlier than SQL Server 2005, the publication compatibility level automatically increased if you enabled a feature that required a higher level. Starting with SQL Server 2005, you must manually set the publication compatibility level to 90RTM or higher before you enable functionality that requires that compatibility level.
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 has a lower compatibility level (such as 80RTM) and you want to add another publication in the same database that has a level of 90RTM or higher, you must manually increase the level of the first publication before you add the new publication.
- If a database contains two or more publications that have lower compatibility levels and you want to add another publication in the same database that has a level of 90RTM or higher, you must drop all existing publications except one; increase the level of the remaining publication to 90RTM or higher; re-create the dropped publications with a level of 90RTM or higher; and then create the new publication with a level of 90RTM or higher.

Required Components and Compatibility Levels for Web Synchronization

SQL Server 2008 supports Web Synchronization for Subscribers that are running SQL Server 2005, SQL Server 2008, and SQL Server Compact 3.5 versions 3.0, 3.1, and 3.5. The following table lists the publication compatibility level and the server components that are required for each type of Subscriber.

Publisher version	Subscriber version	Required publication compatibility level	Required components on the IIS server
SQL Server 2008	SQL Server 2008	100RTM	SQL Server 2008 IIS components
SQL Server 2008	SQL Server Compact 3.5 3.0, 3.1, and 3.5	90RTM	SQL Server Compact 3.5 SP1 IIS components and SQL Server 2008 IIS components
SQL Server 2008	SQL Server 2005	90RTM	SQL Server 2008 IIS components
SQL Server 2005	SQL Server 2005	90RTM	SQL Server 2005 IIS components
SQL Server 2005	SQL Server Compact 3.5 3.0, 3.1, and 3.5	90RTM	SQL Server Compact 3.5 SP1 IIS components and SQL Server 2005 IIS components
SQL Server 2005	SQL Server 2008	Not applicable1	Not applicable1

1 This configuration is not supported because the Publisher version must be equal to or greater than the Subscriber version.

Concepts

Replication Backward Compatibility
Considerations for Upgrading Replicated Databases

Help and Information

Getting SQL Server 2008 Assistance