0

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?

Zoredache
  • 130,897
  • 41
  • 276
  • 420
Eric Z Beard
  • 503
  • 1
  • 6
  • 12

4 Answers4

1

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!

Dave Drager
  • 8,375
  • 29
  • 45
  • 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 05 '10 at 18:22
1

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.

mfinni
  • 36,144
  • 4
  • 53
  • 86
  • The IO is definitely a problem. A single backup being sent to the NAS can saturate the Gigabit NIC. – Eric Z Beard Jan 05 '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 05 '10 at 18:30
1

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.

Bart Silverstrim
  • 31,172
  • 9
  • 67
  • 87
0

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

SuperCoolMoss
  • 1,262
  • 11
  • 20
  • 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 06 '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 06 '10 at 21:08