FILESTREAM is a Microsoft product that enables SQL Server-based applications to store unstructured data, such as documents and images, in the file system. With FILESTREAM, applications can leverage the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
Like most data-intensive applications, the Integrity database should be stored on a separated volume and not on the same volume with the operating system, SQL Server database, SQL Server log, tempdb, or paging file.
Microsoft also recommends the following best practices for FILESTREAM:
-
Always create the FILESTREAM data container that uses the FILESTREAM enabled database in a separate volume.
-
Use RAID 5 + stripping for the FILESTREAM data container as it provides excellent read, write, and fault tolerance. Follow this recommendation even though this is an expensive configuration.
-
It is a good practice to periodically perform disk defragmentation of the volumes which store FILESTREAM data.
-
Disable indexing on FILESTREAM volumes.
-
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage by running the following command at a command prompt:
FSUTIL BEHAVIOR SET DISABLE8DOT3 1
-
Disable last file access time tracking in NTFS volumes used for FILESTREAM data storage by running the following command at a command prompt.
FSUTIL BEHAVIOR SET DISABLELASTACCESS 1
-
Disable antivirus software from scanning FILESTREAM volumes if it's not mandatory within your organization. If antivirus scanning is mandatory, then set the policies to not automatically delete offending files. Deleting of these files leads to corruption of your FILESTREAM enabled database.
-
To reduce disk fragmentation, ensure disk volumes used to store FILESTREAM data get formatted with a 64K allocation. The default NTFS allocation is 4K.
-
Disable Auto Shrink and Auto Close options on FILESTREAM enabled databases to avoid disk fragmentation and poor performance.