To configure FILESTREAM in SQL Server, follow these steps. FILESTREAM allows you to store large binary data (like documents, images, videos) in the file system while maintaining transactional consistency with the database.
Step-by-Step: Enable and Configure FILESTREAM
1. Enable FILESTREAM at the SQL Server Instance Level
- Open SQL Server Configuration Manager.
- Go to SQL Server Services.
- Right-click your SQL Server instance → Properties.
- Go to the FILESTREAM tab:
- Check Enable FILESTREAM for Transact-SQL access.
- Optionally check Enable FILESTREAM for file I/O streaming access.
- Provide a Windows share name (e.g., SQLFileStreamShare).
- Click Apply and restart the SQL Server service.
2. Enable FILESTREAM Access at the Database Level
- 0 = Disabled
- 1 = T-SQL access only
- 2 = T-SQL and Win32 streaming access
3. Create a Database with FILESTREAM
4. Create a Table with FILESTREAM Column
Note: The table must have a ROWGUIDCOL column with a unique constraint to support FILESTREAM.
Tips
- FILESTREAM is best for storing large objects (>1MB).
- It integrates with the NTFS file system for efficient storage and access.
- Backups include FILESTREAM data unless you exclude it explicitly.
From my SQL Server DBA handbook.