What is SQL Server Transactional Replication from 2005 to 2017?
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.
What is sql server transactional replication?
SQL Server Transactional Replication:
Transactional replication is a feature of Azure SQL Managed Instance and SQL Server that enables you to replicate data from a table in Azure SQL Managed Instance or a SQL Server instance to tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.
Transactional Replication is not a long-term solution. You need to upgrade to at least SQL 2014 or higher which supports running your DB in 2008 mode. You should attempt to test a later edition. Perhaps the 2008 compatibility mode will work just fine. Until then, the following would apply:
Traditional answer: That is because it is not supportable, Ie. 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 an 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.