0

As per the msdn, Subscriber version depends on the type of publication: A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.

I am having a requirement where I need to transactionally replicate a SQL Server 2005 to 2017, and as per the above msdn note we cannot do it, right?

But when I created a test environment and configured replication from 2005 to 2017 it is working fine. Any explanation to that? I am not able to understand why is it happening.

Improve this question
3
  • See my edited answer, as I had forgotten about SMO, which is a more dependable way of replicating across versions. Likely what you are looking for. :)
    – clifton_h
    Mar 7, 2019 at 22:57
  • There is being able to physically do something and there is supportability in doing it. While you may be able to setup and run this, there is no guarantee this will work indefinitely or across all items. Additionally 2005 has been out of support for some time now, so even if you had an issue it's not supported just based on the version. So, feel free to try it since 2005 isn't supported so nothing to lose except time.
    – Sean Gallardy - Mostly Retired
    Mar 7, 2019 at 23:47
  • @SeanGallardy one more edit since I don’t mean to be read as supporting staying on an unsupported environment
    – clifton_h
    Mar 8, 2019 at 1:19

1 Answer 1

Reset to default
0

EDIT: I Should clarify this is not a long-term solution. You need to upgrade to at least SQL 2014 or higher which support running your DB in 2008 mode.

You should attempt to test a later edition. Perhaps 2008 compatibility-mode will work just fine. Until then, the following would apply:

Traditional answer: That is because it is not supportable, I e. They will not help you if you run into stability issues and errors with the version of BCP used in replication, when you attempt to replicate objects that are either depreciated or no did not yet exist (DATE being on example).

You might lose data and they do not want to support something that was made in the early 2000s. That sounds scary! But obviously one cannot force everyone into the latest version in Production.

A traditional approach would be to use existing replication from one distribution DB until you get to the supported version.

Replicate from 2005 to 2012 and from that 2012 version to 2016 and from 2016 to your present version.

  • The key here is what version your Distribution database is, not so much the instance level. So it is not entirely necessary to have lots of instances here.

But this is a pain, and clearly not helpful in your situation. You need a solution that can help you buy time for your dev team to test a supported environment.

Instead, use SQL Server Management Objects (SMO)

  • it can do a lot. Move a DB, Objects, include dependencies, transfer user, permissions, SIDs, plus more. Super easy to setup and very secure.
  • SSDT allows a GUI version where you can pick and choose your objects (have to be predefined, though) and could migrate from your 2005 instance to your 2017. Technically it is. Or using replication and should be covered (test of course).
  • Related Links: INSTALLING SMO - Microsoft
  • TutorialGateWay has a decent guide to start. Transfer SQL Objects Task in SSIS

Again, unsupported means you will be the only one receiving blame and help will not be offered. Don’t stay in 2005, but perhaps this will make the transition smooth.

Microsoft on Supportable Replication

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.