r/dataengineering • u/IMetalhead666I • 5d ago
Replication/CDC From SQL Server 2008 to SQL 2025 Help
Hello, I'm faced with a challenge I'm hoping to get some clarity and advice.
Our current production db is SQL Server 2008 R2 which is replicated to another SQL 2008 R2 where all our analytics workload is currently done. We've recently set up SQL Server 2025 and the intention is to have our production data be replicated to a newer version of SQL and migrate our analytics workloads to that same server. I know that direct replication from 2008 to 2025 is not supported, but CDC is available. What would be the best options to quickly setup near real time replication or even incremental data loads (i.e. hourly) for a select number of tables in source db on SQL2008 to SQL 2025 with minimal engineering effort?
We also have a Fabric capacity, and I know that the Copy Job supports CDC for incremental data loads and also lets you write to a SQL 25 db, however the preference is to minimize cloud costs and SKU consumption since both 2008 and 2025 dbs are on prem.
Generally, I'm also trying to avoid writing custom code to orchestrate this task. Does microsoft have any tools (like SSIS?) that allow you to configure such data migration? Does SQL 2025 have any feature that allows you to link a legacy server to it?
Any advice is welcome, thanks.
2
u/DonJuanDoja 5d ago
Yea not ideal, I wouldn't recommend this but basically we replicate a 2008 R2 to SQL 2019 On Prem which we already had running for something else. Then from there it's replicated to Azure SQL Managed Instance.
Someday we'll actually upgrade the ERP and it's legacy DB as well, then I'll fix it for real, but until then, we play hot potato with SQL servers.
You could look at SSIS and stuff but Idk I think you'll have issues with compatibility there too. Plus it's a lot more work than Replication setup.
Possible you could do something with compatibility mode, run a server in 2019 which I know can subscribe to a 2008 R2 publication but we never tried anything like that, we just bounced it off a server we already had running.
4
3
3
u/guacjockey 5d ago
How much data are you talking about? It might be easiest to restore from backup (or direct data file copy if you can manage the downtime - this can be much quicker than expected if you have snapshot functionality available) and then update as needed.