• Article

Using Multiple Versions of SQL Server in a Replication Topology

Notes

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

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 SP2, see Upgrading Replicated Databases 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 R2.

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.

Notes

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

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:

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

date2, 3

nvarchar(10)

nvarchar(10)

datetime22, 3

nvarchar(27)

nvarchar(27)

datetimeoffset2, 3

nvarchar(34)

nvarchar(34)

FILESTREAMattribute1, 4

varbinary(max)

Not supported

geography and geometry1, 3

varbinary(max)

image

hierarchyid1, 5

varbinary(max)

image

nvarchar(max)

nvarchar(max)

ntext

time2, 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:

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 FILESTREAM and 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 SP2, 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 SP2 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 SP2 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:

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

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.

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