The MS-SQL File Table is available from SQL Server 2012. You can use MS-SQL to save the file and read or modify it in Windows.
Conversely, if you modify or delete a file in Windows, it is immediately reflected in MS-SQL.
In other words, attachments can be saved, deleted, modified and read through the DB.
[Try it]
I tested it on SQL Server 2016 in the cloud
[Setting]
On the DB server, run SQL Server Configuration Manager and set up as follows. (SQL Server Configuration Manager)
SQL Server -> Right Click - Properties -> Go to FILESTREAM
Enable all of these features
Enable FILESTREAM for T-SQL Access: Enable Function
Enable FILESTREAM for file I / O Access: Enables file access within FileTable contents on Windows
Allow remote clients access to FILESTREAM data: Allow access to the contents of the FileTable from a remote server.
Execute the following SQL to grant access authority. (0 to 2), SQL Server restart is required.
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
A folder accessible to the network share has been created as shown below.
Files stored in the FileTable will be displayed under the folder below.
Attributes of DB to create File Table as above -> Go to Filegroup, press AddFilegroup in FILESTREAM, and write down the name to be saved. Multiple is possible.
After saving, go back to options and go into Files and set up as above.
It is the location where the actual file is stored. Change the File Type to FILESTREAM Data.
And you set the folder name and permissions in the above options. Make it full so that it can be accessed through the explorer.
Non-Transacted Access - Non-Transacted Access: This is often seen as access through the Windows Explorer rather than MS-SQL.
[Creat Table]
CREATE TABLE WMDM.dbo.Files AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'Files',
FILETABLE_COLLATE_FILENAME = DATABASE_DEFAULT
)
위와 같이 실제 File Table을 생성한다.
The File Table was created as above.
One drawback is that we can not add / remove internal columns at will. You should only use what is provided.
It can be imported directly from the DB as a file stream, and includes information such as file name, file size, creation date, and access date.
In the past, all of this should have been handled in C #, which DB takes care of.
Details : File Table Schema https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetable-schema
The folder was created in Explorer as above.
[Try it]
I tried to copy a file into a folder in File Table.
I tried Select .
It becomes Select in DB ... It is automatically filled with file size, creation date, and so on. Miraculous.
I have tried to delete the file from Explorer .
I created a folder (This is folder) and I put the BP under it. !!!
Even changing the folder name or file name will change immediately.
Let's delete!
delete from wmdm.dbo.Files WHERE name = 'This is Folder' -- This will cause an error. An error occurs because there is a file underneath.
delete from wmdm.dbo.Files WHERE name = 'BP.zip' -- Erase the files inside the folder ..
delete from wmdm.dbo.Files WHERE name = 'This is Folder' -- I deleted the folder
BP.zip and folder disappear on DB.
The file was also deleted from the real folder.
[Warning]
Never select * from FILE TABLE.
The filestream part of the column is the actual file contents.
I chose to * with no WHERE, and I was getting all the files of tens of megabytes in SSMS.
It took a very long time to get the file because it was Cloud.
If you select the above in the operating environment, you may receive several GB to several hundred GB of attachments as SSMS. The cancellation was also slow.
It seems that it can occupy all the traffic and cause big trouble.
[Idea?]
Only attachments can be managed by DB!
The current Web team is uploaded to an attachment (GPM or server, respectively) and managed by specifying a path to DB.dbo.Files.
When you upload a file, it stores the actual file in each location, and puts the storage path, file name, and size in the DB.
Conversely, if you want to delete a file, you must actually delete the file in C # and update it in the DB. C # and MSSQL alone are not possible.
With the File Table, you can delete files from DB.dbo.Files with only one SP and delete the actual files.
It is convenient for mass work.
Very often ... when you need to migrate a large number of attachments.
It's inconvenient because you have to work with both C # and DB.
The File Table goes up to DB when copy-pasting into Explorer.
[Reference]
Loading files into a FileTable: https://docs.microsoft.com/en-us/sql/relational-databases/blob/load-files-into-filetables
Server / Disk Configuration Guide : https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-and-configure-filestream
