Just found out that the 25-page FILESTREAM whitepaper I wrote recently for the SQL team has been published on MSDN.
You can get it at https://msdn.microsoft.com/en-us/library/hh461480.aspx.
Enjoy!
Here’s the table of contents.
- Introduction
- Choices for BLOB Storage
- Overview of FILESTREAM
- Dual Programming Model Access to BLOB Data
- When to Use FILESTREAM
- Configuring Windows for FILESTREAM
- Hardware Selection and Configuration
- Physical Storage Layout
- RAID Level Choice
- Drive Interface Choice
- NTFS Configuration
- Optimizing NTFS Performance
- Cluster Size
- Managing Fragmentation
- Compression
- Space Management
- Security
- Antivirus Considerations
- Enabling FILESTREAM in Windows
- Configuring SQL Server for FILESTREAM
- Security Considerations
- Enabling FILESTREAM in SQL Server
- Creating a Database Enabled for FILESTREAM
- Creating a Table for Storing FILESTREAM Data
- Configuring FILESTREAM Garbage Collection
- Partitioning Considerations
- Load Balancing of FILESTREAM Data
- Feature Combinations and Restrictions
- Performance Tuning and Benchmarking Considerations
- Data Migration Considerations
- FILESTREAM Usage Best Practices
- Conclusion
7 thoughts on “SQL Server 2008 FILESTREAM whitepaper published on MSDN”
Great article. I’ve been working with some companies using the filing system for large volume of XML messaging.
Not quite file stream – but the NTFS recommendations really hit the spot. Cheers ;-)
Thanks for the white paper!
I had a question regarding partial updates, but first a little background.
We currently use document management software that stores all files as BLOBs in SQL 2000. When user’s access files for editing (i.e. Check Out), the BLOB data first get streamed out of SQL to their personal network folder. All editing to the file takes place here using the associated software. Upon Check In, the reverse operation takes place with the following two options:
1) Create New Version – This creates a completely new record with the newly edited file contents in the BLOB field. This is version 2 of the file. The GUID of both files remains the same.
2) Maintain Existing Version – This overwrites the latest contents/version of the file in the BLOB field. The file version remains 1.
Using SQL 2008 and FILESTREAM, would we still be able to accomplish both scenarios above without creating more files than we need? By "partial update" do you mean, in-place updating only (ie in the BLOB itself), or will any external editing always force a new FILESTREAM file upon Check In?
Hi Mark – you can accomplish scenario 1 no problem. Scenario 2 isn’t possible without causing a whole new FILESTREAM file to be generated, no matter how the existing value is updated, or how much of it is updated. The old file will eventually be removed by the garbage collection process, but the whole new file will be backed up by transaction log backups – not ideal.
Thanks
Good whitepaper Paul.
I wanted to make the following observation. Up until now whenever a server blue screens I have always stated that SQL Server cannot cause a blue screen because it runs entirely in user mode however; with the inclusion of a File System Filter Driver with Filestream I guess we can no longer be 100% certain.
Yes, that’s right.
HI Paul,We are at verge of deciding between NTFS storing of files vs SQL server Filestream, We are very much concern about the downtime comparison between Manual file saving(NTFS) and SQL server Fielstream Data type.
Can you please put some light on it.
1.Compare NTFS (Manual File system Saving ) Vs SQL Server filestream for Read operation.
2.Does update Operation on the row containing the filestream column also get affected even if filestream data is not changed.
1) I have no data on this
2) No