In previous two posts, (comparison with HA and other features) we looked at the new features compatibility with other existing features like Replication, Database Mirroring, Database Snapsho, etc.. Now we look at their compatibility with each other.

• Can you configure FILESTREAM filegroup on a database set of Compression?
• Is Compression allowed on a database encrypted with TDE?
• Can you create FILESTREAM filegroups on a database encrypted with TDE?

Lets look at the matrix below for answers:

As you can see, TDE is not compatible with either Compression or FILESTREAM. A database set of Compression cannot be encrypted with TDE and vice versa. Since Compression requires Disk Cluster size to be 4096 bytes you cannot compress FILESTREAM data. For FILESTREAM data, the recommended Disk Cluster is 64K. I would recommend any one implementing FILESTREAM to read Paul S. Randal’s (SQLskills.com) article on MSDN

“The recommendation for using FILESTREAM is that the individual units of BLOB data be 1 MB in size or higher. If this is the case, it is recommended that the NTFS cluster size for the FILESTREAM data container volume is set to 64 KB to reduce fragmentation. This must be done manually as the default for NTFS volumes up to 2 terabytes (TB) is 4 KB. This can be done using the /A option of the format command. For example, at a command prompt type:

          format F: /FS:NTFS /V:MyFILESTREAMContainer /A:64K”

Why is it this way and all other implementation details will be covered in future articles.

Hope this helps,
_SqlTimes