Are you suggesting that I simply make a virtual directory within the application directory that actually points to a share that contains the actual physical filepath that is being used by FILESTREAM?
Yes exactly in understanding, but there is a potential catch. Now in my case it happened to be another VD outside the actual application, and in my case I did
not use FILESTREAM to store the files, but rather manually saved them using System.IO (project was done prior to SQL 2008).
The caveat to using the FILESTREAM is the blobbed data is stored in such a manner that it is
not directly assessable as "whatever.mp3". The UNC path leads to the
blobbed data. In order to view the file you need to use a SqlFileStream Type (not System.IO) so I don't think the VD pointing to the blobbed data would work in that situation. You might be able to do something like create a custom HttpHandler (on the
site for accessing the files) that when the file was requested could realtime use the SqlFileStream object to pull the file unbenounced to the client. I have not tried accessing the blobbed data directly so someone else might have more details or a better
solution when using a FILESTREAM. Here is some documentation from MSDN on this:
Storage Namespace In FILESTREAM, the Database Engine controls the BLOB physical file system namespace. A new intrinsic function, PathName, provides the logical UNC path of the BLOB that corresponds to each FILESTREAM cell in the table. The application uses this logical path
to obtain the Win32 handle and operate on the BLOB data by using regular Win32 file system interfaces. The function returns NULL if the value of the FILESTREAM column is NULL.
One of the really nice things I like about using FILESTREAM is when it comes to deleting files. In my case with the example I provided, I had to keep reference to both the file URL for read only access, and
also the UNC path for file deletion (can't use URL to delete file). With FILESTREAM, deleting a row deletes the underlying blobbed data which is a plus.
atconway
All-Star
16846 Points
2756 Posts
Re: Mass Media Storage Architecture
Apr 03, 2012 02:23 PM|LINK
Yes exactly in understanding, but there is a potential catch. Now in my case it happened to be another VD outside the actual application, and in my case I did not use FILESTREAM to store the files, but rather manually saved them using System.IO (project was done prior to SQL 2008).
The caveat to using the FILESTREAM is the blobbed data is stored in such a manner that it is not directly assessable as "whatever.mp3". The UNC path leads to the blobbed data. In order to view the file you need to use a SqlFileStream Type (not System.IO) so I don't think the VD pointing to the blobbed data would work in that situation. You might be able to do something like create a custom HttpHandler (on the site for accessing the files) that when the file was requested could realtime use the SqlFileStream object to pull the file unbenounced to the client. I have not tried accessing the blobbed data directly so someone else might have more details or a better solution when using a FILESTREAM. Here is some documentation from MSDN on this:
Storage Namespace
In FILESTREAM, the Database Engine controls the BLOB physical file system namespace. A new intrinsic function, PathName, provides the logical UNC path of the BLOB that corresponds to each FILESTREAM cell in the table. The application uses this logical path to obtain the Win32 handle and operate on the BLOB data by using regular Win32 file system interfaces. The function returns NULL if the value of the FILESTREAM column is NULL.
One of the really nice things I like about using FILESTREAM is when it comes to deleting files. In my case with the example I provided, I had to keep reference to both the file URL for read only access, and also the UNC path for file deletion (can't use URL to delete file). With FILESTREAM, deleting a row deletes the underlying blobbed data which is a plus.