I’m developing a new module called “File Cabinet” that is a central repository for storage and distribution of text and image files.
By utilizing the FILESTREAM attribute of the varbinary(MAX) data type in Microsoft SQL Server 2008 (any edition) we are actually storing the binary file information directly to an NTFS store configured on the same machine as the SQL Server instance. Studies have shown that data under 256k is better stored and accessed through the SQL Server API in a varbinary(MAX) field and that blob data larger than 1MB performs better when streamed from an NTFS store. FILESTREAM allows you to access your larger files more efficiently so it doesn’t have to eat up the buffer in your database and cause massive slowdowns. The system doesn’t work well with numerous read/writes because it completely replaces the blob data each time, leading to data fragmentation. Luckily most of our correspondence is set it and forget it.
Here is the initial list of supported file formats:
- Plain text files (.txt)**
- Rich text files (.rtf)**
- Adobe Acrobat (.pdf)*
- All image files (.jpg/.jpeg/.bmp/.gif/.png/.tif)*
- MS Office Excel (.xls / .xlsx)
- MS Office Word (.doc/.docx)
* Viewable from within the application
** Editable and viewable from with the application
It will feature:
- drag and drop storage or viewing of documents/images/text from the file system or the clipboard
- variable replacement based on database values or business logic
- multiple points of integration: depending on the command line, the file cabinet will be specific to a user, shipment, or account
- assignment of viewing/editing rights to other users
Our previous version of this product was the “Correspondence Manager” which stored paths to file locations but still offered variable replacement and rich text editing.
