Re: Image storage - db vs filesystem (again)
From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 03/24/04
- Next message: Sasavat: "How to query data ?"
- Previous message: Quentin Ran: "Re: This one could be tough"
- In reply to: Gary McPherson: "Image storage - db vs filesystem (again)"
- Next in thread: Gary McPherson: "Re: Image storage - db vs filesystem (again)"
- Reply: Gary McPherson: "Re: Image storage - db vs filesystem (again)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Mar 2004 00:10:53 -0500
> Using the filesystem, I would envisage an indexed sub-directory
> arrangement, perhaps of the form "D/I/DirectoryName" or something similar.
> Running two or more webservers, the local path could still be
> reconstructed and the appropiate file returned. I'm not certain how we
> could maintain synchronisation between the db and web servers in that
> arrangement, however.
Do you mean you are afraid that someone might delete or alter the data in
the table, and not make the same change to the filesystem, or vice-versa?
Remember that even if you store the image in the database, human error can
still lead to bad data. You fix this by developing a thorough management
code base and sensible security and checks, not by hiding the images in a
table, IMHO.
> Using the database, we would maintain the image files in their own tables
> and synchronisation would be a much simpler task.
Disk space requirements for the databases go way up, and if you don't plan
your filegroups well, you will reach a point where you will no longer be
able to back up your database. If you have a million images, even if
they're only 10kb each, that's a massive single backup task. What's the
largest backup you've ever attempted? And now how about the largest that
was successful? Have you ever tried to restore a 10TB database?
Whereas if you replicate the files across multiple (and CHEAP!) file
servers, you have backup of the actual file data via redundancy (and you can
very easily write tools to backup portions at a time if you would like to do
that also). The biggest issue will be finding an affordable file
replication solution that meets your needs and budget. We've tried a few
and are using services like inktomi and a couple others I forget the names
of. We spread it around based on $/GB and reliability, which of course is a
balancing act.
> One database server per web server,
You're still going to have a synchronization issue here. Filesystem
movement is cheap (and can be achieved with minimal impact on the
application and its users); keeping separate databases synchronized is a
much heavier task. Maybe you could use clustering, but I'm not clear about
your budget and in-house network / SQL Server expertise. It is certainly
not cheap to license, configure and maintain a SQL Server cluster, never
mind a decent-sized SAN. And most reliable synchronization methods are
either more cumbersome / kludgy, or more expensive, or both.
We have web farms and the images (hundreds of thousands) are all stored on
the filesystem. The files are replicated across multiple servers and the
data about each file is stored in the database. This has scaled quite well
to dozens of file servers, and one pair of databases in a cluster. We can
still perform backups with ease, we can add a file server to share the load
in a matter of hours (and I mean from the point of opening the server box
and first boot), and we sleep easy knowing we're not reaching any
theoretical, practical, perceived or real thresholds on the abilities of a
database (and we can always throw more disks at the file servers, since disk
space is cheap). Having millions or billions of images gives my arguments
more weight, IMHO, than less.
-- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/
- Next message: Sasavat: "How to query data ?"
- Previous message: Quentin Ran: "Re: This one could be tough"
- In reply to: Gary McPherson: "Image storage - db vs filesystem (again)"
- Next in thread: Gary McPherson: "Re: Image storage - db vs filesystem (again)"
- Reply: Gary McPherson: "Re: Image storage - db vs filesystem (again)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
