SQL Server World

Everything you need to know about FileStream feature of SQL Server 2012

FileStream directory

The file stream network path technically is this \\SQL Server name\Network Share Name\Database File stream Directory Name\<File Table Name>

1. SQL Server Name – (starting with \\)
2. Windows Share Name – It is visible in SQL Server Configuration tool -> SQL Server properties -> FileStream tab.
3. Database File stream Directory Name – It can be seen in Database Properties -> Options -> FILESTREAM Directory Name
4. The last part is the file table name.

Some helpful FileStream queries

Exploration Queries:

SELECT DB_NAME(database_id) AS DatabaseName,non_transacted_access_desc, directory_name  FROM sys.database_filestream_options WHERE directory_name IS NOT NULL

SELECT directory_name,is_enabled,filename_collation_name FROM sys.filetables;

SELECT * FROM sys.tables WHERE is_filetable = 1

SELECT object_id, OBJECT_NAME(object_id) AS ‘Object Names’    FROM sys.filetable_system_defined_objects

— To get the root level UNC path of a file table

SELECT FileTableRootPath()

SELECT FileTableRootPath(N’file_table_name’)

Enable Disable Queries:

https://msdn.microsoft.com/en-us/library/gg492061.aspx#HowToDisable

— Disable Non-Transactional write access.

ALTER DATABASE database_name

SET FILESTREAM ( NON_TRANSACTED_ACCESS = READ_ONLY );

— Disable non-transactional access.

ALTER DATABASE database_name     SET FILESTREAM ( NON_TRANSACTED_ACCESS = OFF );

— Enable full non-transactional Access

ALTER DATABASE database_name     SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );

Find locks held by filestream queries:

Open handles to the files stored in a FileTable can prevent the exclusive access that is required for certain administrative tasks. To enable urgent tasks, you may have to kill open file handles associated with one or more FileTables.

SELECT * FROM sys.dm_filestream_non_transacted_handles;

— To identify open files and the associated locks

SELECT opened_file_name    FROM sys.dm_filestream_non_transacted_handles

WHERE fcb_id IN        ( SELECT request_owner_id FROM sys.dm_tran_locks );

— Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles;

— Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @table_name = ‘filetable_name’;

— Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @handle_id = integer_handle_id;

Code Re-usability:

— Use the relative path for portable code

https://msdn.microsoft.com/en-us/library/gg492087(v=sql.110).aspx

DECLARE @root nvarchar(100);

DECLARE @fullpath nvarchar(1000);

SELECT @root = FileTableRootPath();

SELECT @fullpath = @root + file_stream.GetFileNamespacePath() FROM EDMS

WHERE name = N’document_name’;

PRINT @fullpath;

How FileStream works with some of the High Availability Features of SQL Server 2012?

 AlwaysOn Availability Groups and FileTables

https://msdn.microsoft.com/en-us/library/gg492086.aspx#alwayson

When the database that contains FILESTREAM or FileTable data belongs to an AlwaysOn availability group:

• FileTable functionality is partially supported by AlwaysOn Availability Groups. After a failover, FileTable data is accessible on the primary replica, but FileTable data is not accessible on readable secondary replicas.
• The FILESTREAM and FileTable functions accept or return virtual network names (VNNs) instead of computer names.
• All access to FILESTREAM or FileTable data through the file system APIs should use VNNs instead of computer names.

Replication and FileTables

Replication and related features (including transactional replication, merge replication, change data capture, and change tracking) are not supported with FileTables.