SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties

Read Part 1 Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

Read Part 2 Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

In this third part of the series, we will see how we can retrieve various information from the FileTable database.

Identify Directory or File

You can run following query which will give details if query is file or directory.

USE FileTableDB
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[is_directory]
FROM [dbo].[FileTableTb]
GO

If [is_directory] is 1 it means the row belongs to folder.

Root Level Path of the File

SELECT FileTableRootPath('FileTableTb') AS FileTableRootPath

Above query will return the filepath where the filetable has stored the files

\\MyPersonalServer\MSSQLSERVER\FileTableDB\FileTableTb_Dir

Specific Locator ID for a file by providing the path

SELECT GetPathLocator('\\MyPersonalServer\MSSQLSERVER\FileTableDB\FileTableTb_Dir')

Above query will provide the locator ID for the file.

Relative path of particular file or directory

SELECT file_stream.GetFileNamespacePath()
FROM [dbo].[FileTableTb]

Above query will return following resultset. It will give relative path from the FileTable folder with hierarchy of the directory.

\FileTableTb_Dir\mydir
\FileTableTb_Dir\mydir\SharePoint SSIS Adapters 2011.docx
\FileTableTb_Dir\5 Tips for a Smooth SSIS Upgrade to SQL Server 2012.docx

There are more to FileTable and we will see those in my future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

Read Part 1 Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

In this second part of the series, we will see how we can insert the files into the FileTables. There are two methods to insert the data into FileTables:

Method 1: Copy Paste data into the FileTables folder

First, find the folder where FileTable will be storing the files. Go to Databases >> Newly Created Database (FileTableDB) >> Expand Tables. Here you will see a new folder which says “FileTables”. When expanded, it gives the name of the newly created “FileTableTb”. Right click on the newly created table, and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

When clicked on the option it will open up following folder in my local machine where the FileTable data will be stored.

\\127.0.0.1\mssqlserver\FileTableDB\FileTableTb_Dir

You can just copy your document just there. I copied few word document there and ran select statement to see the result.

USE [FileTableDB]
GO
SELECT *
FROM FileTableTb
GO

SELECT * returns all the rows. Here is SELECT statement which has only few columns selected from FileTable.

SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO

I believe this is the simplest method to populate FileTable, because you just have to move the files to the specific table.

Method 2: T-SQL Insert Statement

There are always cases when you might want to programmatically insert the images into SQL Server File table. Here is a quick method which you can use to insert the data in the file table. I have inserted a very small text file using T-SQL, and later on, reading that using SELECT statement demonstrated in method 1 above.

INSERT INTO [dbo].[FileTableTb]
([name],[file_stream])
SELECT
'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
GO

The above T-SQL statement will copy the NewFile.txt to new location. When you run SELECT statement, it will retrieve the file and list in the resultset. Additionally, it returns the content in the SELECT statement as well. I think it is a pretty interesting way to insert the data into the FileTable.

SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO

There are more to FileTable and we will see those in my future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

Filestream is a very interesting feature, and an enhancement of FileTable with Filestream is equally exciting. Today in this post, we will learn how to set up the FileTable Environment in SQL Server.

The major advantage of FileTable is it has Windows API compatibility for file data stored within an SQL Server database. In simpler words, FileTables remove a barrier so that SQL Server can be used for the storage and management of unstructured data that are currently residing as files on file servers. Another advantage is that the Windows Application Compatibility for their existing Windows applications enables to see these data as files in the file system. This way, you can use SQL Server to access the data using T-SQL enhancements, and Windows can access the file using its applications. So for the first step, you will need to enable the Filestream feature at the database level in order to use the FileTable.

