Replication between SQL 2000 and SQL 2014

  • Hi,

    I have replication between SQL 2000 and SQL 2008 which is working fine.

    My SQL 2008 server needs upgrading and so I have SQL 2014.

    The issue is that replication cannot be setup between SQL 2000 and SQL 2014.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/replication-backward-compatibility?view=sql-server-ver15

    Please can someone suggest any other way of overcoming this issue.

    Thanks,

    Harsha

  • Your company appears be addicted to out-of-support SQL versions. SQL Server 2014 reached its end of life on July 9, 2019 and its extended support will end on July 9,2024.

    The obvious answer is to get on supported versions of SQL Server. But I assume that's not an option.

    This post indicates "you can use replication, if you configure your SQL 2014 server as an obdc (sic) subscriber. This way you will be able to move all changes to tables with PKs originating on your SQL 2000 server continuously to your SQL 2014 server." I can't verify that.

    Could you replicate from 2000 to 2008, and then from 2008 to 2014?

  • Excuse the pun, but the question "Replication between SQL 2000 and SQL 2014" sounds like a Jurassic Park franchise title

    😎

  • Thank you for the suggestion. Appreciate your response.

    I am trying to make the SQL 2014 ODBC(SIC) subscriber, but not able to so.

    The link is not the thing what I am looking for.

  • Strong, strong suggestion, if you're upgrading from 2000, 2008, 2012, skip 2014. Go to 2016 or greater. The reason is simple. 2014 introduced a new cardinality estimation (CE) engine, but didn't provide a good way to deal with issues that come up. 2016 adds Query Store which gives you the ability to deal with CE issues. Skip 2014, which is effectively out of support anyway. Move on up to 2016. You'll be much happier. Overall functionality is wildly better.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I had to check, and yep, mainstream support ended in 2019. Extended support ends in less than two years. Don't "upgrade" to 2014. Move on to one of the three more recent versions. Yes, three.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I'm curious. I've had to keep old servers alive for various "historical" reasons, but they never were active unless someone needed a report out of them.  They sat in a corner collecting dust for the most part.

    It appears you have an active system running on SQL 2000.  Why?

    And, why SQL 2014 as opposed to something newer?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • You could certainly export data with an ETL process of some sort (bcp/SSIS/etc) and move data from SQL 2000 to 2014. The reverse would also work.

    However, as others have noted, SQL 2000 is very old. I suspect that you have some sort of software package that runs on 2000, but likely would also run on 2008 in 2000 compatability mode. Not perfect, but I'd give that a try as well.

     

  • Thank you Steve.

    I have live SQL 2000 servers as the application is too old one and cannot be upgraded with newer version of the application. I am not sure if we can make it work if we upgrade to higher version, the app will work.

    I had thought of and will do SSIS upload.

    I have 15 servers to get replicated with 113 tables ! .

    So it is a big work 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply