Shulou(Shulou.com)06/01 Report--

Personal understanding:

The external tables of FileStream, Filetable, and Oracle are somewhat similar, but not exactly the same. The similarity is that the actual data in the table is stored in the file system, and the table only records metadata.

The data of the Oracle external table is stored in the file system, linked to the database using directory name, and the oracle external table is read-only.

The data of the filestream field of the FileStream table is actually stored in the file system, which is linked to the file system using the directory corresponding to the database FILESTREAM filegroup, that is, the so-called "data container", which is a read-write table.

The data of the Filetable table, personally understood, is actually stored in the file system, and it is based on FileStream. It is linked not only to the directory corresponding to the database FILESTREAM filegroup, but also to the file system using the database Directory name, so that they are accessed from Windows applications as if they were stored in the file system without having to make any changes to your client application. For example, using the operating system, command prompt, batch files, C# or Visual Basic.NET interface System.IO to add update and delete files directly to\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_1 to complete the dml operation of filetable. It's a table that can read and write.

Some experimental conclusions

1. The corresponding file system files in the filetable file table can be deleted in the operating system, but cannot be modified. For example, 123.txt files can be deleted, but cannot be modified using notepad. The notepad window displays the request is not support.

2. The notepad window of the above 1 is not closed, and the database corresponding to the filetable file table can perform backup backup of sqlsderver normally, and can be recovered by normal restore. After recovery, you can see the 123.txt files in the corresponding file system of the restored database.

3. When both filetable1 and filetable2 are file system tables, operations such as insert into filetable1 select * from filetable2 cannot be performed, and an error Column name or number of supplied values does not match table definition will be reported.

4. After the database of the filetable file table is backed up, after the different machine is restored, the file corresponding to filetable is automatically available in the file system of the different machine. For example, the file system corresponding to the database of the filetable file table has files\\ PC1\ MSSQL\ dir1\ table1 under the path of pc1. After restoring to the different machine, the different machine automatically has\\ different machine\ MSSQL\ dir1\ table1.

5. SELECT * into table1 from filetable1; where filetable1 is a filetable file table, but table1 is a regular table rather than a filetable file table, that is, the SELECT INTO statement in FileTable will not propagate FileTable semantics on the created target table (just like the FILESTREAM column in a regular table). All target table columns behave like regular columns.

6. FileTable does not support partitioning.

7. The database of FileStream and FileTable cannot build mirror, and an error will be reported.

A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage.

8. The FILESTREAM filegroups of FileStream and FileTable in always on do not need to be located in shared disk resources.

9. FileTable has two DIRECTORY_NAME, one at the database level and the other at the table level.

10. When only FileStream does not have Filetable, the corresponding file system directory is the directory corresponding to the FILESTREAM filegroup, that is, the data container

11. Filetable file table, there are two corresponding file system directories

11.1.\\ SERVERNAME\ FILESTREAM_SHARE_NAME\ FILESTREAM_DIRECTORY_NAME\ FILETABLE_DIRECTORY

11.2. The directory corresponding to the FILESTREAM filegroup, namely the data container

12. If the corresponding file system directory of FileTable is\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_ 1, after manually adding an operating system file such as test.txt in this directory, you can see a new record by querying select * from testdb.dbo.table1. After performing delete from testdb.dbo.table1, you find that the newly added file no longer exists.

13. The file system directory structure corresponding to the FileTable file table is\\ SERVERNAME\ INSTANCE_DIRECTORY\ DB_DIRECTORY\ TABLE_DIRECTORY. For instance directory, database directory and table directory, please see SERVERPROPERTY ('FilestreamShareName'), sys.database_filestream_options and sys.filetables, respectively.

FileStream

Official document https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filestream-sql-server?view=sql-server-2017

In SQL Server, BLOB can be standard varbinary (max) data that stores data in a table, or a FILESTREAM varbinary (max) object that stores data in a file system. FILESTREAM integrates with the SQL Server database engine varbinary (max) binary large object (BLOB) data by storing it as a file in the NTFS or ReFS file system. The Transact-SQL statement inserts, updates, queries, searches, and backs up FILESTREAM data. FILESTREAM storage is implemented as a varbinary (max) column, where the data is stored in the file system as BLOB. The size of BLOB is limited only by the size of the file system. The varbinary (max) standard limit of file size 2GB does not apply to BLOB stored in the file system. To specify that the column should store data in the file system, specify the FILESTREAM property on the varbinary (max) column. In this way, the database engine stores all the data for the column in the file system, not in the database file. FILESTREAM data must be stored in the FILESTREAM filegroup. A FILESTREAM filegroup is a dedicated filegroup that contains the file system directory rather than the file itself. These file system directories are called "data containers". Data containers are the interface between database engine storage and file system storage, and multiple data containers can be added to FILESTREAM filegroups.

