Recently I re-visited the blobs in/blobs out argument with a colleague. You know the one, one of you says blobs shouldn't be stored in database (principally because the last time he tried it 'blobs in' in VB 6 access to the blob data was a pain in the arse), then the other one says no they should be in the database (because the last time they tried it 'blobs out' all the files got mixed up / out of sync / weren't backed up). Etc...
Anyway, not only has Paul Randal posted a good summary of the pros and cons, but he did so as an intro to a new SQL 2008 data type 'FileStream' that attempts to bridge the two approaches (the 'have your cake and eat it' approach).
I'm cautious. Transactions at the filesystem level are a real mess (as some of the OneNote blogs make clear, especially with non-MS implementations of SMB like SAMBA). Your database backup is presumably still huge and unwieldy (or missing the blob data, which is worse?).
The main advantage of this approach seems to be that SQL can access the blob data faster through NTFS than via it's own internal MDF formats. But you've apparently still got to go via SQL to get the data, you can't (for example) just serve up images-stored-as-blobs directly via IIS. Or maybe I've missed something. Either way, the upside all seems to be focused on blob streaming performance, which may or may not be the most relevant factor for your app.
So it's possible that next year's arguments will be blobs in vs blobs out vs filestream, and still no one-size-fits all. Ah well.
Subscribe to:
Post Comments (Atom)
Posts
1 comment:
You could compress the blobs on the way in (and decompress on teh way out): http://mitch-wheat.blogspot.com/2007/05/save-and-restore-filesimages-to-sql.html
Post a Comment