Filestream is a great feature for databases that have to handle a large volume of BLOB data. It allows the DBA to store data in Filesystem folders rather than data pages within SQL Server. Since the filesystem is meant to store and work with such data it makes it a perfect candidate for this feature. Previously we would store Blob data into varbinary max columns but with Filestream and File Table features we can integrate unstructured data into SQL Server very easily as shown in this video.
In this video we show the database options as well as how to setup and configure FileStream and load data into Filestream tables. The script used in the video can be found here. Even though the data is stored inside the file system they still follow database principles such as transaction level isolation and consistency. Database backups will take into account the data stored into file tables and features like full text search are compatible out of the box. The table captures additional metadata such as file paths, extensions, size, modified dates etc.
If you have unstructured data in your databases you should be considering Filestream and File tables or else you are not doing your due diligence. When migrating to the cloud and using Azure SQL PaaS we often replace Filestream with an even better option of saving the files in Azure Blob Storage.
sp_configure 'filestream access level', 2
reconfigure with override
go
CREATE DATABASE MyFileTable
WITH FILESTREAM (
NON_TRANSACTED_ACCESS = FULL
,DIRECTORY_NAME = N'FileTableDir'
)
/* Add a FileGroup that can be used for FILESTREAM */
Go
ALTER DATABASE MyFileTable ADD FILEGROUP FileTableFG CONTAINS FILESTREAM
GO
/* Add the folder that needs to be used for the FILESTREAM filegroup. */
ALTER DATABASE MyFileTable ADD FILE (
NAME = 'FileTable_File'
,FILENAME = 'C:\Work\FileTable'
) TO FILEGROUP FileTableFG;
GO
use MyFileTable
/* Create a FileTable */
CREATE TABLE resumes
AS FILETABLE WITH (FILETABLE_DIRECTORY = 'FileTableDir')Please Consider Subscribing
