Eric M Russell (1/9/2015)
There are probably a lot more multi-TB instances than there are multi-TB databases. I don't currently manage backups, but in our environment we have a 10 TB instance partitioned both by client and by seperation of periodically updated reference data from OLTP data. A significant portion of our databases and file groups are set as read-only where allowable. So my understanding is that we leverage partial backups to greatly reduce the total amount of data included in the daily full backups, which also allows for graceful partial restores.
I'm not sure if the sysadmins also leverage striped backups, but I'm guessing that the type of storage and how effectively it distributes parallel I/O across multiple files is key here.
My question is:
From your experience, does striping to a SAN or a single NAS or DAS drive offer the same performance benefit as striping to multiple NAS or DAS drives?
Stripping usually increases backup performance. But is that SAN the same SAN where you keep your data files? If the answer is yes, then that's a really bad idea. And not because performance degradation but due data loss. Imagine your SAN going down. Then your backups and databases are gone.
Now, I've copied backup files locally, stripped and compressed and then move to an external backup repository. That way. I have two backup copies: one locally at the SAN and a remote location, for redundancy.
Also, in my experience and I've been working with TB size databases mostly all my DBA career, I don't find stripping useful on any database below 100GB. Of course, it depends, but usually the performance gain with anything smaller than that is not noticeable in terms of backup time reduction.
Another important point, at least when using RedGate, is that when you split backups, the backups can't take advantage of using multiple threads to create backups. Multiple threads are only allowed when is a single backup file.