October 21, 2013 at 2:12 pm
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.
October 22, 2013 at 7:24 am
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
October 22, 2013 at 7:37 am
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.
October 22, 2013 at 9:27 am
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
October 31, 2013 at 9:09 am
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?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply