0

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)

Improve this question
6
  • 4
    A common method I have seen to achieve this is log shipping - Move SQL databases to a different server using SQL Server log shipping
    – Scott Hodgin - Retired
    Commented Oct 9, 2020 at 10:39
  • 1
    I'd mark this comment by @ScottHodgin as the answer. Log Shipping is the standard approach to this exact problem.
    – Grant Fritchey
    Commented Oct 9, 2020 at 12:45
  • But with Log Shipping you can't "restore (with recovery), rebuild indexes". You end up with a physical copy of the source database. But it's not obviously necessary to rebuild indexes or reorganize data as part of the migration, and I would normally recommend not doing that, and defering any reorganization to later when you implement row, page, or ColumnStore compression. The normal solution for keeping two databases logically in sync after a one-time copy is Transactional Replication.
    – David Browne - Microsoft
    Commented Oct 9, 2020 at 17:06
  • Have you looked at partitioning and partitioned indexes? learn.microsoft.com/en-us/sql/relational-databases/partitions/…
    – Metaphor
    Commented Oct 10, 2020 at 14:09
  • thanks, the 2nd server is not local, and also the recovery model is simple, so this rules out log shipping. we did need to rebuild indexes, and that will take a number of hours with online=off, so i need to be able to get the latest data after this maintenance
    – Quade
    Commented Oct 12, 2020 at 5:48

1 Answer 1

Reset to default
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.

Improve this answer
2
  • Thanks, this looks more like schema changes? unless i've read that wrong?
    – Quade
    Commented Oct 14, 2020 at 5:38
  • "Change data capture records insert, update, and delete activity that applies to a SQL Server table."
    – Lee M
    Commented Dec 20, 2020 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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