Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional Replication from SQL Server 2008 to SQL Server 2000 Expand / Collapse
Author
Message
Posted Monday, October 21, 2013 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, 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.
Post #1506866
Posted Tuesday, October 22, 2013 7:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
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
Post #1507123
Posted Tuesday, October 22, 2013 7:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, 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.
Post #1507137
Posted Tuesday, October 22, 2013 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:37 AM
Points: 299, Visits: 561
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
Post #1507219
Posted Thursday, October 31, 2013 9:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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?
Post #1510251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse