Tuesday, December 4, 2007

Backups, backups, backups!!

Backups are quite possibly the most important part of a SQL server administration situation, but they're almost always overlooked.
The servers I look after have various different methods for backing up data ranging from local disk backups to UNC NAS storage, and even dedicated SAN LUNS. Pretty soon I'll also start streaming directly to tape for some of the multi-terrabyte backups I'll have on my hands but let's not worry about that.

I've got a few tips for anyone that needs to admin a SQL server:
1. Always make sure you a backup plan that will meet the needs of the business.
2. Use all the backups types available wisely. Transaction log, differential, and full can all be used to create restore plans, but keep in mind recovery time!
3. Always verify your backups! I recommend a script that does at least a checksum check (you are backing up "with checksum" on SQL 2005, right?), and more is better.
4. If possible, use dedicated NICs and VLANs for backup traffic.
For point 4 I setup a static route like this:
ROUTE -p ADD 10.xxx.xxx.xxx MASK 255.255.255.0 10.yyy.yyy.yyy METRIC 10
-xxx IP denotes the target subnet of your backups (i.e. a cifs share on a NetApp or other NAS... be sure to set the proper mask!)
-yyy IP denotes the adapter that'll be used to connect to the share (dedicated NIC)
-p makes it survive reboot

No comments: