Technical Article

Filestream and FileTable Implementation SQL Server 2012.

,

Two of the most important responsibilities for any DBA are protecting the data in a database and keeping that data available. I was responsible for our first SQL Server 2012 filestream production implementation for one of our high profile client implementation project. In this article, I would like to demonstrate about Filestream implementation in SQL Server 2012.

FILESTREAM was introduced by Microsoft in SQL Server 2008 for the storage and management of unstructured data.Storing and managing unstructured data was tricky prior to the release of SQL Server 2008.There were two approaches to storing unstructured data in SQL Server. One approach suggests storing the data in a VARBINARY or IMAGE column. This ensures transactional consistency and reduces management complexities, but is bad for performance. The other approach is to store the unstructured data as disk files and store the location of the file in the table along with the other structured data linked to it. This approach was found to be good in terms of performance, but does not ensure transactional consistency. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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).
USE master
GO
EXEC sp_configure 'filestream access level',   2
Go
RECONFIGURE
GO
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;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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. 

/****** # 1 Create filestreamtest database script Object:  Database [Filestreamtest]   ******/
USE [master]
GO

CREATE DATABASE [Filestreamtest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Filestreamtest', FILENAME = N'D:\SQLData\User\Filestreamtest.mdf' , SIZE = 209920KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB ), 
 FILEGROUP [DefaultFS] CONTAINS FILESTREAM  DEFAULT
( NAME = N'DocStore', FILENAME = N'D:\SQLData\User\DocStore' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'Filestreamtest_log', FILENAME = N'D:\SQLLog\User\Filestreamtest_log.ldf' , SIZE = 14528KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )
GO

ALTER DATABASE [Filestreamtest] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Filestreamtest].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO

ALTER DATABASE [Filestreamtest] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [Filestreamtest] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [Filestreamtest] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [Filestreamtest] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [Filestreamtest] SET ARITHABORT OFF 
GO

ALTER DATABASE [Filestreamtest] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [Filestreamtest] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [Filestreamtest] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [Filestreamtest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [Filestreamtest] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [Filestreamtest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [Filestreamtest] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [Filestreamtest] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [Filestreamtest] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [Filestreamtest] SET  DISABLE_BROKER 
GO

ALTER DATABASE [Filestreamtest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [Filestreamtest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [Filestreamtest] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [Filestreamtest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [Filestreamtest] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [Filestreamtest] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [Filestreamtest] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [Filestreamtest] SET RECOVERY FULL 
GO

ALTER DATABASE [Filestreamtest] SET  MULTI_USER 
GO

ALTER DATABASE [Filestreamtest] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [Filestreamtest] SET DB_CHAINING OFF 
GO

ALTER DATABASE [Filestreamtest] SET FILESTREAM( NON_TRANSACTED_ACCESS = READ_ONLY, DIRECTORY_NAME = N'FilestreamtestFileStreamDirectory' ) 
GO

ALTER DATABASE [Filestreamtest] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO

ALTER DATABASE [Filestreamtest] SET  READ_WRITE 
GO




/******  # 2 File Table creation script Object: Table [dbo].[DStore]  ******/


USE [Filestreamtest]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ARITHABORT ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DStore] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [DefaultFS]
WITH
(
FILETABLE_DIRECTORY = N'DTable', FILETABLE_COLLATE_FILENAME = SQL_Latin1_General_CP1_CI_AS
)

GO

SET ANSI_PADDING OFF
GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.67 (9)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.67 (9)

You rated this post out of 5. Change rating

Related content

External Article

An Introduction to SQL Server FileStream

Filestream allows us to store and manage unstructured data in SQL Server more easily. Initially, the accounts of FILESTREAM assumed prodigious powers of concentration and cognition, and we mortals all recoiled numbly. However, it became clear that we were missing out on some extraordinarily useful functionality, so we asked Jacob Sebastian to come up with a simple and clear-cut account of the FILESTREAM feature in SQL Server 2008. You'll agree he has managed the feat superbly.

2009-08-06

3,929 reads