Four places to configure for access.Let's go through each option (A, B, C, D) one by one.
A# OS (level): SQL Server Configuration Manager.
This can be enabled during SQL install,or afterwards like this:
Open | SQL Server Configuration Manager | Properties for the SQL Server Service | Checkmark Enable FILESTREAM for Transact-SQL-access & Enable FILESTREAM for I/O access.
You may have to check here:
Open | Failover Cluster Manager | Select Roles | Select your instance | in the lower window Select the ‘Resources TAB’ | Select SQL Server FILESTREAM share (Instance name ) | Bring it online if needed.
Share name is defnined in SQL server configuration manager defaults to the instance name.
B# INSTANCE(level): Sp_configure.
The FILESTREAM feature of SQL Serve can also be enabled using the Transact SQL (TSQL).
EXEC sp_configure 'filestream access level', 2
There are 3 levels of FILESTREAM access which are supported in SQL Server 2008, 2008R2 and 2012 and they are mentioned below for your reference.
When the value specified is 0 then, FILESTREAM support for the instance is Disabled.
When the value specified is 1 then, FILESTREAM for Transact-SQL Access is Enabled.
When the value specified is 2 then, FILESTREAM for Transact-SQL and Windows streaming access is Enabled.
--You can use this statement to see current config value and run value.
EXEC sp_configure filestream_access_level;
C# DATABASE(level): properties.
DATABASE:Properties | Options | FILESTREAM
Enable Non-Transactional access and specify FileTable directory at the Database Level.FILESTREAM Directory Name is defined in Database Properties. Please see below.
This is the directory that store files used with the FileTable.
D# TABLE(level): Permissions to the Database & Table.
FileTables is a new feature introduced in SQL Server 2012. It's a unique table that reflects metadata of files in a specified folder.
The two primary benefits of FileTables are the ability to dynamically access file attributes via DML,and to perform FullText search on files in the specified folder.
The FileTable feature builds on FileStream and HiearchyID, which were introduced in SQL Server 2008.
Filestream Database | Tables | FileTables |
When scripting out a database for creation, this db option is set as:
ALTER DATABASE [DB NAME] SET FILESTREAM( NON_TRANSACTED_ACCESS = READ_ONLY, DIRECTORY_NAME = N'FILESTREAM DIR name' )
Please see below script #1 to Create filestreamtest database.
Please see below script # 2 to Create File Table.
As such, a DBA may be responsible for creating and testing a disaster recovery plan, and creating and supporting a high availability solutions. Before you create either, you have to know your RPO (Recovery Point Objective) and RTO (Recovery Time Objective).Since it all depends what options might be a fit for your database(s).Having said that ,Database mirroring does not support FILESTREAM. AlwaysOn availibility groups, Replication of data and Log shipping does support FILESTREAM.I hope this article will get you started quickly with FILESTREAM, and then help you master basic essential aspects of administering FILESTREAM-enabled databases.
I welcome your feedback on this article. Thanks for your time.