Enable the FileStream function

1. Open the SQLServer configuration manager, find the SQLServer service you want to enable under the SQL server service (the default instance is usually MSSQLServer), right-click the service, select Properties, you can see the FileStream tag in the properties window, and select "enable FILESTREAM for Transact-SQL access"

two。 Execute the following command, or right-click the instance-- properties--advanced--filestream, and select full access enabled

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

3. Add a FILESTREAM type filegroup group filestream1 to the database test

ALTER DATABASE [test] ADD FILEGROUP [filestream1] CONTAINS FILESTREAM

4. Add a value of FILESTREAM type file file file_stream1,filename to the database test G:\ DEFAULT.DATA\ file_filestream1 is a directory non-file, and the parent directory G:\ DEFAULT.DATA must exist first, and the subdirectory file_filestream1 will be created automatically (otherwise, the subdirectory will report that the directory already exists and the subdirectory cannot be created).

ALTER DATABASE test

Add FILE (name = 'file_stream1',FILENAME =' G:\ DEFAULT.DATA\ file_filestream1') TO FILEGROUP filestream1

5 、

5.1. create a regular table and use FILESTREAM

CREATE TABLE table1

(

Id INT NOT NULL PRIMARY KEY

Photo VARBINARY (MAX) FILESTREAM NULL

RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

UNIQUE DEFAULT NEWID ()

)

A new directory is added under the G:\ DEFAULT.DATA\ file_filestream1 directory assuming 1, and a subdirectory is automatically generated under subdirectory 1, which is assumed to be 1

5.2.Create a table of filetable files based on FILESTREAM

