Database administrators: are you backing up your SQL Servers to local drives? If so, you need to stop for a minute to think about a few possible side effects, and maybe think about backing up to a network share instead. Why? I’m glad you asked!
Get your data off the server faster
My fellow Quest SQL Server expert Bryan Oliver has a great question: he asks, “Why do you back up?” There’s only one reason: to be able to restore.
If your server crashes, you need to be able to restore as fast as possible. In the event of a hardware or operating system error, if your data is on a local drive on that dead SQL Server, you’re going to take much longer to do a restore. You might be running into the datacenter to go pull out the hard drives, or maybe you might spend valuable time trying to resuscitate a dead operating system.
Instead, if the backups are on a network share, you can immediately start restoring those backups onto an other server. The faster you can get back online after disaster strikes, the better you look. Then you can take your time troubleshooting the dead gear.
Restore your data to dev/QA faster
Do you frequently refresh your development and testing SQL Servers from production backups? You can do this faster and with less impact to your production servers if those backup files live on a network share instead of on your production server.
Easier log shipping with multiple subscribers
When you have multiple log shipping subscribers pointing to the same publisher, they all need to read those same backups from your central publisher server. The more subscribers you have, the more impact it is on the production publisher because all of those subscribers will be copying the data off the publisher.
Save yourself performance by simply writing the backup to a network share in the first place. Then the write is done once, it’s off the production publisher, and it can go on doing what it needs to do: serve database users.
Faster writes to tape
If you’re writing backups to disk first, and then offloading those backups to tape, you suffer a performance hit whenever the tape backup agent connects to grab the backup files off local disk. You’ll get a performance improvement if you write those files off to the network to begin with, because then the tape backup software can grab the files off the network share – hammering your file server instead of your valuable database server.
Your bottleneck probably isn’t the network card
Sometimes I hear DBAs say that they’d rather dump the data onto local disk first to get the backup over and done with faster. Sometimes – but rarely – is that actually true. Many times, especially if you’re using backup compression software like LiteSpeed, the network isn’t the bottleneck.
Want to prove it? Try it yourself – do a backup to a known fast network share, not some slow junky Pentium 2 in the antique section of the datacenter. Time to see how long it takes to finish the backup as compared to writing to local disk. If it takes less time, you should switch to the share right away. Even if it takes 10-20-30% longer, I would still argue that you should switch because the faster you can get those backups off your server, the safer you are. And I like you – I don’t want you getting fired for not being able to recover your data! I can’t afford to lose any of my blog readers.