"Go" easy to manage files with filetable SQL Server 2012

I. Introduction of FILESTREAM and Filetable

We often need to store structured data (int, char, and so on) and unstructured data (such as varbinary (max)), so how do we store it?

1. Before SQL Server 2008, we typically store structured data in a database and store unstructured data (such as documents, audio, video, and so on) in the NTFS file system, and then store the paths to those files in the database. However, it is difficult to guarantee the consistency of the file system and the database.

2. In SQL Server 2008, a new FileStream, which allows us to store files of type varbinary (max) on the NTFS file system, is able to manage files directly from the file system through SQL Server, effectively guaranteeing transactional consistency. However, the file system is used only to store files and cannot be used to organize and manage files.

3, in SQL Server2012, on the basis of FileStream added filetable, it has all the functions of FileStream. And through the Windows file system to organize and manage the file, all operations on the file will be reflected in the SQL Server file table, such as the file system to create new files, new folders, change file type, file size, etc., will be corresponding to the SQL The server's file table does the corresponding operation.

Second, how to configure and use Filetable

1. Turn on file stream access in SQL Server Configuration Manager

A) tick "enable FILESTREAM for Transact-SQL access"

b) Tick "Enable FileStream for file I/O access". "Windows share name (W)" Fill in "Canwaysqlfile"

c) Tick "Allow remote client access to FILESTREAM data (R)"

2. Allow access to file streams on instance properties of the database

Open SQL Server Management Studio and modify the configuration of the instance. The default configuration is as follows:

Change "FileStream access level" from "disabled" to "full access enabled"

There are three options available for the FileStream access level:

A) disabled

Binary large object (BLOB) data cannot be stored in the file system. This is the default value. That is FileStream access level=0

b) Transact-SQL access is enabled

FILESTREAM data can be accessed using Transact-SQL, but not through the file system. That is FileStream access level=1

c) Full access is enabled

FILESTREAM data can be accessed using Transact-SQL and through the file system. That is FileStream access level=0

When you click OK, you are prompted to restart the SQL Server service before it takes effect:

3. Add file stream groups and files to the database

A) Add a file flow group

Right-click Database filetabledb-> Properties--file group, and in the bottom of "FileStream" tap "Add" to add a file stream group Filestreamgroup.

b) Add File

After you add a file flow group, you can add data files to the file flow group

Right-click Database filetabledb-> Properties-file, add a database file named "FileTable", File type "FILESTREAM data", filegroup "Filestreamgroup", and Path to "E:\FB".

Open the Directory "E:\FB", you can see the automatic new Folder "FileTable", under the FileTable folder there is a Filestream.hdr file, it is the FileStream container header file

4. The database enables "non-transactional access" and fills in the "FileStream directory name"

Right-click Database filetabledb-> Properties--Options

A) option "full" at "FileStream non-transactional access"

b) Fill in "ImageFile" at "FileStream directory name"

After clicking "OK" prompt:

A direct click is all you can do.

5. create File Table filetable

Open SSMs, open a new Query window, enter the following command and run

Expand Database Filetabledb, there is a folder named Filetable under the database, expand this folder, you can see a table named ImageTable2 with many columns.

6. Get the shared path

After you create the file table ImageTable2 under Database Filetabledb, you will be able to see the files stored in the file table ImageTable2 through the Windows Explorer.

Get the shared path to the file table ImageTable2 first, and run the following command under SSMs to get:

7, access the database through the shared Path Filetabledb file table ImageTable2 files stored in

A) access through shared paths

There are currently no files in the shared file.

b) Query ImageTable2

Catalog table ImageTable2 is empty, too.

c) Open the above shared directory on Windows Explorer

I. Create a new file named "Test.txt"

And then query the table ImageTable2

(There are other fields later, not truncated)

It can be found that a file is created under a shared directory, and a record is automatically added under Table ImageTable2.

II. Similarly, deleting the records under Table ImageTable2 in the database will automatically delete the files in the shared directory accordingly.

III. Creating a folder in a shared directory also automatically creates a folder in the file table

Iv. changing the name of the file in the shared directory, adding the contents of the file, changing the type of the file, and so on, will automatically make the corresponding changes under the ImageTable2 of the database.

Iii. Summary

With SQL Server 2012 Filetable, we can easily organize and manage the files that are stored in the database. The file system can be directly to create a multi-level directory of files for classification management, can modify the file type, modify the filename, etc., these will not affect the database and file system consistency.

"Go" easy to manage files with filetable SQL Server 2012