January 8, 2015 at 8:30 pm
Comments posted to this topic are about the item Multiple Backup Files
January 8, 2015 at 11:59 pm
From my experience… multi stripe SQL backups are not used for small DB, but are commonly used when DB are TB large. So it may be just a few percent of the DB which are backed up that way, but it’s a far higher percentage of the backup volume!
Multi stripe is supported by common backup application such as Data Protector, Networker, NetBackup, etc., and it’s very easy to setup them that way. If the DB is setup for performance (multiple data files on different partitions or mount points), SQL reads all of them in parallel during a backup, and in this case having multiple output devices really helps performance.
I’m not sure the performance improvement is large if you backup to local or SAN disks. But if you backup to tapes or dedup capable devices such as StoreOnce or DataDomain, having multiple output streams really helps performance.
January 9, 2015 at 3:24 am
One of our clients (not hosted, but we look after their environment for their LOB) uses NAS replication to copy the backups to our site for "off site" backups. Blissfully unaware that the NAS replication doesn't work on files of a certain size, no error raised, no documentation. Some comments on gogle. So a good few weeks of backups were not being replicated to our site. Backups were being created at the clients site and tested. But no off site backup was created of the big files, only found out when the scheduled refresh of the test environment stopped.
Simple to script the backup job to use multiple backup files if there is any chance that a backup file exceeds the NAS limits.
Separation Of Duties in companies can be a real SoD when it comes to things slipping between the cracks.
January 9, 2015 at 3:46 am
I only do that in situations where I need to backup the database urgently & no single drive attached to the server have enough space to store the complete full backup file of the said database.
Other than above situation I find no use of this technique. May be it can provide little bit of performance improvement but the management effort in keeping those multiple backup files negates that. If I am keeping 4 backup files on 4 drives & any of the drive goes down I am out of backup ! so no, I am not comfortable with that.
January 9, 2015 at 3:56 am
Yep, I've done this one large database. 1.2TB backup was taking over 5 hours and the same for the restore. Implemented striped backup set with 8 files and got the time down to around an hour. This was used for a weekly restore from the Production database to a Reporting instance. Eventually we moved to a SAN based snapshot mechanism but the striped backups was in place for about a year.
January 9, 2015 at 4:13 am
My main use for striped backup files has been when migrating large databases. Having multiple backup files means that when copying these files (either over the network or onto a NAS device) the files are smaller so if there is a failure during the copying process it's a lot less to re-start with smaller files ;-). Other than that, despite dealing with databases over a TB in size, I've not used striped backups!
January 9, 2015 at 6:13 am
Like you, I'm working with databases in the small to medium size range. I haven't had to set up multi-file backups, but I'm sure it'll come one day and it'll probably been sooner than I think.
January 9, 2015 at 6:38 am
I have used striped backups for databases big and small and have done restores many times with no problems ever.
The time savings can be significant for large databases and makes use of all the processors on the server since multiple threads are used.
One job was with a hospital and their clinical database was up 24-7 so doing backups on their very large database was much quicker when we split it into multiple files.
Another benefit is when you need to send a copy of a database to another server for testing or to a vendor for problem resolution you can break it up into multiple smaller files instead of trying to send one huge file. If the transmission fails it is easier to deal with one re-send of a small file than a huge one.
Huge fan of striped backups.
January 9, 2015 at 7:05 am
I recently implemented striped backups based on an article I read on this site for our two nearly 1 TB databases. It reduced the backup and restore time needed by half (about three hours to 90 minutes). The four files for each backup take about the same space as the single file did. I have done several routine test restores and have not had a problem once.
January 9, 2015 at 7:21 am
Yes for me as well I work with a Small/Medium sized business. We actually have a database for each job that we process in the field 200+ jobs a year. The Databases only range from 50MB up to 150GB in size per DB so striped backups is not something I normally perform as it is not worth the time or hassle. However coming also from a Backup Administrator point of view I also use Backup to Disk with Symantec technology and have noticed striping Backups leads to better performance. Kind of like using Raid 0 however just like the article says it leaves you susceptible to losing the whole backup if one of those files are corrupt or lost.
January 9, 2015 at 7:25 am
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?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 9, 2015 at 7:28 am
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?
I achieved my 50% gains when striping to the same drive.
January 9, 2015 at 7:45 am
We have found that once a database grows beyond 1TB striping is required, if only just to fit into our maintenance window. We currently have been backing up a 2.5TB database to four stripes weekly and the job (Ola's scripts) completes in about 1 hour. We run a daily differential to one stripe in about 1 hour as well (100 GB avg.) and log backups every half hour (following an index rebuild job, these can be 150+GB in size). Restores from those backups occur more than once a week, from various points in time as we load production into testing/development environments, so we test the backups frequently and often. Never had a concern over losing a file, but we do write directly to a DataDomain NFS. This environment is logged shipped to a DR site, and mirrored to a reporting server with CDC turned on for many of the busier tables to load a DW staging environment nightly. We have looked at most of the bells and whistles we can find, and hope to move to 2012 (currently 2008R2) around April.
January 9, 2015 at 8:41 am
Test and find a balance that works for your environment. It does add complexity but when time is money it can be worth it. I had a ~1TB db and got the restore times down to 15min. I tried a lot of scenarios and what worked for me and our shop was to backup to 4 files and max out the buffercount and maxtransfersize. We used 3rd party compression and Ola Hallengren's famous script. The recovery time objective (RTO) was critical so anything to dramatically reduce restore times was a huge win for the business.
January 9, 2015 at 8:45 am
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply