FILESTREAM and partitioned storage

Question

I have a question about the current implementation of the FILESTREAM type for SQL Server 2008. Say I have a table that stores my documents using FILESTREAM. After a year, I want to archive (move) off a number of these documents to a different server. Is it possible to have FILESTREAM documents on two separate drives? Or do they have to all be on the same drive?

Assuming the answer is yes, can someone post the T-SQL that would be used to create this table that uses two different locations for storage of the FILESTREAM data?

Answer 1

Yes, you can store documents of VARBINARY(MAX) type (with the FILESTREAM storage attribute) on multiple drives. All you need to do is to partition the table across multiple filestream file groups. Each of these file groups may have a filestream container on a different disk.

See here for general information on partitioning: http://msdn.microsoft.com/en-us/library/ms188730.aspx

A great white paper that mentions filestream-specific partitioning: http://msdn.microsoft.com/en-us/library/cc949109.aspx