- Structured Data is data which is stored in a relational database
- Semi Structured Data is often of xml type and can be stored in the database
- Unstructured Data is such as images, video etc
Filestream allows unstructed data to be stored in the NTFS file system in directories called data containers which are listed as filegroups in the database. This allows transactional consistancy between structured and unstructured data and allows point in time recovery. Deleting or renaming or any filestream files directly in the file system will corrupt the database. Each row has a unique row ID. Fragmentation can occur on the NTFS file system and regular defragmentation is required. Streaming of data through the filestream is more efficent than through the conventional database set up however backing up the database with filestream files is slower than just backing up the database. Each Filestream data container may need to be on it’s own volume to stop data contention. Data stored on the NTFS file system can be compressed but it is expensive if the data is uncompressable. Filestream requires integrated security and filestream cannot block antivirus scanning. If a file is infected it is best to quarantine the file and use DBCC CHECKDB to identify the missing file. Filestream has a garbage collection process to remove files no longer required, which is automatic, when the checkpoint process runs.
Filestream has to be enabled at the windows level before the SQL Server level.
Filestream data can not be encrypted. Database Mirroring does not support filestream although failover clustering does.