5

(I'm trying to predict long-term SQL backup storage needs and if/where to enable compression, on a storage appliance with de-duplication. I know the best answer is "test both and see", which we are doing.)

I seem to recall that SQL backups (and restores) are performed upon SQL database extents (8x 8kb data pages). My question is, are those extents handled predictably and consistently by the SQL backup process in a way that the storage de-duplication can rely on?

By that I mean, if we do a full backup each night, will the unchanged extents result in identical bits on disk (for those unchanged portions) from night to night? Or is that not reliable/predictable?

What if we enable SQL backup compression? Will identical extents result in identical compressed bits from night to night? Or is that not guaranteed?

Improve this question

1 Answer 1

Reset to default
6

if we do a full backup each night, will the unchanged extents result in identical bits on disk (for those unchanged portions) from night to night?

Almost certainly, as it's just a block copy of the data.

But have you considered doing Full backups less often, if so little data is changed? Perhaps a weekly or twice-weekly Full, twice-daily Diff, and Log backups every few minutes?

Will identical extents result in identical compressed bits from night to night?

Much harder to say, and I would guess probably depends on the MAXTRANSFERSIZE parameter. In other words, an unchanged block of the size equivalent to MAXTRANSFERSIZE would yield an unchanged result. The compression algorithm itself is deterministic.

Improve this answer
2
  • Thanks for this answer. We are def considering differentials, but that doesn't entirely moot the question, since there is still likely some percentage of unchanged extents between weekly fulls. Commented Jul 17 at 13:42
  • Hmm, I guess we'd have to hear from someone who knows the inner workings, but I would have thought it would compress the data first, then "bundle" it (in MAXTRANSFERSIZE-d blocks) as it is written to disk? But that's entirely a guess. The only doc even slightly related I can find has to do with MAXTRANSFERSIZE in the context of TDE: a setting larger than 64kb and SQL will decrypt-compress-encrypt TDE-encrypted data on the fly during backup. Commented Jul 17 at 14:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.