How to store and maintain unstructured data or Binary Large Object(blob) data

Problem: How to store and maintain unstructured data or Binary Large Object(blob) data inside SQL Server Data and what are different options that SQL Server provides.

Solution: Binary Large Object (Blob) Data

Every application has some sort of data stored in an unstructured format, where the data storage is files and documents in file systems. Most of the times the applications save the metadata information of this files in the database to keep a track of the location.

Benefits of storing blobs in RDBMS:

Blobs can be anything like an image, pdf, word document, spread sheet, other custom file formats.

·         Integrated storage and data management capabilities such as backup.

·         Searching data and metadata.

·         Ease of administration and policy management over the unstructured data.

Currently there are few options to store the data in SQL Server:

·         FILESTREAM: (Starting SQL Server 2008) FILESTREAM feature, which provides efficient storage, management and streaming of unstructured data stored as files on the file system. However, a FILESTREAM solution requires custom programming, and does not satisfy the requirement for full Windows application compatibility described above.

·         FileTables: (Starting SQL Server 2012) The Filletable feature builds on top of existing FILESTREAM capabilities to enable enterprise customers to store unstructured file data and directory hierarchies in a SQL Server database, by addressing the requirements for non-transactional access and Windows application compatibility for file-based data.

Comparing FILESTREAM vs FileTable:

Feature	File Server and Database Solution	FILESTREAM Solution	FileTable Solution
Single story for management tasks	No	Yes	Yes
Single set of services: search, reporting, querying, and so forth	No	Yes	Yes
Integrated security model	No	Yes	Yes
In-place updates of FILESTREAM data	Yes	No	Yes
File and directory hierarchy maintained in the database	No	No	Yes
Windows application compatibility	Yes	No	Yes
Relational access to file attributes	No	No	Yes
SQL Server Version Supported		2008	2012