ALTER DATABASE [test] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = Naturtest`)

-- set completely non-transactional access to filestream, and must set the directory name at the database level

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON filestream1 WITH

(

FILETABLE_DIRECTORY = Noble table 2, FILETABLE_COLLATE_FILENAME = database_default

)

Where FILETABLE_COLLATE_FILENAME represents the collation, and the value can be database_default or SQL_Latin1_General_CP1_CI_AS, etc.

A new directory is added under the G:\ DEFAULT.DATA\ file_filestream1 directory, assuming 2, and a subdirectory is automatically generated under subdirectory 2, which is assumed to be 2. 1.

6. Insert data into the table1 table in the above 5 examples

INSERT INTO test.dbo.table1 VALUES (1, NULL,newid ())

There are no files under G:\ DEFAULT.DATA\ file_filestream1\ 2\ 2q1

INSERT INTO test.dbo.table1 VALUES (3, CAST ('Seismic Data' as varbinary (max)), newid ())

A file 00000022-00000088-0002 was generated under G:\ DEFAULT.DATA\ file_filestream1\ 2\ 231.

7. Update the records of the table1 table

Update test.dbo.table1 set photo=CAST ('Xray 1' as varbinary (max)) where id=3

A file 00000022-000000c5-0005 is generated under G:\ DEFAULT.DATA\ file_filestream1\ 2\ 231.

8. Delete the record of the table1 table

Delete from test.dbo.table1

The two files under G:\ DEFAULT.DATA\ file_filestream1\ 2\ 2o.1 are still there.

When using FILESTREAM storage, consider the following:

If the table contains FILESTREAM columns, each row must have a unique non-Null row ID.

FILESTREAM data containers cannot be nested.

When using a failover cluster, the FILESTREAM filegroup must be on a shared disk resource. Individuals have experimented that FILESTREAM filegroups do not need to be located in shared disk resources during always on

FileTable

Official document https://docs.microsoft.com/zh-cn/sql/relational-databases/blob/filetables-sql-server?view=sql-server-2017

FileTable is based on the FILESTREAM feature of SQL Server 2008, which allows us to store Windows files in SQL Server, which means we can store files and documents in a special table called FileTable in SQL Server, but access them from Windows applications as if they were in the file system without making any changes to your client application. And these FileTable tables can be backed up and restored using SQL Server, the creation updates and deletions of files in the file system will be reflected in the FILETABLE table, and DML operations will be performed on the FILETABLE table, these changes will also reflect the file system attributes of the file itself, each row of data in the FILETABLE table represents a file, and the fields in the table represent some attribute columns of the file, such as file creation time and modification time, as well as the file path. The FileTable feature enables enterprise customers to store unstructured file data and directory hierarchies in SQL Server databases.

FileTable about transactional and non-transactional issues, FileTable, as a SQL Server table, supports transactionality, but because the operation on files belongs to an Windows file system, it is not transactional, so the non-transactional access level of FILESTREAM data, such as NON_TRANSACTED_ACCESS = FULL, can also be configured at the database level. But that doesn't mean that FileStream itself has to turn on this option. That is, if I don't use FileTable, I don't need to enable the non-transactional access option when I add FILESTREAM FILEGROUP.

Create a table of filetable files

After enabling the FILESTREAM function and creating the file and filegroup of FILESTREAM, configure NON_TRANSACTED_ACCESS and DIRECTORY_NAME to create the filetable

ALTER DATABASE [testdb] ADD FILEGROUP [file_stream1] CONTAINS FILESTREAM

ALTER DATABASE [testdb] ADD FILE (name = 'filestream001',FILENAME =' G:\ DEFAULT\ filestream1') TO FILEGROUP file_stream1

ALTER DATABASE [testdb] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = negative filestreammate testdb')

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1

WITH

(

FILETABLE_DIRECTORY = Noblesheet 1, FILETABLE_COLLATE_FILENAME = database_default

)

After you have created the table, you can create, delete and modify files under the FileStream share path through Windows Explorer. The share path is

\\ SERVERNAME\ FILESTREAM_SHARE_NAME\ FILESTREAM_DIRECTORY_NAME\ FILETABLE_DIRECTORY, enter the\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_1 directory

FILESTREAM_SHARE_NAME: see SSMS-- > right click instance-> Properties-- > Advanced-- > FILESTREAM-- > FILESTREAM SHARE NAME. Default is MSSQLSERVER.

FILESTREAM_DIRECTORY_NAME: see SSMS-- > right click database-> Properties-- > Options-- > FILESTREAM-- > FILESTREAM Directory Name

For example, filetable table name testdb.dbo.table1

1. The corresponding filesystem directory is G:\ DEFAULT.DATA\ filefilestream1\ 1\ 1room1. First, the FILESTREAM filegroup directory G:\ DEFAULT.DATA\ filefilestream1\ directory is created. After the table1 table is created, a new directory under the filegroup directory is assumed to be 1, and a subdirectory is automatically generated under subdirectory 1, which is assumed to be 1room1.

2. The corresponding file system directory is\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_1. After manually adding an operating system file such as test.txt in this directory, you can see a new record by querying select * from testdb.dbo.table1. After performing delete from testdb.dbo.table1, it is found that the newly added file no longer exists.

3. For every new file added in\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_1, 2 new files will be added under G:\ DEFAULT.DATA\ filefilestream1\ 1\ 1

4. After\\ testmachine\ MSSQLSERVER\ filestream_testdb\ table_1 deletes the new file, the two files under G:\ DEFAULT.DATA\ filefilestream1\ 1\ 1 are still there.

The method of loading a file into FileTable

1. Drag and drop files from the source folder to the new FileTable folder in Windows Explorer.

2. Use command line options (such as MOVE, COPY, XCOPY, or ROBOCOPY) from the command prompt, in a batch file, or in a script.

3. Write a custom application for moving or copying files in C # or Visual Basic.NET. The method is called from the System.IO namespace.

Manage filetable

1. Query which tables are filetable file tables

SELECT * FROM sys.filetables

Or

SELECT * FROM sys.tables WHERE is_filetable = 1

2. Disable and re-enable table-level FileTable namespaces

ALTER TABLE filetable_name ENABLE | DISABLE FILETABLE_NAMESPACE

3. Terminate the open file handle associated with the FileTable (the open handle of the file stored in the FileTable can prevent exclusive access required by some administrative tasks. To enable urgent tasks, you may want to terminate the open file handle associated with one or more FileTable)

3.1. list of open file handles associated with FileTable

SELECT * FROM sys.dm_filestream_non_transacted_handles

3.2. terminate the open file handle associated with FileTable

USE database_name

-- Kill all open handles in all the filetables in the database.

EXEC sp_kill_filestream_non_transacted_handles

-- Kill all open handles in a single filetable.

EXEC sp_kill_filestream_non_transacted_handles @ table_name = 'filetable_name'

-- Kill a single handle.

EXEC sp_kill_filestream_non_transacted_handles @ handle_id = integer_handle_id

4. Query the DIRECTORY_NAME used by the instance's FILESTREAM

SELECT SERVERPROPERTY ('FilestreamShareName')

5. Query the DIRECTORY_NAME corresponding to the database of the FILETABLE table

Select db_name (database_id), * from sys.database_filestream_options

When using only the filestream function, the database does not need the corresponding DIRECTORY_NAME

6. Query the DIRECTORY_NAME corresponding to the FILETABLE table

Select object_name (object_id), * from sys.filetables

7. Query the full path name of the file in the filetable table testdb.dbo.table1

SELECT FileTableRootPath () + [file_stream] .GetFileNamespacePath (), name FROM testdb.dbo.table1

8. Query the file system directory corresponding to the FILETABLE table, right-click the FILETABLE table, and select Explore FileTable Directory

Description of table-level FILETABLE_DIRECTORY

The FILETABLE_DIRECTORY of the FileTable table is automatically created when the table is created. If FILETABLE_ directory is not specified when the table is created, FILETABLE _ DIRECTORY is equal to the table name. Different FileTable tables must use different FILETABLE_DIRECTORY directories. One FILETABLE_DIRECTORY directory cannot be used by two different tables.

1 、

ALTER DATABASE [testdb] SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,DIRECTORY_NAME = Naturtestdb')

-- the name of the DIRECTORY_NAME directory of the database is testdb. See SSMS-- > right click Database-> Properties-- > Options-- > FILESTREAM-- > FILESTREAM Directory Name.

Absolute path:\\ testmachine\ MSSQLSERVER\ testdb\

2 、

CREATE TABLE table1 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = Numbtable1, FILETABLE_COLLATE_FILENAME = database_default

)

-- the FILETABLE_DIRECTORY directory name of table table1 is table1

Absolute path:\\ testmachine\ MSSQLSERVER\ testdb\ table1

3 、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = Numbtable1, FILETABLE_COLLATE_FILENAME = database_default

)

The error is as follows, because the FILETABLE_DIRECTORY directory name 'table1' is already used by table table1 and can no longer be used by table table2

FILETABLE_DIRECTORY 'table1' attempting to be set on table' table2' is not unique in the database 'file_db'. Provide a unique value for the option FILETABLE_DIRECTORY to this operation.

4 、

CREATE TABLE table2 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_DIRECTORY = Noble 99 percent, FILETABLE_COLLATE_FILENAME = database_default

)

-- the FILETABLE_DIRECTORY directory name of the table table2 is table99

Absolute path:\\ testmachine\ MSSQLSERVER\ testdb\ table99

5 、

CREATE TABLE table3 AS FILETABLE FILESTREAM_ON file_stream1 WITH

(

FILETABLE_COLLATE_FILENAME = database_default

)

-- the FILETABLE_DIRECTORY directory name of table table3 defaults to table3

Absolute path:\\ testmachine\ MSSQLSERVER\ testdb\ table3

Error reports encountered by Filetable

1. Backup error report

The operating system returned the error'5 (Access is denied.)' While attempting 'CreateFile' on'T:\ DEFAULT.FILESTREAM.DATA\ Netapp1_9_FS2\ ee50c020-b8df-485b-b7fd-924123a9f8e5\ 1dafa2ac-3d5e-4b8e-b4c5-711ded06ae19\ 00001526-000034f6-0002.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

2. Restore error (backup package is normally backed up)

The operating system returned the error'32 (The process cannot access the file because it is being used by another process.)' While attempting 'OpenFile' on' F:\ FS2.FILESTREAM.DATA\ Netapp2_1_FS5\ 0bb73706-bf4a-4d2e-84bb-fa714c2ba93a\ d6c88672-71bf-40b3-9229-667528207588\ 00000352-0003d60dMurray 0015mm.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

3. Restore error (backup package is normally backed up)

A previous restore operation was interrupted and did not complete processing on file 'AMPFileStream'. Either restore the backup set that was interrupted or restart the restore sequence.

4.\\ SERVERNAME\ INSTANCE_DIRECTORY\ DB_DIRECTORY\ TABLE_DIRECTORY opens normally, but cannot be opened once after restarting the server. Check that the database is all right. It can be opened normally using\ 127.0.0.1\ INSTANCE_DIRECTORY\ DB_DIRECTORY\ TABLE_DIRECTORY in the server, but cannot be opened using\\ SERVERNAME\ INSTANCE_DIRECTORY\ DB_DIRECTORY\ TABLE_DIRECTORY or\\ server IP\ INSTANCE_DIRECTORY\ DB_DIRECTORY\ TABLE_DIRECTORY. If you think there should be a problem with remote access, just reset the filestream function and right-click on Sql Server Configuration Manager--Sql Server Services-- to correspond to the instance-- Properties--FILESTREAM.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.