| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-11-03 : 18:35:41
|
| Hello,Our production db is required to be on-line 24/7 except in 2 situations:1. 2 hours downtime in case of disaster recovery on a week day2. s/w or h/w upgrade on a weekend.Also we can only afford to lose 1 hr of data so we have log shipping.Now that we are moving our data to SAN, I want to know if any of you on SAN have eliminated SQL backups and implemented Snap shots and Clones because they serve the purpose better? I realize that snaps require pausing of db (which i cannot use instead of log shipping) but what about clones? Is SQL backup better in other ways too?**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-03 : 19:06:24
|
| We have been using the SAN technology for over a year now and have always used the BACKUP command in SQL Server for our backups. I hear that some of the products can take backups faster, but our backups have not interfered with performance as of yet.Tara |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-04 : 02:50:18
|
| We too backup to straight to SAN disk (we use SQL Litespeed) and the backup volume has a BCV set up for it which we can snap off and present to a media server which then backs it up to tape. So whilst not employing snapshot backups as such , we do utilise the BCV to get the backups onto tape. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-11-06 : 06:22:02
|
| We are looking at implementing SNAPSHOT's as part of our backup scenario (using IBM's FASTT900 hardware).Our theorey, atm, is to put DB into readonly mode, ensuring there are no updates, snapshot (takes about 2 sec's), put database back into read-write. The SNAPSHOT volumes are then mounted on another server (server 2), and the files attached to the SQL instance on that machine. Then we backup that database via SQL Backup, to a drive on Server2. That backup will go to tape, and be used for other purposes.The reason we don't use CLONE is that, in testing, on a 50 gb database, the CLONE of the snapshot took 1h20 minutes, whereas the SNAPSHOT, attach and then backup of the attached database to 20 minutes.anyone want to give comments on the approach ? Lessons learnt ?*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-06 : 12:22:57
|
| In our environment, we can not afford the snapshot approach. We've got very high availability requirements that wouldn't allow for the detach/attach method. Our only option is to take online backups. We have talked about looking into SQL Litespeed, but nothing concrete yet. The server admins have come up with some recommendations for hardware level stuff, but we haven't given those a lot of thought yet.Tara |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-06 : 14:41:44
|
With the cost of SAN storage, SQL Litespeed really does pay for itself and is much more "tweakable" than a native SQL backup (in terms of threads,cpu affinity,priority etc). Whilst we may have to "get smarter" in the future as more and more VLDB's are deployed, the combination of Litespeed and BCV's does a good job at the moment. I am a firm believer in keeping things as simple as possible when dealing with backups  |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-06 : 15:18:18
|
| What is a BCV? Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-06 : 15:21:57
|
| I had to google it:Business Continuity VolumeI wonder if we already have these here.Tara |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-06 : 17:25:20
|
| If you're really interested [url]http://www.emc.com/products/product_pdfs/ds/timefinder_l700-4.pdf[/url] |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-06 : 17:36:19
|
| JasperWhat is your opinion of SQL Lightspeed ? It looks like a really interesting product. What is it doing differently to speed up backups ?Damian |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-06 : 17:53:44
|
I really rate it. The main benefit in my opinion is in fact the decrease in size of the backups - I see the speed increase as an added bonus.I have some figures (not to hand unfortunately) but given the cost of SAN storage, it really does pay for itself for any database over a few GB. It also gives you flexibility. You can either - decrease the amount of disk allocated to backups now
- offset future expansion costs
- keep more backups "online" rather than on tape
It also speeds up the whole backup process because there's less data going to tape. The extractor utility also allows you to convert a Litespeed backup into a native backup so it can be restored onto a server without Litespeed installed on it (handy for refreshing dev/qa environments etc). For highly sensitive data , you can optionally choose to encrypt the backups as well (for a slight CPU overhead). With the latest version there is also a nice mmc snapin GUI for managing native/Litespeed backups which includes a maintenance plan conversion wizard. I prefer my own approach [url]http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=864[/url]And no, I'm not on commission  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-06 : 17:56:51
|
quote:
Originally posted by jasper_smith
The extractor utility also allows you to convert a Litespeed backup into a native backup so it can be restored onto a server without Litespeed installed on it (handy for refreshing dev/qa environments etc).
That is very cool. I've e-mailed my boss about this product. I guess he had already considered it but he didn't get much enthusiasm from the necessary groups.Tara |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-11-06 : 18:10:55
|
I just noticed the second part of your question and to be honest I don't know the details. It uses the VDI interface and with multiple threads, on the fly compression, maybe a larger buffer transfer size plus the ability to use more CPU if it's available it gets the job done quicker. It does rather raise the question why the native backups aren't as efficient/configurable out of the box  Microsoft use Litespeed themselves  |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-06 : 18:18:47
|
| It does raise that question doesn't it Log Explorer raises similar questions. But when MS build in those sorts of features they get accused of crushing competition, a no-win situation I guess.Thanks for the info.Damian |
|
|
|
|
SQL Backups on SAN