BLOBs in the Database? Think again.

In response to a not-that-uncommon usergroup question:

Ah, I'm not a fan of putting BLOBs in the database--despite that it works better now-a-days. Adding BLOBs can easily fill up one of the smaller footprint DBMS systems like MSDE (2gb) and SQL Express (which can hold 4gb). There are a litany of other issues as well:

• Performance. While fetch and retrieval performance has improved, it still takes longer to fetch a BLOB from the database and save it back than it does from a file (even on somewhere on a remote server).
• Code issues: While it's easier than ever to fetch and save BLOBs there are still issues when writing code to deal with these special columns. There are special SQL requirements, DataReader settings and more.
• Cache issues: When you fetch a BLOB, relevant data in the cache can be flushed along with procedures that need to be pushed aside to make space.
• Backup/Restore issues: When you save BLOBs to the database, unless you partition off the segments of the database your backup includes the BLOB data. That might not be a bad idea unless the BLOB data is RO. In this case you needlessly store data that does not need to be backed up past burning the file to a DVD.
• Object use issues: When you fetch a BLOB into a VarBinary or whatever object, you don't have it in a form that Word or any word processor can process. If it's a picture, you don't have it in a form that SnagIt Editor (very cool) can use. Sure, you can bind to a control, but invariably you probably have to write the value to a file first and then point another application at the file.  If the file is RW, once the file is on the client system you have to make sure it's re-persisted to the database somehow. Yes, SharePoint uses SQL Server to store the data in SQL Server. Question: do you plan to replicate this functionality? It's taken them years to get to this point.
• Distribution. When you want to pass updated BLOBs to the client, you must upload the data to the database. In many cases customers have found it far easier to simply distribute the data via removable RO media (a CD or DVD). Some folks taking this approach have encrypted or otherwise protected the data files by adding security headers to the files so common image viewers won’t work.

As far as using multiple databases, I think you're making a lot of work for yourself and painting yourself into a corner. Yes, you can link servers and databases, but the mechanisms are not well understood or widely used. What you're describing is trying to solve the problem of delivering coal with a bicycle by using more bicycles.

I would address the issue of the file format instead of putting the data in a database. I suggest that you can write a security layer to encrypt the files, wrap them in a security blanket that would have to be unlocked with your application--this could be done far easier than by trying to wedge them into a limited capacity SQL Server database.