Hello netland,
Sorry I took so long to get a summary back on this, thats what happens
when you are working 7 jobs at once. I want to thank all those who answered
me, and used a combination of of a couple of responses to solve my problem.
Special thanks goes to Dr. Randy Garrett for taking the time to Fax me a
special Sybase bulletin.
- Dave
-- Original question
I have a problem with Sybase that I hope isn't true and that maybe someone
out there can give me some help. I am running Sybase on a Sun 4/260 platform
with a single DC600 (60MB) tape drive. I am running my Sybase databases on a
large raw disk partition.
One of these databases has had alot of activity recently and has grown to
a size of approx 250MB. Last friday when I tried to dump the database to tape
I noticed that the dump only took one tape. Even if there was some outlandish
compression scheme I figured that this should take more than one tape, so I
called Sybase "Technical Support". They told me that that Sybase did not
have the ability to back databases up to multiple tapes and if I didn't have
an extra 250 Megs of disk space laying around (which I don't) I wouldn't be
able to backup my database.
I refuse to believe that a supposedly reputable RDBMS like Sybase would
lack this capability. If it doesn't I strongly suggest to all those out there
deciding on a datbase for thier Sun/Unix platforms might want to look
elsewhere for a product that will let you backup your database regardless of
size (Oracle, Ingres, etc...).
Any pointers to help me out of this box would be greatly appreciated!!!
-- Responses
From: TAWC1::WINS%"tom@yac.llnl.gov" 7-AUG-1991 10:44:54.33
... I ran into this as well. Their "console" utility doesn't work
for 1/4" tapes for some weird reason, so you can't do dumps to multiple
tapes. I was forced to keep enough disk space around to do nightly full
dumps to disk. Actually, this is not all that bad, given that SCSI 1.2G
drives can now be had for < 2.5K, and it sure is a LOT faster than dumping
to tape.
You might think that adding an Exabyte will solve the problem. I thought
so, and made mine a Sybase "disk" dump device (to /dev/rsmt0). Well, the
only problem is that Sybase appears to dump 512 byte blocks to all "disk"
devices, so I couldn't even get 350Mbytes on a 2.2G tape! ...
---------------------------------------------------------------------------
From: TAWC1::WINS%"randy@ncbi.nlm.nih.gov" 7-AUG-1991 11:09:49.96
Using the standard Sybase server, the DC600 tape drive is supported as a
disk device, which means that you can't do tape changes. This was also true
with the Exabytes; however, we have heard but not verified that you can make
an Exabyte a tape device under 4.1.1 (but not earlier). If this is so, you
can do tape changes on Exabytes. There is also a patch release what has a
patch which may let you write to standard output and maybe named pipes. If
so, that could permit you to do a kind of workaround. That is, you could
write to a named pipe, which would chunk the output up int 55+MB pieces and
write the results to the tape which changes in between. It's a hack, but it
could work.
If you get the details on this, let me know what they are. We're interested
in this but are far too busy to deal with it at this time. We simply use
Exabyte tapes instead.
Here's all I have on that patch release:
BugId: 17490
Request: Greater flexability in specifying dump/load
devices. He needs support for standard-in and standard-out
(stdio) - he wants to perform dumps and loads to other
systems using this. Also named pipes would be useful.
That is all I have. The patch release we have is EBF #554. You'd need this
one or later. You'd also have to get the folk at Sybase to tell you how to
use this, since they don't tell you how to specify such devices, etc.
Randy Huntzinger
----------------------------------------------------------------------------
From: TAWC1::WINS%"lentz@ody1.uchicago.edu" 7-AUG-1991 11:37:11.04
I am sure that other people will be able to give more reasonable help than I
-- but I have a suggestion which (while it does not attack your basic problem)
might be a way around it until you can get a 'real' solution: if you have
access to one, or can get one easily, use an Exabyte cartrige drive; they can
store up to 2.5GB (for the 'small' one; 5.0GB for the model 8500).
One of my colleagues here uses Sybase on a Sun3 (monty@delphi.uchicago.edu);
if he does not see Sun-Managers, you might contact him directly.
We use Ingres (said he, smugly) which we like, even if it is hard to set up
applications for it.
Gordon Lentz
Laboratory for Astrophysics
and Space Research
The University of Chicago
-------------------------------------------------------------------------------
From: TAWC1::WINS%"<bb@math.ufl.edu>" 7-AUG-1991 12:18:40.47
I heard a speaker at the last USENIX call wanting to run something on
a raw partition for performance reasons a 'failure of the filesystem',
because it has failed to serve the user. I would suggest you bite the
bullet and store your database within the normal UNIX filesystem.
Disadvantages: presumably slower; however the fast file system is so
highly tuned that I am skeptical of this; I would run comparison tests
before believing this.
Advantages: your data is now suspectable to the entire range of normal
U*IX file, disk, and backup tools. Silly problems like you've found
with backups go away (and get replaced by more general silly
problems!)
"Any sufficiently advanced technology is indistinguishable from a rigged demo."
-------------------------------------------------------------------------------
Brian Bartholomew UUCP: ...gatech!uflorida!beach.cis.ufl.edu!bb
University of Florida Internet: bb@math.ufl.edu
-------------------------------------------------------------------------------
From: TAWC1::WINS%"deltam!flyer!mark@uunet.UU.NET" 7-AUG-1991 12:41:59.04
Our BudTool program will solve your problem. The newest versions has
user-loadable backup classes (commands) and so can handle your Sybase
backup command. In addition, BudTool shields the tape management issues
from the backup command. If an image is to be split, is is handled
transparently by BudTool. The backup command never knows the image has
been split.
Currently, all of our commands have image splitting turned off. Our
thinking is that Exabyte tapes are cheap. If we waste a little tape at
the end of one, we can save the user time when it comes to restoring the
tape. Since time is ususally more expensive than tape, this comes out to
a win.
If you would like more information about obtaining a FREE demo copy of
BudTool, contact our representative in your area:
Eric Madsen
Peripheral Devices Corporation
4800 N. Federal Hwy
Suite 107E
Boca Raton, FL 33431
407/750-4606
FAX 407/750-4608
If you have technical questions about how BudTool works, you can contact
me directly. My addresses and phone numbers can be found below.
--Mark Galbraith Voice: +1 415 449 6881--
--Software Engineer UUCP: uunet!deltam!mark--
--System Administrator/Postmaster Domain: mark@deltam.com--
--Delta Microsystems, Inc. Compuserve: 76234,3126--
------------------------------------------------------------------------------
From: TAWC1::WINS%"<justice@dao.nrc.ca>" 7-AUG-1991 13:07:51.09
You simply must have enough disk space (local or NFS mounted), or a local 1/2"
tape drive (for multi-volume dumps), or a local non 1/2" tape media big enough
for the whole database.
At $2,000, we found the exabyte more than satisfactory.
Gerald Justice
Unix Systems Manager (and Sybase Manager)
Phone: (604) 363-0055 PDT Fax: (604) 363-0045 Telex: 049-7295
Internet: justice@dao.nrc.ca Mail: Dominion Astrophysical Observatory
National Research Council (Canada)
BITNET: justice@nrcdao 5071 W. Saanich Road
VAX PSI: 68100434::justice Victoria, B.C. CANADA V8X 4M6
-------------------------------------------------------------------------------
From: TAWC1::WINS%"kpc!kpc.com!cdr@uunet.UU.NET" 7-AUG-1991 13:34:56.29
One really ugly approach might be to dd the raw device to your tape, but
then to restore you'd have to overwrite a partition of exactly the same
size somewhere; so its not at all pleasant. A better alternative would
be to buy an 8mm backup drive, which comes in 2.3GB or 5GB sizes.
The 2.3GB ones are now going for around $1-2000, which seems very cheap
compared to the loss of a database.
I won't hazard a guess as to whether the Sybase backup stuff is too
stupid to backup to 8mm tape.
-- Carl Rigney cdr@kpc.com--------------------------------------------------------------------------------
From: TAWC1::WINS%"<miker@sbcoc.com>" 7-AUG-1991 13:36:02.00
Get an Exabyte tape drive ... 2.5 or 5.0 GB, instead of just 60 MB.
Please be sure to summarize back to the list ... thanks!
Oh, another possibility is to use bcp to copy out individual tables, instead of a whole database at a time.
-------------------------------------------------------------------------------
From: TAWC1::WINS%"lee@sq.com" 7-AUG-1991 14:19:02.29
If the database file has lots of `holes' in it (as they generally do) -- i.e., blocks that have never been written onto -- then it *could* all fit on a 150 Meg tape.
If your data is valuable, perhaps you could justify an exabyte or dat backup drive? They're truly wonderful, and the tapes are awfully cheap.
Lee
-- lee@sq.com the barefoot programmer
--------------------------------------------------------------------------------
From: TAWC1::WINS%"mgk@sql.sybase.com" 7-AUG-1991 14:33:30.11
Yes, unfortunately the current release of Sybase SQL Server does not support database dumps that span more than one volume of a tape device. Yes, I agree with you, this is *not good*. The folks in Sybase engineering are aware that people are unhappy with various aspects of backup and restore facilities, and are "working on it." (How's that for vague and noncomittal?) You could hammer at your sales rep's ear to find out more, maybe tell him/her about the sun-managers mailing list, I imagine it would help them to realize the significance of your displeasure...
Anyway, towards solutions... I have an idea, that may or may not help you, but I'll pass it along. On Unix systems, as you are undoubtedly aware, tape devices appear as regular files. If you create a Sybase dump device, and use "disk" instead of "tape" for the device type, and use "/dev/rst8" (or whatever) as the physical name, you can get the SQL Server to think the device is a disk file. At this point you might be thinking: "So what, I can still only fit 60M on it!" Yes this is true, for cartridge tapes, but Exabyte 4mm DATs can hold a bunch more (I forget, 1.4 Gig, ? ... a *lot* anyway).
Couple of points:
- yes, this involves buying hardware, not so great, but at our place we have found the 4mm DAT solution to be a good one for all our Sun backups--to each their own...
- check with Tech. Spt. on this, they originally didn't support this kind of finagling, but I think they now support this on Exabyte drives
I hope this helps some...
BTW, I work for SQL Solutions, a wholly owned, *independent* subsidiary of Sybase, but my opinions are my own.
------------------------ Michael Keirnan SQL Solutions, Inc. (617)270-9234 x202 mgk@sql.sybase.com
--------------------------------------------------------------------------------
From: TAWC1::WINS%"Teemu.Torma@frend.fi" 7-AUG-1991 16:50:06.11
You might use dd(1) to copy the raw device onto tapes. E.g.,
dd if=/dev/sybase of=/dev/tape bs=50k count=200 dd if=/dev/sybase of=/dev/tape bs=50k count=200 skip=200 dd if=/dev/sybase of=/dev/tape bs=50k count=200 skip=400 ...
If I remember correctly, we used this method before Exabyte. I don't know how reliable this is, I have never tried to restore this kind of backup. The server should not be running during backup.
I hope this helps.
Teemu
--------------------------------------------------------------------------------
From: TAWC1::WINS%"edsr!tantalum!cheeks@uunet.UU.NET" 8-AUG-1991 00:13:22.15
... I currently have a 3 GB database split up into 15 smaller databases (a *real* aministrative headache) so that I can fit the data from each db onto one 8mm tape.
The last I checked (this was about 4 months ago) a tech at sybase told me they were revamping the backup system (ie writing one), but that it wouldn't be available 'til v5.0 ... and nobody knows when v5.0 will be out. And that still leaves you in a mess since nobody wants to upgrade their DBMS without backup up the databases first. I can find and forward a copy of the message from the tech if you want.
I have two suggestions. The first one is as yet untried by me, but I think it would work if you have time to mess with it:
Tell sybase it's dumping to a "disk dump" device, or whatever they call it when you're dumping to a file-system file. Then create a named FIFO (lookup "mknod p ...") as the file for Sybase to dump into. Attach a unix program on the other end of the pipe to catch the data and do something intelligent with it (compress it, send it over the net to a machine with more disk, give it to a program which can intelligently deal with a tape drive (cpio? some versions of tar?), etc.)
The second one is: Get a new DBMS. I've evaluated Empress, and it's a pretty nice system. The data bases are stored in normal unix files, so you just back them up with the rest of your unix backups. It's priced about the same as Sybase (ie exorbitantly), but IMHO is a much more robust system. (It also wastes much less space than sybase in storage overhead for large text/image objects, for what it's worth).
Sorry to have babbled so long ... it's just that my blood pressure goes up every time I see someone else get taken by Sybase.
Good luck. Let me know how things turn out (I'll see the summary if you post one).
Mark -- cheeks@edsr.eds.com or ...uunet!edsr!cheeks
--------------------------------------------------------------------------------
From: TAWC1::WINS%"rlg@ida.org" 9-AUG-1991 09:12:22.51
Tech support is right, you're basically hosed. Sybase backups are utterly and totally absurd. I agree that I found it hard to believe what poor support they have. There's more: They don't really support 8mm tape backups. They don't support network backups -- device has to physically be on the machine. There's no verification mechanism. Nor is there any real error notification -- as you found out -- except you notice that the backups finish way too soon.
Lastly, there's no fixes planned for a long time.
Having given all the bad news, there was a patch to Sybase and to the SunOS kernel that let you do multi-volume backup to certain devices. Let me look up the notes and I'll send you a message or you can give me a call --
Randy Garrett IDA (703)845-6688 rlg@ida.org
BTW, we have Sybase on 4 machines -- 4/280's and 4/490's. We have had constant complaints to them ever since we\ installed which was about 18 months ago. We have found them very unresponsive.
--------------------------------------------------------------------------------
From: TAWC1::WINS%"carl.johnson@UK.Sun.COM" 9-AUG-1991 10:43:12.31
Apparently its true, its supposedly fixed in version5.0 which is due mid '92, not that this is going to help much.
Cheers, Carl
ps It might be worth posting the question to comp.databases as well, you never know, someone may have come up with an unofficial work around.
--------------------------------------------------------------------------------
From: TAWC1::WINS%"ralphh@augeas.cs.athabascau.ca" 14-AUG-1991 05:10:44.82
We had the same problem and were forced to buy an exabyte tape drive so that we could dump the whole database. Much faster than any of the other tape drives we have anyways so I was not much of a sacrifice.
Ralph Andrew Hand Development Analyst Alberta Cancer Board 9707-110 Street (Sixth Floor), Edmonton, AB, T5K 2L9, Canada DESK: (403) 482-9394 FAX: (403) 488-7809
--------------------------------------------------------------------------------
My solution:
I realize that the optimal solution is attacth a large capacity storage device to the machine (4 or 8 mm tape). Unfortunately I work for the second largest bureaucracy in the world (IBM is first of course :-) ) and it is going to take me about 9 months to get my order through the procurement system.
My temporary workaround was this:
First: I used bcp to copy all the tables out of the database into individual files.
Second: Use standard Unix utilities to run these out to tape.
Third: Delete all the rows of each table in the database. This dropped the space used by the database to below 60 MB. Now I can DUMP the database to a DC600 tape and not lose any of the schemas or stored procedures.
Fourth: Use mkfile to create a large file on top the normal Unix filesystem to use as my database file.
Fifth: Recreate the database on this file, loading the dumped database and using bcp to bring the tables back in.
Sixth: For now I run the database on top of Unix (and really don't see much in the way of response degradation) and when it comes to backup time I shutdown SQL*Server and use normal Unix tools to backup the file.
Again thanks to all who helped me work this out!!!!!
-Dave
-------------------------------------------------------------------------------
Capt. Dave Grootwassink USAF Tactical Air Warfare Center INTERNET: GROOTWASS@TAWC1.EGLIN.AF.MIL (129.61.5.1) PHONE: (904)882-4100 AUTOVON 872-4100 (904)882-4600 872-4600 -------------------------------------------------------------------------------
This archive was generated by hypermail 2.1.2 : Fri Sep 28 2001 - 23:06:19 CDT