How to enable FILESTREAM in Sense/Net
Sense/Net ECMS is a robust and scalable system that lets administrators add more resources as needed. One of the biggest problems of such large content repositories is how and where to store huge amount of documents or other files. A possible solution for this is to store the files in the file system, outside of the database. This article describes a way for system administrators to enable the FILESTREAM feature of Microsoft SQL Server in Sense/Net ECMS.
Sense/Net Content Repository lets you store any kind of content in the database: articles, images, office documents, even video files. It provides a unified way of handling binary values along with metadata in a consistent and secure way. However storing binaries in the database can cause severeal problems: serving large files from the database can be slow and in case of write-intensive scenarios fragmentation is guaranteed.
The FILESTREAM feature of Microsoft SQL Server was designed to solve these problems and provide the best of both worlds. It is essentially a special kind of binary column in the database that actually stores the files outside of the database. It comes with the following advantages:
- Binaries are located in a separate file group that can be stored on a different (more efficient) storage (the database contains only an identifier that points to a file system, where the actual file is located).
- Same access methods apply for a FILESTREAM column as to a simple varbinary(max) database column. This means the feature can be transparent to the system above if needed.
- If the database layer knows about the filestream column, it can access it in a special way that lets the program access the file system directly, using the Win32 API - still in the database transactional context.
- Lower memory footprint and faster data access.
- Security is still handled by SQL Server.
Sense/Net ECMS provides a way for system administrators to enable storing large files in a FILESTREAM column and handle documents and other files more efficiently. The following sections describe how you can switch this feature on.
Please be aware that the following method involves modifying the database with an SQL script. After you executed the script, the feature cannot be removed. You may choose not to use it later, as you can see in the configuration section of this article.
Before you begin, you have to have Sense/Net ECMS installed. Please see the following article about installing the product:
Please note that the default version of Sense/Net ECMS works on SQL Server 2008 and above. To be able to use FILESTREAM, you will have to use at least SQL Server 2008 R2 because of isolation level restrictions in previous versions.
Please also note that the FILESTREAM feature works only if the application uses Integrated Authentication (Windows Authentication) connecting to SQL Server. You must ensure that the account has file system permissions to write to the FILESTREAM container.
Please check the Configuration for Production Environment article for more detailed information about software and hardware requirements of the default install.
The following steps involve modifying the database and the web.config file. It is recommended to stop the IIS site of Sense/Net before you begin.
Enabling the FILESTREAM on SQL Server
First you have to enable the feature manually in Sql Server Configuration Manager.
- Choose the SQL Server Services tree item and locate the particular SQL server service that runs the Sense/Net ECMS database.
- Open Properties window and navigate to the FILESTREAM tab
- Enable Transact-SQL, I/O and Remote client access (all three checkboxes).
- Apply changes and restart the SQL server service.
Modifying the database
After the feature was enabled on the service, you have to execute a SQL script that updates the Sense/Net ECMS database:
- Creates a new file group with one 'file' that will store your documents in the file system (actually it will be a whole folder as you will see later).
- Adds a new FileStream column to the binaries table.
- Modifies a couple of stored procedures to enable the new feature.
Download and execute the following SQL script in SQL Management Studio. Make sure that you are executing it on the appropriate database.
This featue is available in Sense/Net 6.3.
You should see an output similar to the following:
Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install. FILEGROUP and FILE added to database. UNIQUE constraint added to RowGuid column. FILESTREAM column added. Old stored procedures dropped. New stored procedures created.
In older versions of SQL Server you'll receive the following error message when executing the script:
ALTER DATABASE failed because FILESTREAM filegroups cannot be added to a database that has either the READ_COMMITTED_SNAPSHOT or the ALLOW_SNAPSHOT_ISOLATION option set to ON. To add FILESTREAM filegroups, you must set READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION to OFF.
The only thing that you can configure is the minimum size limit of this feature. You can fine-tune your environment by defining which files should be stored in the file system. The following value (given in kBytes) is used to determine if the file is big enough to go to the file system. Files under this size will be stored in the database.
<add key="MinimumSizeForFileStreamKB" value="500"/>
If you installed the FILESTREAM feature but choose not to use it, you may set this size limit big enough to ensure that all files will be saved into the database from than on. On the other hand, if you want to store all files in the file system (e.g. because of database size limitations of a particular SQL server version), you can set this limit to 0.
Check the feature
To make sure that the feature works, you may do the following:
- Upload a file that is bigger than the previously set file size limit.
- Navigate to the Data folder of SQL server, where your database files are located. You should see a folder named SenseNetContentRepository_files that will contain the binaries. These files are managed by SQL Server and you should not try to modify them manually.
- Open the Properties window of the Sense/Net database in SQL Server Management Studio. On the Filegroups tab you should see a new file group named SNFileGroup.
- You may execute the following script to check the values in the Stream and FileStream columns (only one of them should contain a value, depending on the file size):
SELECT * FROM [BinaryProperties] ORDER BY CreationDate DESC
Here are a couple of articles about the FILESTREAM feature that you may want to check: