FILE uploads in the traditional sense is all about uploading data files to the Server and keeping it there for downloading later on. If you are like me and have coded Web Applications with File uploading capabilities – then it’s most likely you have configured your Web application to accept and store these uploaded files in an ”upload’ folder on the server; while the relative path (or perhaps the full-path) to the file is stored as a field in the database.
This works fine… there are no major issues with this method at all.
In this article I will explain some alternative solutions to this method, and show how you can enable the use of SQL Server FileStreams in your applications…
Uploading files from the Client machine to the Web Server is nice and easy. But the simplicity of this method clouds two major flaws:
a) Security – anyone with access to the upload destination folder can view, modify or even delete these file.
b) Lack of synchronisation – the database record attached to the file can go out of sync with the data file. This will happen if synchronisation is not handled in the application. For example, the record can be deleted from the database, but the file still remains on the server. Or vice-versa the file can be delete and the record can remain in the database.
One old school solution to this is to use BLOB types in SQL Server. BLOB types store the entire contents of the file in the SQL server database. I shall explain further below..
Blobs will resolve the two issues identified above. There will be no security leaks with file access, and there will be no lack of synchronisation between the file and the database record because when the record is deleted so is the BLOB data. But the biggest concern with BLOB types is that it uses up the file size of the SQL Database. For each SQL database you will notice at least two files attached to it:
- a) the Row Data file (*.MDF) and
- b) the LOG file (*.LDF)
As data is inserted into a database the Row Data storage file increases in size. If BLOB types are used any BLOBs inserted into the database will also increase this file size – thus capping the space allocation sooner than you would expect.
This is where SQL Server FileStream Types come to the rescue. SQL Server FileStream Types store file content in a separate virtual location away from the Row Data and Log data files. Uploading files as a FileStream type will not cap the databases’ Row Data file as BLOBS do. Any related data like ID’s and File paths can be stored as normal database fields. Synchronisation is maintained when the record is removed from the database table, so is the associated FileStream content.
But what about the security?… When enabling FileStreams, the Database Admin is given the option to enable File System access to these files, or limit the access via TSQL commands only. Disabling File System access will lock down any security access to these files.
Below I will explain the steps to enable FileStream Types in your SQL Server instance:
a) Open up SQL Server Configuration Manager
b) Select the SQL Server instance and go into its Properties
c) Click on the FILESTREAM tab, and tick the following options
1) Enable FileStream for Transact-SQL access: You must tick this option to turn on FilStreaming.
2) (optional) Enable FILESTREAM for File I/O streaming access: If you want to allow read / write FILESTREAM data from Windows then tick this box.
3) (optional) Enable remote client access: If you want to allow clients connecting to this Server to have access to the files, then tick this box.
That’s it! Your SQL Server is now ready for FileStream Type!
In PART 2 – I will explain how to configure your Database to store FileStream Type data.