Our application stores user files on the physical drives (alebit a large number of them). The files are organised and grouped into folders (one folder for each user). The application also manipulates the folders by creating sub folders within it and grouping files into those subfolders. The physical location of each file is stored in the SQL Server database. As such you can gauge that the application is tighly coupled with the physical filesystem. We are looking to migrate the file management operations to SQL Server FILESTREAM. However as I have understood FILESTREAM does not allow one to create a file hierarchy consisting of folder and folder groups. Also FILESTREAM does not allow me to rename files. Is this true of FILESTREAM? Is there any other option for using FILESTREAM to manage the physical files without me having to change my application logic considerably?
A:
See here for a basic overview of the Filestream feature.
When using Filestream, you let Sql Server engine control the placement and naming of NTFS files. The physical paths are abstracted by the engine and therefore clients cannot directly manipulate/rename them.
The idea is that if your scenario organizes the blobs into a hierarchy, that hierarchy needs to be implemented on the relational level.
Since opening filestream data needs to happen through special API (and involves the Sql Server engine), your existing client code would have to be changed to use this API rather than opening NTFS files directly.
Pawel Marciniak
2010-09-20 16:54:10