En trevlig funktion som kom redan i SQL Server 2005 är BACKUP WITH COPY_ONLY. Detta…
In this blog post I will talk about deduplication,or dedup, on your database backup share and how it may affect the time it takes to do a database restore.
What is Dedup?
Data deduplication, also called dedup, is a type of “data reduction”, kind of like compression. It reduces the amount of disk needed to store a certain amount of data. It does this by dividing all of the data in to chunks, comparing those chunks and then only stores identical chunks once. When you need to access the files, they get rebuilt before leaving the storage.
Why people use it.
This is can significantly increase the amount of data you can store.
A good use case of using dedup would be storing VDI files, because a lot of the files are mostly the same and you would therefore get a really good dedup ratio. You might get a good dedup ratio on SQL Server backup files too, if you have mostly data that doesn’t change all that much.
Why it isn’t always the best choice.
But the downside is that your restores take a lot longer and depending on your RTO, it can be a deal breaker. One of our customers use dedup on their backup share, and with their permission, I did some restore tests so I could get an idea of how much difference dedup makes.
This test was made with a 10GB database. I also tried it with and without SQL Servers built in backup compression.
Here are the numbers I got from the test. As you can see, restoring from a share with dedup can take around 10 times longer than from a regular share. Which is huge. I also noticed that restoring a SQL compressed backup file took noticeably shorter, which is interesting. These number can of course vary depending on what kind of data you store and how much of it. Make sure to do your own restore tests, are the times acceptable?
Should you use it?
Deduplication can drastically reduce the size of your data, but we recommend you store your database backups in a share without dedup. You might think that the amount of storage you save is worth it, and it might be. Just make sure that you know how much time a restore will take on all of your most important databases.