How to break a high end de-duplication backup device

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.

This entry was posted in Backups/Recovery, SQLServerPedia Syndication. Bookmark the permalink.

One Response to How to break a high end de-duplication backup device

  1. Scott Gleason says:

    Funny, just last week we ran into major issues with our high end de-duplication backup device when we tried to replicate the Prod and DR backup devices to themselves. When we enabled this feature, the device would not accept any files from the SQL servers at all.
    Turned out we spend three days troubleshooting the issue (with three different support personal each day) only to have the last support person inform us that there was a bug in the OS. Once we patched the OS the device worked perfectly.
    Luckly, the DBAs in the past set aside large amount of room on the SAN so that the Tlogs would not use up all the LUN space.

Leave a Reply

Your email address will not be published. Required fields are marked *