Setting up FILESTREAM and FileTable in SQL Server

by Posted in FileTable, SQL Tips Tagged , , , , , 4 Comments

In my previous blog post I wrote about what FILESTREAM and FileTables are. In this post I explain how to set FILESTREAM and FileTables up.

To enable and change FILESTREAM settings

  1. On the Startmenu, select All Programs, select the SQL Server folder, select Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Managersnap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance, and then click Properties.
  5. In the SQL Server Propertiesdialog box, click the FILESTREAM
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply.
  10. In SQL Server Management Studio, click New Queryto display the Query Editor.
  11. In Query Editor, enter the following T-SQL code:
EXEC sp_configure filestream_access_level, 2

RECONFIGURE
  1. Click Execute.
  2. Restart the SQL Server service.

Next we need to add a File Group to the DB with FILESTREAM and then add a File into the File Group.

USE MASTER
GO

ALTER DATABASE ImageFilesDB
ADD FILEGROUP ImageFG1 CONTAINS FILESTREAM;
GO

ALTER DATABASE ImageFilesDB 
ADD FILE 
(
    NAME = ImageStorage,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ImageStorageFG1.ndf'
)
TO FILEGROUP ImageFG1;
GO

Once you have created the File Group and add a file you will need to set the FILESTREAM File Group as the default in the database properties. (Right-click the database, select Properties, select Filegroups in the left hand pane, check the default checkbox in the FILESTREAM section)
FileGroupProperties
Next you need to check whether non-transaction access is enabled on the database.

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options;
GO

If it is not enabled on the database we need to enable it

ALTER DATABASE ImageFilesDB
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'ImageTable')
Once non-transactional access is enabled on the database we can create a FileTable

CREATE TABLE ImageStore AS FILETABLE 
    WITH ( 
          FileTable_Directory = 'ImageTable',
          FileTable_Collate_Filename = database_default
         );
GO

Now that everything is set up the last thing to do is to add files to our FileTable. There are two methods of doing this, either through T-SQL code or by exploring the FileTable from SQL Management Studio.

T-SQL method:

DECLARE @img AS VARBINARY(MAX)
SELECT @img = CAST(BulkColumn AS VARBINARY(MAX)) 
FROM OPENROWSET(BULK 'C:\temp\testimage.jpg', SINGLE_BLOB ) AS x
 
INSERT INTO dbo.ImageStore(name, file_stream)
SELECT 'testimage.jpg', @img
 
SELECT * FROM dbo.ImageStore

queryresults
Management Studio Method:

  1. Expand the FileTables node under Tables in your database
  2. Right-Click the FileTable you created
  3. Select Explore FileTable Directory

explorefiletable
A shared network location will open up where you can paste images/files into.
filesystemfilelocation

FileStream and FileTable in SQL Server

by Posted in FileTable, SQL Tips Tagged , , , , Leave a comment

FileStream and FileTable are used to store unstructured data in the database. You can store BLOBs such as Word documents, PDFs and images.

FileStream
FileStream was introduced in SQL Server 2008 as a way to store BLOBS in a table on a relational database. FilesStream is not a datatype and is enabled as an attribute on a VARBINARY(MAX) column. FileStream allows us to manage our unstructured data and stores them as files on the file system.

The FileStream column is not cached in the SQL Server buffer cache and is cached in the Windows NT system cache. This means that the SQL Server buffer pool is not used by FileStream data, therefore the memory available for query processing is not reduced.

FileStream allows transaction consistency between the other data stored in the database. You are able to backup and restore the data stored in the file groups which contain FileStream data. The full-text search functionality on FileStream columns works the same way as it would on a VARBINARY(MAX) column.

When storing a file/image on a FileStream enabled column, only a pointer to the file is stored in the table and the actual file is stored in the Windows NTFS.

FileTable
FileTable was introduced in SQL Server 2012 and builds on top of existing FileStream capabilities. FileTable allow you to store directory hierarchies in a SQL Server database, and addresses the requirements for non-transactional access and Windows application compatibility for file-based data. FileTable is a specialized table with a fixed schema. FileTable can also be queried and updated through normal T-SQL commands.

For non-transactional file access for Windows based API applications the file and directory data in a FileTable is exposed through a Windows shared location. This looks like a normal network share location with its files and directories to the Windows application.

Compare FileStream and FileTable

Feature FileStream Solution FileTable Solution
Single story for management tasks Yes Yes
Single set of services: search, reporting, querying, etc Yes Yes
Integrated security model Yes Yes
In-place updates of FILESTREAM data No Yes
File and directory hierarchy maintained in the database No Yes
Windows application compatibility No Yes
Relational access to file attributes No Yes

Table reference: https://msdn.microsoft.com/en-US/library/hh403405.aspx

Pros for using FileTable:

  • Windows API compatibility for file data stored within a SQL Server database. This includes the following:
    • Non-transactional streaming access and in-place updates to FILESTREAM data.
    • A hierarchical namespace of directories and files.
    • Storage of file attributes, such as created date and modified date.
    • Support for Windows file and directory management APIs.
  • Integrated storage and data management capabilities such as backup.
  • Integrated services such as full-text search and semantic search over data and metadata.
  • Ease of administration and policy management over the unstructured data.
  • Ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table
  • Allows SQL to take advantage of the NTFS I/O streaming capabilities
  • Buffer pool is not used therefore the memory available for query processing is not reduced

Cons for using FileTable:
FileTables do not support memory-mapped files such as NotePad and Paint. You are not able to use these applications on the same server as SQL Server to open the files stored in a FileTable, however, you can use these applications from a remote computer to open files that are stored in a FileTable.

NOTE:
In order to use FileStream or FileTable you will need to enable FileStream on the instance of SQL Server, then enable it on the database. I shall go into more details on setting up FileStream and FileTable in my next blog post.