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) Continue reading