We have a new feature being developed for our publicly accessible websites that enables users to upload files into “the system”. At first glance the SQL Server Filestream feature looks perfect for this type of scenario however there are several limitations that cripple it’s usefulness in publicly accessible environments.
The SQL Server Filestream feature has two modes of operation. In the first mode all file access is performed through transact SQL. Even though the file is stored on the SQL Server’s filesystem it is access via T-SQL and is returned as a blob (varbinary(max)). This is essentially the same as storing the file in the database as a blob.
In the second mode, the application queries the filestream enabled table to get the handle of the file. It then “streams” the file using a SMB connection to the SQL Server. This is by far more efficient that accessing the file via T-SQL. It’s more efficient because SQL Server is doing what it does best, returning data sets, and Windows Server is doing what it does best, file serving.
For maximum performance we want to use the second mode however there are a few problems using the streaming feature.
- The fileshare is located on the SQL Server itself. For a webserver in the DMZ to use the streaming feature it needs SMB access to the SQL Server. This is generally a port that we’d rather not open up.
- Often the DMZ servers are not on the same domain as internal servers. This causes issues with authentication to the fileshare. There may be ways around this such as passing credentials from the application to the fileshare but even so this may be a cludgy approach.
After reviewing the filestream feature we’re still using it, however we’re using it in T-SQL mode. It looks as though the streaming mode is probably best used on internal applications where security isn’t a major factor. Typical examples that come to mind are intranets, document management systems, Customer Relationship Management systems etc.
