Is it ever a good idea to store BLOBs in a database?

Question

For a while I've been working on inserting the data from a bloated Excel workbook into a SQL database. My team has suggested keeping a backup of the .xls, and they are wondering if it might be a good idea to put the Excel workbook actually inside the database so that all the relevant data from our project was together.

I know some databases support the insertion of binary files, but clearly this was the not the original intended use of DB systems, and so I'm thinking that this is a bad idea. My vote would be to put URLs to the workbooks in the database and keep the workbooks in a secure location on our intranet.

What are the pros (if any, besides keeping both the production data and the backup in the same place) and cons of putting BLOBs in a database?

Edit: Just to clarify a little further, though I give a specific scenario, I intend this question to primarily be about BLOBs in general. But whatever I can glean for your responses that will assist me in my specific situation is, of course, appreciated. :)

Thanks,
-Robert

P.S. For those of you that didn't know, BLOB is a backcronym for "Binary Large Object"

Answer 1

Many CMS packages (such as Sharepoint) store it all as BLOBs anyway.

A quick summary. I have no personal opinions because I've never had to design or develop very large BLOBs (such as spreadsheets) in a DB. I've stored images and other smaller stuff.

In database:

Pros: Assuming other data as well as BLOBs:

• it's all in one place.
• backup/restores are coherent

Cons:

• Database bloat
• Performance

Out of database

Pros:

• No database bloat
• Stored natively

Cons:

• Backup/restore more complicated
• Risk of broken links

Answer 2

Generally I avoid putting blobs in the database if there are other storage options that cost less. Sure, there are valid reasons for storing blobs in the database, but I tend to err on the side of caution and use the FS for file storage, and databases for data structures.

Since you're using SQL 2008, you might also be interested in the FileStream data type. It could make things a lot easier on you.

Answer 3

It seems to me that opening the Excel spreadsheet if need be becomes more difficult if it is a blob. On the other hand, you don't want the backup spreadsheet being changed without the data changing, so in your case, this could be an advantage. You might also consider how many revisions of the spreadsheet you want to store. We often get asked to look back at the raw input data from a client that goes back a year or more (really fun when you get a daily file) to find the source of an issue (or at least to prove it was them and not us!). If you need to look at different versions of the data, your blob would need to be in a related table. That is porbably an adavantage in your case too, as it seems to me, you would only rarely need to actually access this data.