When you are responsible for databases on a project (as an architect, administrator or developer), you sometimes have to store documents (Files) which are linked to structured data. These data often have to be accessible outside SQL Server (Windows API’s). Storing BLOBs in the database, especially the ones over 1MB, can consume large amounts of file space and expensive server resources. Within SQL Server and Azure, there are different solutions to solve these kind of issues.
The most basic way, and also the only way possible on PRE-2012 SQLServer versions, is to create a table (e.g. ‘Documents’) with a path column that contains the complete file path and name.
SQL Server Remote BLOB Store (RBS) is an optional add-on component that lets database administrators store binary large objects in commodity storage solutions, instead of directly on the main database server. This way of BLOB storage is also used by SharePoint. Aside to the Native SQL BLOB Provider (Default) since SQL Server 2005, there is also a Filestream BLOB Provider since SQL Server 2008R2. This is the only Remote BLOB provider that ships with SQL Server 2008 and it allows you to store BLOB data on any local hard drive on the SQL Server. With a third party provider Store (Storage Point), it gives you the ability to use FileStream towards the Cloud.
Note: RBS can be downloaded from the SQL Server 2008 R2 Feature Pack page. RBS is not included on the SQL Server 2008 R2 installation media, and is not installed by the SQL Server 2008 R2 Setup program.
Architectural design with RBS and Storage Point:
Since SQL Server 2012, Microsoft has prvided a table of files (also referred to as a FileTable) for applications that require file and directory storage in the database. A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes. This is, in my opinion, the biggest advantage. The files as such are available by the normal Windows File and Directory management, but you can keep track of it in SQL Server, including a hierarchical name space of directories, files and file attributes. Every row in a File Table represents a file or directory.
Note: FileTables do not support Memory-Mapped files (e.g. Notepad, Paint files).
With SQLServer Filetables, you can perform 3 actions: create, alter and drop
EXEC sp_configure filestream_access_level, 2 RECONFIGURE GO
0 = Disables FILESTREAM support for this instance.
1 = Enables FILESTREAM for Transact-SQL access.
2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access.
CREATE DATABASE Documents ON PRIMARY (Name = Documents, FILENAME = 'D:\FileData\Documents.mdf'), FILEGROUP FTFG CONTAINS FILESTREAM (NAME = FileTableFS, FILENAME='D:\FileData\FS') LOG ON (Name = DocumentsLog, FILENAME = 'D:\FileData\ DocumentsLog.ldf') WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'Documents'); GO
Note: Can also been done with an ALTER DATABASE command on an existing DB
Since Filestream is currently (current release of SQL 2016) not supported by Azure, you need to go to:
In this case we’re back at the basics and store: