DONE: Microsoft SQL Server already implements this functionality at the database level.
For Microsoft SQL Server the solution would be to enable and use FILESTREAM.
There are two specific options:
- The first option is to use FILESTREAM backed varbinary columns.
FILESTREAM backed columns are completely transparent to dbFront and require no special handling.
- The second option is to create a FILETABLE which uses FILESTREAM. A FILETABLE not only stores the files in the file system but automatically reflects file structure changes back into the table.
For detailed instructions on implementing FILESTREAM please see:
Based on your request a FILETABLE is exactly what you need. If FILESTREAM is enabled then you can use the following to create a FILETABLE.
-- Create File Group
ALTER DATABASE AdventureWorksLT
ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
GO
-- Create FILESTREAM File and add to FILEGROUP
ALTER DATABASE AdventureWorksLT ADD FILE (
NAME = 'AdventureWorksLT_Filestream',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT_Filestream'
)
TO FILEGROUP FileStreamFileGroup;
GO
-- Create a FILETABLE named DocumentStore
CREATE TABLE DocumentStore AS FileTable WITH (
FileTable_Directory = 'DocumentTable',
FileTable_Collate_Filename = database_default
);
GO
-- Create a VIEW to get the Files Path
CREATE VIEW vDocumentStore_FilePath as (
SELECT
ds.stream_id,
FileTableRootPath() as rootpath,
ds.file_stream.GetFileNamespacePath() as filepath,
FileTableRootPath() + ds.file_stream.GetFileNamespacePath() AS fullpath
FROM DocumentStore ds
);
GO
The newly created view [vDocumentStore_FilePath] can be joined to the new [DocumentStore] table within dbFront to expose the network accessible path.
