Re: Controlling access to pdf/doc files (db "better" than filesystem?)

["David Wall @ Yozons, Inc." <dwall () yozons com>]

> Why is storing the data in the BLOB a good solution? From what I can see
> the only advantage of this is that it takes it off the file system. How
> is this any different from moving the files to a location in the file
> system that is not accessible from online? A little obscure, not
> necessarily any more secure.

One key advantage that a db has over a filesystem is that you have full
transactional support.  Any meta data stored about the file is stored with
the file so insert/delete/update work together with transactional integrity,
especially since that file being stored may also be stored with other
records related to the file.

Another advantage is that when doing replication or backing up a database,
the data and the file are backed up together.  If you use a filesystem, then
you need to  backup the db and the filesystem and hope that the two backups
create a consistent view if they were restored later.

Lastly, with db triggers/constraints, you are less likely to have a
filesystem file be removed without also removing the associated records
about that file.  That wouldn't be the case with a filesystem that only has
filesystem-type security constraints compared to the more powerful
mechanisms inside a db.

> In SQL Server (can't comment on other platforms) BLOBS perform poorly
> and a real PITA to extract and work with, so why store files in the db?

I think it would be interesting to see whether performance benefits of being
in the db are real or not, to see if doing the query, opening and streaming
the BLOB data from a db is any worse/better than doing a query, getting a
file name, opening the file via the filesystem and then reading and
streaming the data back.  My impression is that the db would perform better.

> that  in SQL Server is that all data in SQL Server is split over ~8k
> pages. When you add a BLOB it needs to be split into 8k chunks. When you

But filesystems also store data into pages, often much smaller than 8k
chunk.

Our Signed & Secured application stores all files as BLOBs in a database for
all of transactional and backup capabilities, but we've never run tests of
100+ concurrent web users downloading files to see if the database or the
filesystem would be faster.  In general, faster was less important to us
being able to support lots of concurrent requests because the speed of
retrieval from the db was always assumed to be faster than it could be
streamed back across typically slower Internet links.  After all, the data
has to be sent back to a user's web browser, so the speed of the transfer is
limited by the slowest link between the browser and the web server.

David