Implementing SQL FILESTREAM Part II

Last month I wrote about enabling SQL FILESTREAM with ILINX Content Store. After discussing this with a few people, I think I should share some more information and reiterate a couple points.

For Existing Applications:
As I mentioned before, the decision to enable FILESTREAM should be done during the planning phase. If you perform this process on an application with a lot of content, it can be a very time costly endeavor with a big performance impact to the server. Also, after the move from BLOB to FILESTREAM, you could have a fragmented database. The BLOB to FILESTREAM process can definitely be done on an existing system, just be sure to plan accordingly and allow for sufficient time.

After step #10 of my previous blog post (all the data is copied and you have deleted the BLOB column), you will notice that the database file size hasn’t decreased. This is remedied easily enough be executing a DBCC CLEANTABLE command. The DBCC CLEANTABLE command will reclaim the space from the dropped variable length column. For example, if your database is named ILINX_CS and your application is named Sample Application, the query to do this is:

DBCC CLEANTABLE ('ILINX_CS','[dbo].[Sample Application]',10000)

After all this done, you may have a need to shrink the database. Shrinking a database is generally frowned upon because of the fragmentation it causes, but in the case of moving BLOBs to FILESTREAM, you may need that disk space. After the database shrink, be sure to rebuild all your indexes.

Some Best Practices:
Before you even enable FILESTREAM, there are a number of best practices to follow to get the best performance from your system:

  • Keep your FILESTREAM data on a different disk than the database and logs.
  • Disable indexing on the FILESTREAM volume.
  • Disable 8.3 naming on the FILESTREAM volume.
    • This can be done in the registry or by executing an FSUTIL command.
  • Disable the file access attribute so the system has less work to do when these files are accessed.
    • This can be done with an FSUTIL command and will require a system reboot so plan accordingly.
    • This may have an adverse reaction for your backup solution, so research that first.
  • Disable Anti-Virus software on the FILESTREAM data.
    • This is generally accomplished by setting up an exclusion in your AV solution.
  • Make sure your database does not have Auto-Shrink enabled.
    • This should always be the case unless you have a specific need.
  • Make sure your database does not have Auto-Close enabled.
    • This should always be the case unless you have a specific need.

And finally, if you have a high-availability system, it should be noted that database mirroring is not supported for a FILESTREAM enabled database. Because it isn’t a best practice to have virtualized SQL servers, this can be a bit of a pain point. If you need a high-availability ILINX system and are implementing FILESTREAM for the content, explore the implementation of AlwaysOn Availability Groups.

The above items are just a few more things to consider before implementing FILESTREAM for your ILINX Content Store system.

John Linehan
Sr. Systems Engineer
ImageSource, Inc.

John Linehan