SQL Server不同版本之间发布订阅的主意事项

I was asked recently about Replication with different versions of SQL Server i.e. Multiple versions of SQL Server in a Replication topology. Generally, Microsoft supports replication back to two previous versions. We just need to know the rules. Let's take a look.

Including the two previous releases, we can therefore replicate between SQL Server 2008, 2005 and 2000. Of course, certain service packs need to be applied: SQL 2000 has to have SP3 and SQL 2005 needs SP2.

If you do mix versions, you should know that SQL Server takes the lowest common denominator approach. That means that if you have at least one SQL 2000 Subscriber, you are limited to SQL 2000 functionality for replication. When you set up the Publication, the wizard asks you about the levels of SQL Server supported by the subscribers. The more levels you check off, the more you are limiting replication functionality. Obviously, running Publishers, Distributors and Subscribers at the latest release is going to allow the maximum features, but that is not always possible.

One general rule is that the Distributor cannot be an earlier version than the Publisher. Many times the Publisher and Distributor roles are on the same server so this is not an issue. However, if you have a large number of subscribers you may choose to have a separate Distributor server. The Distributor can be a later version or the same, just not earlier.

For Transactional replication, you can mix and match the supported versions as you like between Publishers and Subscribers. The Publisher can be SQL 2000 with SQL 2005 and SQL 2008 Subscribers or a SQL 2008 Publisher with SQL 2008, 2005 and 2000 Subscribers. Just remember the lowest common denominator rule.

Merge replication is a little less flexible: the subscriber needs to be at a version no later than the Publisher. Earlier or the same is fine. For example, a SQL 2008 Publisher with SQL 2008, 2005 and 2000 Subscribers is supported. If you are replicating "down-level" in this way, you will need to be careful with new data types as they will be mapped to compatible data types for the older version. For instance, a data type of Geography in a 2008 Publication will be mapped to a data type of Varbinary(max) in a 2005 Subscription and to a data type of Image in a 2000 Subscription.

New SQL Server 2008 features like Data Compression and Filestream data are supported with replication, as long as the Publisher, Distributor and Subscribers are at the 2008 level. Otherwise replication will not work. This makes sense.

As always the proof is in the testing.

 

原文来自：http://www.sqlmanager.net/en/articles/1548