1

I work on a payment system (a bit regulated area with requirements to store really old data for audits & customer requests).

We have SQL Server 2016 (but will move to 2019 soon).

System is 16 years old, and for its functioning old data is not needed (but needed for ocasional analytics queries done by close-to-business analysts on readonly replica).

When i first thought about some kind of cold storage of old data i though about backups. But backups have 2 drawbacks:

  1. No backup will contain all data (so if i need data that is 3 yr old & 10 yr old i probably would need to restore two backups)
  2. They are hard to use, since they need to be restored and it takes some time.

Our business guys are okay to remove old data from main DB Server but really want to have old data be easily accessible with single SQL query (e.g. slow machine with all data for all years).

Is this even possible? Some kind of a replication that ignores huge data deletions (not alll DELETE operations but specific DELETEs that clean up all data), or any other approach that would be easily mainteinable in long run

Thanks!

P.S. Already use SSD & legally can't use Strech Database / public cloud :(

Improve this question
2
  • About how many Tables do you need to keep history on?
    – J.D.
    Commented May 7, 2022 at 12:25
  • @J.D. My guestimate is that 50 of 800 tables in DB would give 95% of benefit, and all of them have a datetime column that can be used as a determinant. I think that its also acceptable to have single common threshold for all the tables
    – Grigory
    Commented May 9, 2022 at 9:24

3 Answers 3

Reset to default
1

"Some kind of a replication that ignores huge data deletions".

You could use transactional replication and modify the replication properties of specific articles (tables) to ignores deletes, that way you can replicate and keep everything to a reporting or archive database.

There are lots of other things to consider with replication of course, but by design it can do that.

Improve this answer
0

Have you considered polybase? This allows you to create a connector to external data sources which can even be comma separated.

Improve this answer
3
  • I don't think this necessarily helps OP with the actual archival process (of moving the data out of his Tables) though.
    – J.D.
    Commented May 7, 2022 at 12:19
  • 1
    Polybase has been retired
    – Erik Reasonable Rates Darling
    Commented May 7, 2022 at 15:26
  • @ErikDarling Only scale-out groups for Polybase is being retired, but the original data virtualization functionality (that this answer is referring to) is still staying. But I guess that would be a glorified Linked Server for OP's question, so not sure I see the benefit in this answer.
    – J.D.
    Commented May 8, 2022 at 3:11
0

Consider that in 10 years you may not be able to restore the (then) 10 years old backup on the (then) current version of SQL Server (or whatever will take its place), nor find a (then) 10 years old version of SQL Server to install on the (then) current hardware. Digital decay is a thing.

I think the most practical way to store long term archival data is to use some "greatest common factor" format, such as CSV or JSON, and use tools like AWS S3 Select to query them. This way you won't need to maintain snapshots of multiple versions of your database infrastructure to support all the possible versions of your native backup images.

Improve this answer

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.