| Author |
Topic |
|
madness
Starting Member
13 Posts |
 Posted - 2005-05-10 : 15:40:00
|
| Our databases are currently backed up and then compressed to save disk space. However I would like to cut down the overall time this takes and minimise disk IO by backing up directly to a compressed file, (ie zip). I understand there are tools on the market that provide this solution. however I don't believe that this should be to hard to implement and was wondering if anyone had made any progress looking into it? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-05-10 : 16:14:53
|
| The only thing I know of that does something like that is SQL LiteSpeed. I've never used it, but I hear good things.Writing your own thing to go direct to Zip is going to be terribly complicated and prone to problems. I'd buy something that did it.Backups are not something to mess around with. Really, what's cheaper? Disks, backup software, or losing your data?The answer to that question is your answer methinks.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-10 : 18:16:56
|
| In addition to Imceda's SQL LiteSpeed (http://www.imceda.com/), Idera (http://www.idera.com/) make a backup utility that compresses on the fly. It's cheaper than LiteSpeed, but I don't know of anyone who uses it, and I know MANY who use LiteSpeed and swear by it. I'm definitely a LiteSpeed apostle.I couldn't tell you how easy or hard it would be to program something like LiteSpeed, but I can tell you you'll spend more time and money on it than simply ordering the software. Plus their support is absolutely top-notch and their reputation is rather high and well-deserved. As Michael stated, you're far better off going with them than writing your own. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-10 : 19:01:29
|
I'll stand behind Rob on this one (a lot of us will).  We evaluated several products and chose SQL LiteSpeed because of it's easy integration, incredible support, and reputation. Besides that, Tara has free scripts you can use to get you started a little faster. They cut some great deals on the product, so why reinvent the wheel when they've perfected it?If you want to sell something, then have a go at it. You have VERY tough competition though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-10 : 19:51:53
|
| FYI, Imceda was just bought out by Quest software. -ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-11 : 00:41:01
|
| Yeah, they have a lot of good products also though. I just looked at a bunch of them today. Idera also has good support btw.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
madness
Starting Member
13 Posts |
Posted - 2005-05-11 : 17:53:55
|
| Litespeed sounds like the way forward then!! I'll give it a whirl. Thanks for all your replies. It's a pity microsoft didn't include this in Yukon. |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-05-11 : 18:08:27
|
| One more thing:You can always backup directly to a compressed folder structure. It doesn't save you quite as much space as Litespeed or a Zipped backup, but its free. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-11 : 20:19:57
|
quote:
You can always backup directly to a compressed folder structure. It doesn't save you quite as much space as Litespeed or a Zipped backup, but its free.
Considering the problems you're having with it here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49630I would say it's not a good suggestion. It is best to avoid using filesystem compression for SQL Server:http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspxEven though that article does not discuss backup files, the entire I/O process is saddled with overhead that is better utilized elsewhere. SQL Server works best when file operations are clean and sequential. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-11 : 20:33:04
|
| yes, compressed folders will kill the performance of your system.Most shops backup their SQL boxes to a local filesystem. They then depend on the automated tape backup job to sweep these filesystems for new files and copy them to tape.The problem with using the compressed folders is you have huge I/O overhead when writing the file (not to mention CPU). Then when the tape backup comes along the file needs to be decompressed and sent to tape. This happens on the fly, but it is a CPU hog and you will see your I/O go through the roof.Also, most places don't have enough spindles or distinct RAIDs setup to balance out their I/O load. Some places I have worked even put their backup directory on the same filesystem as their logfiles. Imagine all the additional I/O overhead if you also throw compressed folders into the mix. -ec |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-05-11 : 23:22:31
|
quote:
Originally posted by robvolk
quote:
You can always backup directly to a compressed folder structure. It doesn't save you quite as much space as Litespeed or a Zipped backup, but its free.
Considering the problems you're having with it here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49630I would say it's not a good suggestion. It is best to avoid using filesystem compression for SQL Server:http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspxEven though that article does not discuss backup files, the entire I/O process is saddled with overhead that is better utilized elsewhere. SQL Server works best when file operations are clean and sequential.
Rob,Repectfully, the issue I posted earlier today has nothing to do with the suggestion I made in this thread and your entire argument against it is a red herring. Admittedly, no one is required to be logical and offer good arguments, but by doing so you assist the entire community that frequents this forum. Eyechart is on the money about the CPU and I/O overhead, however he overstates the impact because he makes some incorrect assumptions.For instance: not every SQL Server installation requires and/or has the resources it takes to follow every best practice. It's quite possible that the company that Madness works for fell on relatively hard times and is strapped for cash. Or, perhaps, his SQL Server is quite under utilized but lacking on storage space for backups.The point is that there are plenty of situations where using compressed folders for backups is perfectly reasonable. The original poster didn't list enough information for us to make that decision for him, but he doesn't have to. He was asking for ways to cut down the overall backup time and minimise disk I/O over an additional compression step, and compressed folders are a way to do just that. As with everything, the implementor has to carefully balance the pros against the cons for each alternative and select the one with the most important advantages and/or the least harmful disadvantages.Keep in mind that I am not advocating compressed folders over SQL Litespeed. If you have the ability to purchase and use Litespeed, then by all means do so. It's really a fantastic product from a fantastic company. But thats not the point. I'm just offering another alternative that has different advantages and disadvantages.To that end, I humbly admit that I should have been a more responsible member of this forum by pointing out the possible pitfalls in my initial post. By posting too little, I appeared weak minded and therefore triggered that elitest urge you had to jump all over my 'inferior' suggestion. Jeez, how could I be so stupid? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-12 : 07:12:52
|
quote:
Jeez, how could I be so stupid?
You could completely overreact to a post and make an ass out of yourself by sticking to an argument you yourself undermine with your reply. You could accuse veteran members of the forum of elitism and misdirection when they are only offering their best advice based on experience and knowledge, while naturally pointing out this same quality in yourself as a noble contribution. And you could call the advice of a noted Microsoft support engineer and published author a "red herring" that should be disregarded simply because you feel it has nothing to do with the problem, even though it does.Full marks for the troll though. |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-05-12 : 10:35:06
|
quote:
Originally posted by robvolk
quote:
Jeez, how could I be so stupid?
You could completely overreact to a post and make an ass out of yourself by sticking to an argument you yourself undermine with your reply. You could accuse veteran members of the forum of elitism and misdirection when they are only offering their best advice based on experience and knowledge, while naturally pointing out this same quality in yourself as a noble contribution. And you could call the advice of a noted Microsoft support engineer and published author a "red herring" that should be disregarded simply because you feel it has nothing to do with the problem, even though it does.Full marks for the troll though.
Its nice to see a noted Microsoft support engineer and published author refute my logic, instead of posting an angry backlash. Added w/edit:Listen Rob: I have no beef with you. Settle down, focus on helping people, and move on. I really have a great respect for your knowledge and your accomplishments, but it would be nice to see your behavior improve. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-12 : 11:10:25
|
| lazerath, you don't realize it but your post that rob links to is exactly the problem I described with using compressed folders.You probably have disk queueing through the roof. On top of that, you put your compressed folder on the same disk as your logfiles. Then you attempted to backup the logfile. Think how much Disk I/O is going on there. No doubt this is a RAID5 also, which compounds the problem even more. Have you looked at things with perfmon?Using compressed folders with SQL Server is a bad idea altogether. btw, I posted my response because of past experience, not because of an "elitist urge". -ec |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-05-12 : 12:44:34
|
quote:
Originally posted by eyechart
lazerath, you don't realize it but your post that rob links to is exactly the problem I described with using compressed folders.You probably have disk queueing through the roof. On top of that, you put your compressed folder on the same disk as your logfiles. Then you attempted to backup the logfile. Think how much Disk I/O is going on there. No doubt this is a RAID5 also, which compounds the problem even more. Have you looked at things with perfmon?Using compressed folders with SQL Server is a bad idea altogether. btw, I posted my response because of past experience, not because of an "elitist urge". -ec
Eyechart,Our server has a mirrored array for the Operating system and three RAID 5 arrays (one for backups and applications, one for data, and one for logs). The only thing on the log array besides logs is that single large exported file.Because the logs were rapidly expanding, I chose to add compression to the huge CSV for the sole purpose of freeing enough space to prevent the log from overtaking the drive.During the time the compression was occuring there was indeed increased disk queuing, but once in that situation what other options did I have? Regardless, it bought the time I needed to fix the situation.I fail to see why that was a bad thing? What would you have done in my situation (other than not be in it in the first place)? I understand the mistakes I made and clearly would have done things differently, but we live and we learn, neh?Other than that, please understand that my comment wasn't directed at you, nor was it an attempt to attack or offend Rob. Unfortunately, I came across as a little too sarcastic and I apologize to both you and Rob, foremost, and to any who have followed this thread. |
|
|
|
|
Online Backup Compression