How to break a high end de-duplication backup device

Tim Radney, 2013-02-08

I have been working with a client recently who purchased a nice top of the line de-duplication backup device in order to synchronize backups between two data centers. This device is very nice in how it will de-duplicate data to reduce storage needs and also compress the data. It will then synchronize the data to a secondary device hundreds of miles away.

These types of devices are not cheap and work very efficiently. My client got their SQL Server environment migrated to the new backup appliance and found a nice vulnerability within the device. Their previous backup solution maintained the retention policies whereas this new device did not. After using the new solution for a few months they notice performance decreasing almost to the point of moving back to their old solution.

After a couple of days of research into the issue it was ruled that the root cause was having too many files in the backup directory. Since several of the servers contained databases with low RPO’s, they were making lots of transaction log backups. Several of the tlog backup folders contained 10′s of thousands of backup files all with similar names.

Once the tlog folders were purged the system returned to a normal operational level. It was explained to the client by the vendor that they shouldn’t have more than 1000 files per folder and no more than 1000 subfolders.

The first lesson is that anytime you fundamentally change a solution of a critical process, you need to have a process in place to account for every aspect of the previous solution. Had this client built a proper purge process, they would not have brought a top of the line backup device to its knees.

The second lesson is that if you are making frequent transaction log backups for a system with multiple databases on it, you should consider writing the transaction logs to individual sub folders. The best practice would be to have a tlog folder with a sub folder for each database.  Having this in place will limit the number of files per directory.






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads