SQL Server: How to store binary data (e.g. Word file)?

Question

I'm not a DBA, so I just don't know the upsides and downsides of using various methods of storing binary data in an SQL Server.

• varbinary(n) stores only 8000 bytes
• varbinary(max) stores 2 GB
• BLOBs are meant for big files, but come with "administrative" overhead (the files being only referenced in the DB, but actually sitting on the local harddisk)

I basically have about 1000 documents (per year) which I want to store in a SQL Server 2008 R2 database (being filled with a simple ASPX form upload, being viewed by a simple Gridview with download). All documents will probably be around 2MB - 8MB (Word, Excel files). My guess is that I should be using a table MyDocuments with the following layout:

MyDocuments 
    Data       varbinary(max)
    Title      varchar(255)
    ModifiedOn datetime()
    ModifiedBy varchar(100)

Am I on track with varbinary(max)? Or am I going in the wrong direction (e.g. Performance-wise)?

Answer 1

There is 'FILESTREAM' storage in SQL Server 2008. It allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

You can check these out:

FILESTREAM Storage in SQL Server 2008
Saving and Retrieving File Using FileStream SQL Server 2008

EDIT:
Objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem.

Performance: Varbinary vs FILESTREAM

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-FILESTREAM-performance.aspx
To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem
SQL Server 2008 FILESTREAM performance