Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

In that past year we started using a D2D network device to store our SQL Server backups. This device does dedup and compression. It looks just like any standard CIFS share (\\d2d\sql_backups\...). Every thing works normally except we get horrible restore throughput. We only get 2-4MB/sec. We have worked extensively with the vendor. We are doing simple SQL Server restores like the following.

RESTORE DATABASE [test]
FROM  DISK = N'\\d2ddc2\sql_backup\test_backup_2013_02_10_210004_0520927.bak' 
 WITH  FILE = 1,  
 NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 1
GO

When doing diagnostics the vendor tells us that the SQL Server restore is NOT reading the backup file sequentially but rather it skips forward at times and then back to read the data. This causes numerous buffer problems as the D2D is attempting rebuild the deduped data.

Can anyone confirm or deny this. I have searched extensively on MS, google, various other sites and can't find a definite answer. If it's not true can you point me some authority I can give to our vendor.

Just to clarify. I'm only concerned about how the actual .bak is read. This has nothing to do with the database files or IO.

Thanks Mike

share|improve this question
1  
I'm not familiar with any dedupe technology, but have you seen this article from Brent Ozar's blog: Why Dedupe is a Bad Idea for SQL Server Backups? Maybe it sheds some light. – Marian Feb 15 at 16:34
2  
Restores should be read in sequential order as they are designed to be read from tape which wants to be sequential. SQL Server doesn't do any processing of the data from the backup as it's restoring it. It just reads the file and puts the pages where they belong on the disk. If the backup was compressed that might change. – mrdenny♦ Feb 15 at 17:06
1  
Quoting from an article of Kevin Kline: "Also, data deduplication technologies do not improve the speed of backup or recovery, and actually can slow those processes dramatically. In fact, backups to a deduplicating storage device may be lengthened due to long network queues, and database recovery may take longer because the hashing algorithms might fragment the backup files." – Marian Feb 15 at 19:31
Thanks for the Replies. I should have said in the original post that I never thought Dedup was a good idea for SQL backups but I really wasn't given a choice ;-). I'm most interested in the actual reads of the BAK file (thank mrdenny for the response). I just wish I had a way to prove it since the vendor keeps says sql is flushing the hardware read buffer by doing non sequential reads. Makes no sense to me. Tthanks again. – MikeM Feb 15 at 21:36
2  
@MikeM Dedupe is a great idea for backup. Win Win Win. Timely restore, not so much. – Mark Storey-Smith Feb 15 at 22:10
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.