Filestream

FileStream - a data warehouse that allows you to connect MS SQL Server and the file system. Allows you to store data on an NTFS disk and manage it using a database engine. Data is provided with streaming access. FileStream first appeared in MS SQL 2008, available in all editions, including Express.

To work with FileStream storage, you must enable it at the server level, create a special file group and include a file in it, specifying the desired directory instead of the file name.

After that, a header file, a directory for storing the log, and directories for storing data will appear in the specified directory. The latter will have names of the form GUID .

Content

FileStream and MSSQL Server

MS SQL Server treats warehouse data as varbinary (max). Access to data is provided in the context of a transaction, starting with SQL Server 2008 and outside the transaction (starting with SQL Server 2012). Data is cached , not encrypted. Certain restrictions apply to the filegroup containing Filestream. ^[1] Data can be inserted ^[2] , deleted, or modified using standard T-SQL queries or using an external API . ^[3] The filegroup containing Filestream does not support snapshots. Replication and full-text search are supported. ^[4] . Backed up data stored in Filestream is provided. ^[five]

FileStream and File System

The data stored in FileStream is accessible for viewing and editing by external programs, for example, an image viewer or a text editor. However, the file names do not have a meaningful name, and it is impossible, using system procedures or database views, to determine the name of the file corresponding to the data array.

example of naming files containing FileStream data

This flaw was fixed in SQL Server 2012 with the introduction of a special type of FileTable table. Starting in SQL 2012, data became available outside the context of a transaction on a virtual UNC path. FileStream does not fall under data size limits (2 GB) and database size (4 GB) existing in SQL Express version.

Notes

Links

[1] FILESTREAM Overview

[2] Appending Data Using SQL 2008 Filestream - Microsoft SQLCAT ISV Program Management Team - Site Home - MSDN Blogs

[3] Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008

[4] ttp://msdn.microsoft.com/en-us/library/bb895334%28v=sql.105%29.aspx 2

[5] How to Backup and Restore a SQL Server FILESTREAM Enabled Database