Storing content outside of SQL Server for ILINX Content Store using SQL FILESTREAM
By design, ILINX Content Store stores documents within the SQL database as BLOBs. There are many advantages to this design (security, performance, etc.) but sometimes there is a reason to store the documents outside of the SQL database. SQL Server has a method to do this called FILESTREAM. FILESTREAM integrates SQL Server with the NTFS file system by storing varbinary(max) data outside of the SQL database. FILESTREAM uses the NT system cache for caching file data: this helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
The Advantages of FILESTREAM
One of the main reasons to implement FILESTREAM would be because your documents are generally larger than 1MB in size, storing them outside the database can have a performance advantage. If these are TIFF documents, then this 1MB threshold would be on a per-page basis. This is due to how ILINX Content Store stores TIFF documents. By design, ILINX Content Store splits multipage TIFFs into single pages to allow for users to perform actions on single pages of a document: things like a reorder of pages, single page delete, or rotation.
I recently worked with a customer who scans their documents in as 400DPI color or grayscale TIFF. These documents are generally over 1MB per page and it makes sense to utilize SQL FILESTREAM to store these documents outside of the database and on the file system. With the documents stored in their database, their database would quickly grow to multiple terabytes in size and retrieval could be slower for these 100MB + documents.
One thing to note, is that it is best to make the decision on whether or not FILESTREAM will be utilized before the ILINX Content Store application is in use. Enabling FILESTREAM for an application without documents in it is a very fast process but enabling it for an existing application can take days or weeks. This is because of the file copy that has to occur to migrate the content from in-SQL to outside-SQL.
Step By Step – Activating FILESTREAM
To enable FILESTREAM for an ILINX Content Store application, follow the steps below. Please note that these steps are specific for SQL Server 2008 or 2012. The ILINX Content Store application in the example is named “Sample Application”.
- Enable FileStream for the SQL Service via SQL Server Configuration Manager
a. Open the SQL Server Configuration Manager, Start > All Programs > Microsoft SQL Server (2008 or 2012) > Configuration tools > SQL Server Configuration Manager
b. Right-click on “SQL Server Service” and select “open”
c. In the right pane right-click on “SQL Server(instance name)” and select “properties”
d. Click on the “FILESTREAM” Tab and check the box to “Enable FILESTREAM for Transact-SQL Access”
- Enable FILESTREAM on the SQL server instance via SQL Server Management Studio
a. Open SQL Server Management Studio
b. Right-click on the SQL server instance after logging in and select “properties”
c. Click on the “Advanced” option on the left
d. Under FILESTREAM change the “FILESTREAM Access Level” to “Transact-SQL access enabled”
- Once FILESTREAM is enabled, the SQL Server instance must be restarted.
- Add the FILESTREAM Filegroup to the ILINX Content Store database
a. Right-click on the database you are setting up FILESTREAM on and select “properties”
b. Select the “Filegroups” option on the left
c. The bottom group is named FILESTREAM, select “Add”
d. Type in a name for your FILESTREAM Filegroup (In the below example I simply used the name “FILESTREAM”) you also have the option to set this group to read-only at this time.
- Create a local directory for FileStream data on disk. I used E:\FILESTREAM. The disk must be local to the SQL server machine, UNC paths are not supported.
- Modify database to be able to use the FILESTREAM Filegroup:
a. ALTER DATABASE ILINX ADD FILE (NAME = FILESTREAM, FILENAME = ‘E:\FILESTREAM\ILINX’) TO FILEGROUP FILESTREAM - Now that FILESTREAM is enabled for both the server and database, we must modify the ILINX Content Store application table to be able to support FILESTREAM. There are requirements that a table using FILESTREAM has a column in it that is unique and has the SQL rowquid designation. ILINX already has a unique column (ICS_FileID), we just need to tell SQL to use it by setting RowGuid value to Yes on ICS_FileID and setting it as the unique column:
a. alter table [Sample Application] alter column ICS_FileID add rowguidcol
b. alter table [Sample Application] add unique (ICS_FileID) - The next step involves creating a new column for the binary data that includes the FILESTREAM attribute. If there are already documents in the system, run the query that doesn’t have the ‘not null’ specification:
a. alter table [sample application] add BITS1 varbinary(max) FILESTREAM OR alter table [sample application] add BITS1 varbinary(max) FILESTREAM not null default cast(‘0’ as varbinary(max)) - This next step copies the data from in-SQL storage column to the new FILESTREAM column created in the last step. This will copy all the documents out of the database and on to the file system. If performing this on an existing application, it is worth noting that you have duplicate data between the following steps and you must be sure that the server has enough storage to support this:
a. update [sample application] set BITS1 = BITS - Now that all the documents/data are in the FILESTREAM column and out on disk, you can delete the in-SQL storage column. Only do this once the copy is completed:
a. alter table [sample application] drop column BITS - For the last step, rename FILESTREAM column to the ContentStore required name of ‘BITS’
a. exec sp_rename ‘dbo.[sample application].BITS1’, ‘BITS’, ‘COLUMN’
Following the above steps will enable FILESTREAM for an application in ILINX Content Store. SQL will manage the file storage and all documents added to the system will now be stored on disk rather than in-SQL.
John Linehan
Senior Systems Engineer
ImageSource, Inc.