-- Enable Filestream
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
-- Create Database
CREATE DATABASE FileTableDB
ON PRIMARY
(Name = FileTableDB,
FILENAME = 'D:\FileTable\FTDB.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS,
FILENAME='D:\FileTable\FS')
LOG ON
(Name = FileTableDBLog,
FILENAME = 'D:\FileTable\FTDBLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'FileTableDB');
GO

Now, you can run the following code and figure out if FileStream options are enabled at the database level.

-- Check the Filestream Options
SELECT DB_NAME(database_id),
non_transacted_access,
non_transacted_access_desc
FROM sys.database_filestream_options;
GO

You can see the resultset of the above query which returns resultset as the following image shows. As you can see , the file level access is set to 2 (filestream enabled).

Now let us create the filetable in the newly created database.

-- Create FileTable Table
USE FileTableDB
GO
CREATE TABLE FileTableTb AS FileTable
WITH
(FileTable_Directory = 'FileTableTb_Dir');
GO

Now you can select data using a regular select table.

SELECT *
FROM FileTableTb
GO

It will return all the important columns which are related to the file. It will provide details like filesize, archived, file types etc.

You can also see the FileTable in SQL Server Management Studio. Go to Databases >> Newly Created Database (FileTableDB) >> Expand Tables

Here, you will see a new folder which says “FileTables”. When expanded, it gives the name of the newly created FileTableTb.

You can right click on the newly created table and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

When you click on the option, it will open up the following folder in my local machine where the FileTable data will be stored:

\\127.0.0.1\mssqlserver\FileTableDB\FileTableTb_Dir

In tomorrow’s blog post as Part 2, we will go over two methods of inserting the data into this FileTable.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Guest Post – Jacob Sebastian – Filestream – Wait Types – Wait Queues – Day 22 of 28

Jacob Sebastian is a SQL Server MVP, Author, Speaker and Trainer. Jacob is one of the top rated expert community. Jacob wrote the book The Art of XSD – SQL Server XML Schema Collections and wrote the XML Chapter in SQL Server 2008 Bible. See his Blog | Profile. He is currently researching on the subject of Filestream and have submitted this interesting article on the very subject.

What is FILESTREAM?

FILESTREAM is a new feature introduced in SQL Server 2008 which provides an efficient storage and management option for BLOB data.

Many applications that deal with BLOB data today stores them in the file system and stores the path to the file in the relational tables. Storing BLOB data in the file system is more efficient that storing them in the database. However, this brings up a few disadvantages as well. When the BLOB data is stored in the file system, it is hard to ensure transactional consistency between the file system data and relational data.

Some applications store the BLOB data within the database to overcome the limitations mentioned earlier. This approach ensures transactional consistency between the relational data and BLOB data, but is very bad in terms of performance.

FILESTREAM combines the benefits of both approaches mentioned above without the disadvantages we examined. FILESTREAM stores the BLOB data in the file system (thus takes advantage of the IO Streaming capabilities of NTFS) and ensures transactional consistency between the BLOB data in the file system and the relational data in the database.

For more information on the FILESTREAM feature, visit: http://beyondrelational.com/filestream/default.aspx

FILESTREAM Wait Types

Since this series is on the different SQL Server wait types, let us take a look at the various wait types that are related to the FILESTREAM feature.

FS_FC_RWLOCK

This wait type is generated by FILESTREAM Garbage Collector. This occurs when Garbage collection is disabled prior to a backup/restore operation or when a garbage collection cycle is being executed.

FS_GARBAGE_COLLECTOR_SHUTDOWN

This wait type occurs when during the cleanup process of a garbage collection cycle. It indicates that that garbage collector is waiting for the cleanup tasks to be completed.

FS_HEADER_RWLOCK

This wait type indicates that the process is waiting for obtaining access to the FILESTREAM header file for read or write operation. The FILESTREAM header is a disk file located in the FILESTREAM data container and is named “filestream.hdr”.

FS_LOGTRUNC_RWLOCK

This wait type indicates that the process is trying to perform a FILESTREAM log truncation related operation. It can be either a log truncate operation or to disable log truncation prior to a backup or restore operation.

FSA_FORCE_OWN_XACT

This wait type occurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.

FSAGENT

This wait type occurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.

FSTR_CONFIG_MUTEX

This wait type occurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.

FSTR_CONFIG_RWLOCK

This wait type occurs when there is a wait to serialize access to the FILESTREAM configuration parameters.

Waits and Performance

System waits has got a direct relationship with the overall performance. In most cases, when waits increase the performance degrades. SQL Server documentation does not say much about how we can reduce these waits. However, following the FILESTREAM best practices will help you to improve the overall performance and reduce the wait types to a good extend.

Read all the post in the Wait Types and Queue series.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – FILESTREAM Storage in SQL Server 2008

This white paper describes the FILESTREAM feature of SQL Server 2008, which allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

This white paper is Written By: Paul S. Randal (SQLskills.com) Read the white paper here.

Reference : Pinal Dave (http://blog.sqlauthority.com)