April 17, 2002 at 4:14 pm
I’m having problems coming up with a workable disk file backup plan for my SQL Server 2000 databases. Network Services is currently running nightly full database backups of all databases to tape using Veritas SQL backup module. These backups are taking less than 5 minutes per database. But I would also like to have disk backups available, should there be a problem with the Veritas backups.
I’m currently running full database backups to disk every night. I have 15 databases; the largest is just under 2GB. I have 2 local disk drives, RAID 5 on the SQL Server box. SQL Server is sitting on a clustered NT server, Active/passive clustering.
Scenario 1) I put DATA files on one drive, LOGS on the other drive. I used to put disk file backups on the DATA drive, just before OS nightly backups to tape were run. But, the OS backup of these 2 drives has been discontinued because open SQL files are not backed up. Problem: Putting backup files on the same drive as the DATA, without backing these files to tape, defeats the purpose.
Scenario 2) We were then given access to a network drive, that does get backed up on the OS nightly backups. I tried putting the backup files directly to the network drive, but the largest database that took less than 6 minutes to backup to a local drive took anywhere from 1-2 hours to backup to the network drive. As I added more database backups to the network drive, if a backup started while another was still running, the disk contention made some jobs run 2-3 hours. Problem: Not enough time to backup all database files to the network drive before the OS backup begins.
Scenario 3) I created jobs to backup the databases to a local disk(step 1), then ran an OS copy to copy the backup file to the network drive(step 2). These OS copies are taking 1 to 4 hours. Problem: Same as Scenario 2.
None of these scenarios deals with the transaction log backups. At this time, we have only 2 databases that need logging. Soon, we will be adding an Accounting database that may require backing up the trans log several times during the day. With no OS backups running during the day, should I opt to put disk backups of the transactions logs on the same drive as the DATA? Or, should I OS copy the disk transaction log backup files to the network drive after they have been created on the local drive? Will disk contention with another server’s disk drive slow down the SQL Server box?
Has anyone else had experience with limited disk space and/or network drives? Should I let Veritas handle all the backups? Has anyone used Veritas for transaction log backups to tape, especially when backups are to be done several times during working hours?
April 17, 2002 at 5:38 pm
I deal with limited space but so far my time has not been an issue. Plus I have a free drive with enough space for the backups without it being on the same drive as the Data. I see some major downsides to your doing it to the same drive as your data. First when you write data to the drive multiple reads and writes occurr on a Raid 5 setup (write to drive, read and calc parity, write parity, recalc parity across drives and move as needed). Also writting and deleting data from any drive increases the fragmentation of the drive itself.
Here are some ways that can possibly help. Write your backup to occurr to the drive (or preferable to another drive with neither the data or logs and not RAID 5 based. Then when done using pkzip or someother command line compression compress the file (usually is about 18% it's original size) then ship to the network drive. Also take into consideration the speed at which you connect to the network and the network drive is connected, whichever is slowest will cause a bottleneck, see if you can move up to at least 100MB and try to get a dedicated connection if possible. My preferred option is find an older machine with enough HD space that isn't being used and put a 100MB or better NIC in it, get a small switched hub and put a NIC of the same level in the SQL machine. Then you have a network to yourself that does not have to worry about bandwidth concerns due to other users. Now you have another machine you can use to move to the network drive at your leisure or do backups from it. Really your porblem is not just space but bandwidth and the softwares method of doing the backup. These are the best suggestions I can think of but I am sure people have found even better ways than this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 18, 2002 at 5:12 am
Not sure why you stopped the backup of the data drive, why not just put your backups in one folder and have the backup job only do that folder? I agree that it's good practice to backup and then move to another machine (if you can). SQLZip has a new utility out that will generate compressed backups, see their ad on the site or my review:
http://qa.sqlservercentral.com/columnists/awarren/reviewofsqlzip.asp
Andy
April 18, 2002 at 8:20 am
Stopping the backup was not my decision. AND, all backups were already in just one folder. Network Services stopped the backup of both drives, when they began using Veritas SQL backup. Sadly, I didn’t find out the backup had been stopped until we were having a (rather, heated) discussion about getting access to a third disk drive. For at least 2 months, I was creating backup files that were never being copied anywhere else. When we finally were given access to a third drive, it was very slow. Network Services response to ‘Is it possible to speed up this connection?’, was simply ‘NO’. This is part of the reason I am posting here. I’ve spent weeks documenting backup procedures, juggling between disk drives, changing job schedules, and trying to make this work. I’m hoping that printing these discussions, opinions, problems, solutions, will give me additional ammunition in future discussions. Thanks for the info. I will be checking out the SQLZip software. Of course, if it’s more than $100, this is a capital expenditure and our budget won’t be able to handle it.
Not sure why you stopped the backup of the data drive, why not just put your backups in one folder and have the backup job only do that folder? I agree that it's good practice to backup and then move to another machine (if you can). SQLZip has a new utility out that will generate compressed backups, see their ad on the site or my review:
http://qa.sqlservercentral.com/columnists/awarren/reviewofsqlzip.asp
Andy
[/quote]
April 18, 2002 at 10:12 am
I'd drop them to the local drive and then ship them. SQL Zip will work, also you can use my PushFTP process. Been using it for months and had to in real time get the ftp'd bakcup files last night when my RAID controller failed.
http://qa.sqlservercentral.com/columnists/sjones/pushftp.asp
You can use this same process and instead of writing out a file with the file names, use the .copy method of the filesystem object to move them to a network drive.
If you network copy is taking so long, I suspect you might want to change the timing of your copy or setup a 2nd NIC for sending the backups across the network.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2002 at 4:05 pm
SQLZip starts at about $500. A combination of zipping and Jonesing (FTP) will work if thats all you have. Second NIC is a good idea, can probably get that done under $100. Nothing wrong with copying to a different drive (I dont, Steve does). Curious why they chose to stop the backup entirely though. Not like it's hard to tweak the job.
Andy
April 19, 2002 at 4:58 am
quote:
Sadly, I didn’t find out the backup had been stopped until we were having a (rather, heated) discussion about getting access to a third disk drive. For at least 2 months, I was creating backup files that were never being copied anywhere else.
I am surprised they did this to you and would under most circumstances look to get a backup device of my own with the statement to management about the reason why and the fact that you cannot be responsible for data loss if soemone is not doing the job that was being expected of them. Who knows you might just get a device of you own and all this will be mute anyway.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 7:04 am
We really push to have our SQL servers built with a backup array -- away from the data and log drives. We run the backups to the backup array and then copy it off to tape or disk. I have no problem with the backup array being RAID 5 -- it's economical, fault tolerant, and fast enough to backup our 65 GB database in about an hour. I couldn't do the same to a network drive, and it would take at least four times longer to write it back to the same array that hold the data -- reading and writing to the same disks.
July 22, 2002 at 10:36 pm
SQLZip may be _an_ option. However, if you want a fully featured, Microsoft Certified product, you should check out SQL LiteSpeed. Rughly the same price, but offers so much more ... encryption, striping, backup sets, lower CPU usage, supports fast file I/O thus much faster backups (fastest on the market), cluster support, simultaneous multi instance backups and more.
Worth a look.
JS
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply