Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

What hardware would you recommend for saving database backups with the following setup:

5 separate physical hosts, each with one Sql Server 2008 database. Each instance is larger than 200Gb. Each database does one full backup per day, and differentials every few hours. I want to keep a week of backups off-machine in a location that makes it quick and easy to restore a database to a different box if necessary.

Currently they are all sending compressed backups to a single large file share, which is overwhelmed and causes backups to fail frequently.

And as an additional question, what's the fastest and most cost effective way to copy the local backups to a remote server?

share|improve this question
add comment

4 Answers

You mentioned that currently your backups are failing because they are being sent to a single share - it does not seem like buying cheap hardware to create another share would help.

I think first you need to look at where your bottlenecks are, and then engineer around those backups. If the bottleneck is the network and not disk or CPU, then you would have a different solution. This is highly dependent on your data access patterns, you have 200GB of data but how often is is SELECTed and UPDATEd?

An option may be to set up a Master/Slave instance and have your main SQL server write data to the slave and the back up off of the Slave server to save your active masters.

Note that I am not a DBA but as far as I know this answer is fairly accurate, hopefully someone more knowledgeable than me can answer further!

share|improve this answer
    
The IO on the databases themselves is roughly 50/50 read/write, so a master-slave setup doesn't work so well, not to mention doubling my hardware and license costs. –  Eric Z Beard Jan 5 '10 at 18:22
add comment

I would use the cheapest, largest NAS I could get.

An effective way to copy them would be to either dump the SQL BAK files on the SQL server itself and schedule a robocopy BAT script, or if the NAS supports FTP, using FTP in a scheduled script to copy them.

A potentially better way would be to write the SQL BAK file directly to the NAS share, but you'd have to determine if it could handle the IO.

share|improve this answer
    
The IO is definitely a problem. A single backup being sent to the NAS can saturate the Gigabit NIC. –  Eric Z Beard Jan 5 '10 at 18:21
    
OK, then don't get the cheapest NAS you can get. Get one that can sustain the IO you're generating. Alternatively, do the other - backup locally to the SQL server's own filesystem, and then do staggered copy jobs to NAS. –  mfinni Jan 5 '10 at 18:30
add comment

Without knowing your budget or load, I'd think of trying to divide your backup storage to a couple devices, just to offset the network load a little.

I don't know what others would think...I'm not a specialist in databases, and we don't have anything on a huge scale like a terabyte of data + diffs to store and manage, but I'd wonder about adding a second NIC to the database servers and having a separate switch that connects to a couple of servers acting as NAS devices. Have servers 1,2, and 3 go to backup server 1 and servers 4 and 5 go to backup server 2 on the second LAN. that should ease some of the network congestion, and having separate machines may help alleviate some of the burden on the drives and drive throughput.

I'd also consider getting external drives attached to the servers so you can save it to a "local" drive, then you can pull data from the physical external disks to another system for a direct copy and not saturate a nic or switch. It requires physical intervention, though, and couldn't be easily automated (although you could script something that keeps only files/backups of a certain date to keep from overfilling the file system). It would require 5 large external drives, but it may prove faster than saturating your network.

I'm sure others have better ideas, though. This would probably be something to consider entry-level or on the cheap.

share|improve this answer
add comment

Are you running transaction log backups? Perhaps running transaction log backups every 5 minutes instead of the differentials will be less intensive?

share|improve this answer
    
I had to stop transaction log backups because the files were enormous. They take up a lot more space than differentials. And they make things run slower too. –  Eric Z Beard Jan 6 '10 at 15:28
    
How often were you running them and I assume you were compressing them too? Sounds like you have high transactions per second. –  SuperCoolMoss Jan 6 '10 at 21:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.