migrating to a bigger server – how to copy over differences

All we need is an easy explanation of the problem, so here it is.

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 "link" 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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

Leave a Reply