By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,527 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Storing BLOBs outside database

P: n/a
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)
Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Jun 27 '08 #2

P: n/a
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.
Jun 27 '08 #3

P: n/a
Annonymous Coward (me@home.com) 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
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.