Archive for the ‘File Table’ Category

Enabling FileTable Prerequisite

December 19, 2014 Leave a comment

The FileTable feature builds on top of SQL Server FILESTREAM technology

Enabling the Prerequisites for FileTable

To enable the prerequisites for creating and using FileTables, enable the following items:

  • At the instance level:

    • Enable FILESTREAM at the Instance Level

      • Enable using PowerShell

        # Enable FILESTREAM CLS $instance = "MSSQLSERVER" $wmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement11"-Class FilestreamSettings | where {$_.InstanceName -eq $instance} $wmi.EnableFilestream(3, $instance)

        • Restart SQL Server Services
      • Enable using Configuration Manager

        FILESTREAM Enabled

      • Enable using Registry Key

        By default [EnableLevl] is set to 0, change it to 3 and restart the SQL Server Services.SQL Server Services will create the Share Name by itself.


        Connect to SQL Server instance from SQL Server Management Studio and Enable FILESTREAM for file I/O streaming access



        sp_configure ‘filestream access level’,2

        reconfigure with override

  • At the database level:

Administrative Considerations

About FILESTREAM and FileTables

  • You configure FileTables separately from FILESTREAM. Therefore you can continue to use the FILESTREAM feature without enabling non-transactional access or creating FileTables.

  • There is no non-transactional access to FILESTREAM data except through FileTables. Therefore, when you enable non-transactional access, the behavior of existing FILESTREAM columns and applications is not affected.

About FileTables and non-transactional access

  • You can enable or disable non-transactional access at the database level.

  • You can configure or fine-tune non-transactional access at the database level by turning it off, or by enabling read only or full read/write access.

Categories: File Table

FileTable with AlwaysOn AGs – Bug

December 4, 2014 Leave a comment

After a long while of testing and troubleshooting we identified two bugs in SQL Server that can hit you if you run FileTables in conjunction with AlwaysOn Availability Groups. One concerns the way Checkpoints are done in FileStream, which could lead to very long failover times, the other one concerns a handle issue that could lead to the system running out of sync without noticing.

Right now there is no feasible workaround to both problems, but Microsoft has already confirmed that both issues will be fixed in Service Pack 1.

If you should encounter this problem and need help working around it please ping me and I will share the necessary details.

Categories: File Table