jump to navigation

SQL DB Backups storage area September 24, 2008

Posted by msrviking in Configuration.

I have been into a discussion with one of my clients on where should the backup files be stored for quick backup and restore operations. Here is what I had to talk about,

After some thinking on “Which RAID level should the SQL DB Backups (.BAK, .TRN) be stored in the system?”. I have below thoughts, and recommendations.

Here is the flow of my thoughts.

1)    Does it matter on which RAID levels the backups are taken, be it .BAK or .TRN files or any kind of backups?

Yes, it would matter in systems that have multiple VLDB (Very Large Databases), where the transactions are high, several backup schedules run on multiple databases, taking different types of backups at different timings.

Although the current environment doesn’t have multiple VLDB high transactional databases with backup schedules running during the whole day for different database, I thought we shouldn’t go by the current configurations and should look from a long term perspective which led me to the next question.

2)    How does it matter? Does it matter in terms performance of taking backups, protection of backup files, performance of restore?

Yes, it is important to configure the backups on a drive which has appropriate performance, and fault tolerance. Also it is important to have the backups restored as quickly as possible from the backup location in the system.

In terms of performance of backups of database or restore of backup files, we need to know what are the internals happening so that we could provide an appropriate environment to speed up the process.

A backup or restore operation internally has a basic operation – reading from database, pausing, writing to the media and this happens multiple times until the backup is complete. And the restore operation would be vice-versa of backup internals, reading from backup files, pausing, and writing to [In Recovery] database, until restoration is complete.

The key point in both processes is reading /writing from /to the database /backup file. So essentially we need to help SQL Server in processing this quickly, by configuring the storage devices (system drives in here) appropriately. Also, to enhance the process of backup and restore it’s not the configuration of disks that matters, but it depends on the kind of backups taken, the way the backups are taken on the storage media (drives or tapes).

With all these thoughts, and keeping the configuration of the disk as the primary point I jumped on to the next question.

3)    How do we give performance and protection together?

The SQL DB systems are currently configured to use space allocations from RAID-5 and RAID-10 level of arrays.  Because of my naïve knowledge on RAID’s, I had to refresh my thoughts on what are the different RAID levels, and what exactly it means. Here is what the RAID-5 and RAID-10 are described,

RAID-5: This type of RAID stores parity information but not redundant data and parity information can be used to reconstruct data. And when each logical read occurs, the read performance is increased since reads occur from the configured disks. Whereas when a logical write occurs there are four physical read operations, decreasing the performance of write.

RAID-10: Combining RAID-0 and RAID-1 is RAID-10, which offers higher performance and tolerance because RAID-0 has striping which has high performance output for read /write operations and RAID-1 has mirroring of disks to provide protection.

After knowing how each RAID level works, and what we need my next question was.

4)    What should be the recommendations?

Backup of the databases (any kind) should be on drives which are at RAID-10 levels. The obvious reason being that RAID-5 gives bad performance when it comes to write operations, and RAID-10 provides better read /write performance because of striping, fault tolerance because of disk mirroring.

What do you guys think about my recommendations? Let me know.

Happy reading!



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: