Storing BLOBs outside database
in [Using MS SQL Server]
|
From: Annonymous Coward on 26 Jun 2008 06:49 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)
From: Plamen Ratchev on 26 Jun 2008 08:52 Here is a good reading on the topic: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=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/library/bb933993(SQL.100).aspx http://msdn.microsoft.com/en-us/library/bb895234(SQL.100).aspx HTH, Plamen Ratchev http://www.SQLStudio.com
From: Sybaseguru on 26 Jun 2008 14:15 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.
From: Erland Sommarskog on 26 Jun 2008 18:01 Annonymous Coward (me(a)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, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Creating a database from set of TSQLl files Next: Concatenate in MSSQL |