Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012 – FileTables – Enable / Create / Alter’

SQL Server 2012 – FileTables – Prerequisites- Enable / Create / Alter

Posted in DML, SQL SERVER, tagged , , , on February 3, 2013| 9 Comments »

File table is one of the excellent features introduced in SQL Server 2012. This feature provides the compatibility between windows application and file data stored in SQL Server and gives the support of windows file names space (Windows API) as well. In simple words you can say, from File table you can handle windows files and directories via SQL server 2012. This nice feature is a build on top of file stream technology. In other words, you must enable file stream to use File Table.

In this article, we will learn how to enable File Stream and create File Table in sql server.

Let’s do it step by step to view how it works.

Step 1: Enable File Stream in SQL server 2012:
There are 2 ways to enable file stream in SQL server 2012.

  1. Via T-SQL
  2. Via Configuration Manager

1- Via T-SQL
Given below is the script to enable file stream in SQL server 2012. Execute the given below code and don’t forget to restart the SQL Server Service.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

2- Via Configuration Manager
Through configuration manager, we need to follow certain steps to enable file stream in sql server:
Step I
Open SQL Server configuration Manager.
Step II
Then locate the sql server instance on which, you need to enable file stream on it.
Step III
Then select the instance and right click on it and select properties.
Enablefilestream1.1
Step IV
Select Filestream Tab and Enable FILESTREM for transact SQL access and give windows share name as well. By default it is instance name. If remote users need the access of file stream then you should also allow remote client access to file stream data.
Enablefilestream1.2
Step V
Click apply

Step 2: Create File table enabled Database in SQL server 2012:
There are 2 ways to create file table enabled database in SQL server 2012.

  1. Via T-SQL
  2. Via SSMS GUI Interface

1- Via T-SQL
Execute the given below script to create a file table enabled database.

CREATE DATABASE [MyFileTableDB]
ON PRIMARY
( NAME = N'MyFileTableDB', FILENAME = N'C:\DATA\MyFileTableDB.mdf'),
FILEGROUP MyFileGroup CONTAINS FILESTREAM (NAME = MyFileTableFS,FILENAME='C:\Data\MyFileTable')
LOG ON
( NAME = N'MyFileTableDB_log', FILENAME = N'C:\DATA\MyFileTableDB_log.ldf')
WITH FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'MyFileTableDB' )
GO

2- Via SSMS GUI Interface
Right click on the databases and create a new database in SSMS and enter all the information required to create a simple database, then go to options and set two properties.

  •   Enter FILESTREAM Directory name = MyFileTableDB
  •   Make FILESTEAM Non-Transacted Access = FULL

Enablefilestream1.3

Lets check that file table enabled database is whether created or not.

SELECT DB_NAME(database_id) as [Database Name],
non_transacted_access,
non_transacted_access_desc
,directory_name
FROM sys.database_filestream_options

Enablefilestream1.4

Step 2.1: Enable a file table in an existing Database in SQL server 2012:
There are 2 ways to enable file table in an existing database in SQL server 2012.

  1. Via T-SQL
  2. Via SSMS GUI Interface

1- Via T-SQL
Execute the given below script to enable a file table in an existing database.

ALTER DATABASE test
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'testfiletable' )
GO

2- Via SSMS GUI Interface
Right click on the databases goto options and set two properties.

  • Enter FILESTREAM Directory name = MyFileTableDB
  • Make FILESTEAM Non-Transacted Access = FULL

Enablefilestream1.3

Will discuss another nice features of file table in up coming posts.

Read Full Post »