Design - Java + SQL Server - Discussion about file based vs database storage for file upload

Background:
Below investigation done considering java application with SQL Server as back-end. Java - 1.6 and SQL Server - 2008 r2. 

1. Comparison table (Source-01, Source-04)

Comparison point	Storage solution
	File server / file system	SQL Server (using varbinary(max))	FILESTREAM
Maximum BLOB size	NTFS volume size	2 GB – 1 bytes	NTFS volume size
File Size recommendation	>1MB	<256kb o:p="">

>1MB

Note: File size between >256KB and <1mb b="" is="" need="" project="" subject="" to="">Source-01

)

Streaming performance of large BLOBs

Excellent

Poor

Excellent

Security

Manual ACLs

Integrated

Integrated + automatic ACLs

Cost per GB

Low

High

Low

Manageability

Difficult

Integrated

Integrated

Integration with structured data

Difficult

Data-level consistency

Data-level consistency

Application development and deployment

More complex

More simple

More simple

Recovery from data fragmentation

Excellent

Poor

Excellent

Performance of frequent small updates

Excellent

Moderate

Poor

Data Encryption
(Source-04)

Manual

Possible

Manual

DR

Manual Data Replication

SQL Server Data Replication (log shipping, Data  Mirroring, Always ON)

SQL Server Data Replication (log shipping, Always ON)

Backup and Restore

Manual (3^rd party tools or OS command can be used)

Part of database backup and restore

Part of database backup and restore

Single point failure/ High availability

Redundant File Servers plus SAN implementation provides no single point failure

SQL Server Clustering plus SAN provides no single point failure

SQL Server Clustering plus SAN provides no single point failure. FileStream should also be on SAN.

TDE (Transparent Data Encryption)

NA

Possible

Not Supported

Database Mirroring

NA

Possible

Not Supported

SNAPSHOT Isolation

NA

Possible

Partially Supported (explanation below)

Authentication

Only Windows Authentication

Supports both SQL Server Authentication and Windows Authentication

Does not support SQL Server Authentication. Only Windows Authentication

2. If we use VARBINARY without filestream enabled, research done by Microsoft indicates – file size <256kb better.="" database="" file="" is="" size="" storage="">1MB file system is better (can be regular file system (NTFS) or FILESTREAM (SQL SERVER). (Source-02)

3. FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

(Source-03)

4. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data. (Source-03)

5. Limitation of FileStream of MS SQL Server

1. TDE (Transparent Data Encryption) is not possible
2. Database mirroring is not supported for FileStream
3. SNAPSHOT isolation is not fully supported. If a FILESTREAM filegroup is included in a CREATE DATABASE ON clause, the statement will fail and an error will be raised.
4. When you are using FILESTREAM, you can create database snapshots of standard (non-FILESTREAM) filegroups. The FILESTREAM filegroups are marked as offline for those database snapshots
5. For failover clustering, FILESTREAM filegroups must be put on a shared disk. FILESTREAM must be enabled on each node in the cluster that will host the FILESTREAM instance
6. It’s highly recommended to have separate file group in database to handle FileStream
7. FileStream supports Windows authentication only 

Sources:

1. http://msdn.microsoft.com/library/hh461480
2. http://research.microsoft.com/apps/pubs/default.aspx?id=64525
3. http://msdn.microsoft.com/en-us/library/gg471497.aspx
4. http://msdn.microsoft.com/en-us/magazine/dd695918.aspx
5. http://technet.microsoft.com/en-us/library/bb895334.aspx