migrating to a bigger server - how to copy over differences We have a very large database, one table (logs) contains the majority of the data (around 13 billion rows) total database size is approx 6.5TB and I have 900gb of space remaining before server is full.
We have a delete script for old data, but it doesn't effect size, as indexes are fragmented, it would be nice to reclaim this but i know it will introduce a huge downtime if i rebuild the one big index.
The system is always on, we do have quiet periods like sunday night, but any downtime needs to be max 2 hours.
I was planning on copying over the 1TB .bak file to new server, restore, rebuild indexes, then I somehow need to look at the differences between what changes, and update the new server with these differences (new or updates records)
My question is:
Is there a free tool built into sql server that can detect differences, and copy over new/changed data if I &quot;link&quot; the servers? (the one big table does not allow updates, only inserts/deletes)
We are using sql server 2017 ent
As a side note and for full picture, we do not use compression (row or page, this is a different route we are looking at too - if we can do without down time)
Change Data Capture is meant to capture changes that happen in tables and offers you a way to then read what those changes are to synchronize data to a target server.