Storing BLOBs outside database

[Annonymous Coward]

I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).

Does anyone have any experience doing this ? I have a few questions:

1).what are the things to watch out for (apart from obvious ones like
'file not found' type errors).
2). How may a stored proc be written to fetch the BLOB data ? (An
example would be very helpful)
3). How are errors handled in the stored proc that fetches the BLOB
data? (again an example would be very helpful)

[Plamen Ratchev]

Here is a good reading on the topic:
http://research.microsoft.com/resear...MSR-TR-2006-45

The biggest problem with storing BLOB in the file system (in the current SQL
Server versions) is to guarantee the consistency of the data. For example,
if you have to restore your database to a point in time, how to restore all
files to that same time mark. And with that approach you store in the
database the path to the BLOB file. Then your client applicaiton code can
read the location of the BLOB and open it via the client functions to handle
BLOB data.

SQL Server 2008 will have the new FILESTREAM data type which provides
storing BLOB data to the file system while maintaining transactional
consistency.
http://msdn.microsoft.com/en-us/libr...(SQL.100).aspx
http://msdn.microsoft.com/en-us/libr...(SQL.100).aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Sybaseguru]

Annonymous Coward wrote:

I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).

The way that people (ie document management systems) have done this in the
past is a)used image data types or b) simply held a URL which the client
picks up and processes either from a shared directory or a "lightweight web
server" (google for details)

Current Sql databases are not very good at processing large blobs
efficiently (despite the marketing b******t) as any processing - even just
a passthru mode costs performance on big files.

[Erland Sommarskog]

Annonymous Coward ([e-mail]) writes:

I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).

Well, that you mean that you store the file name.

Does anyone have any experience doing this ? I have a few questions:

1).what are the things to watch out for (apart from obvious ones like
'file not found' type errors).

As Plamen said, SQL 2008 has FILESTREAM, but on SQL 2005, the big
problem is to get transactional consistency. And backups right.

2). How may a stored proc be written to fetch the BLOB data ? (An
example would be very helpful)

If the images are file, you don't fetch them from stored procedures,
but you read them directly from the file system.

On SQL 2008 with FILESTREAM, you can read the blobs from stored procedure,
but the more performant way is return a filehandle to the client
that can read the blob directly outside SQL Server.

3). How are errors handled in the stored proc that fetches the BLOB
data? (again an example would be very helpful)

What would be different here from reading a normal column?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx