SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional Replication from SQL Server 2008 to SQL Server 2000


Transactional Replication from SQL Server 2008 to SQL Server 2000

Author
Message
ELLEN-610393
ELLEN-610393
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 102
Is it possible to have a SQL Server 2008 R2 database that is a Publisher/Distributor of some tables to a SQL Server 2000 database?

We would want to use a push subscription and have transactional replication.

There are about 15 tables in a larger database on a SQL Server 2008 R2 server that we would like to have similar tables on a SQL Server 2000 server database and have them synchronized continuously. Another piece of information is that the servers have different collations.

Both databases have many other non-similar tables that are used in different applications. So both databases are Read/Write [neither are reporting databases] but the tables that would be replicated are only updated on the SQL Server 2008 R2 server.

Thank you for any clarification as to if transactional replication can be used between these two versions of SQL.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2665 Visits: 1262
Accoriding to other information I have found it appears not:

Mixed versions are supported, but:

•Distributor version must always be equal or newer than Publisher
•Subscribers for transactional replication can be within 2 versions newer/older of Publisher
•Subscribers for merge replication must be equal or older than Publisher

MCITP SQL 2005, MCSA SQL 2012
ELLEN-610393
ELLEN-610393
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 102
So, if the SQL Server 2000 server was upgraded to SQL Server 2005 or 2008 R2, the transactional replication could work for the disparate collations?

Also, to confirm, it is the fact that the 2008 SQL Server is version R2 that makes it one too many versions different from SQL Server 2000 for the replication to work?

Thank you for the reply.
RTaylor2208
RTaylor2208
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2665 Visits: 1262
Yes I believe so, the collation issue I am not sure about though. You would have to test for that.

If your using the unicode data types it should replicate ok, but queries performed on the data may return different results based on the collation. Also joins may be an issue.

MCITP SQL 2005, MCSA SQL 2012
MysteryJimbo
MysteryJimbo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5209 Visits: 15346
Different collations shouldn't be an issue.

What could be an issue is newer datatypes in use in the tables being replication. These cannot be replicated. Otherwise, I don't see a problem but I wouldn't necessarily recommend it since SQL 2000 is no longer supported.

Is there anything preventing you from taking the opportunity to upgrade to a supported version of SQL?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search