Topic: Document Storage Management for PCMS
To: Development Team
Dated: 3rd March 2010
To do the analysis for the large file storage in MS SQL Database.
PCMS has many documents that needs to be uploaded corresponding to Job Cards in all the modules. As volume of documents is increased with passage of time, so it causes major development and operational overheads. It grows more then GBs with in few months of period.
A study was conducted few months back; to adopt a third party file system to maintain documents out side the actual database. Many solutions were analyzed but no concrete option was able to qualify all the selection parameters like security, access speed, storage efficiency and operational management.
Microsoft has provided a native solution to this problem. They have merged the benefit of file storage and Database storage under one umbrella with technology named as Filestream.
Large file storage is managed via Filestream.
FILESTREAM Definition: FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system.
To specify that a column should store data on the file system, specify the FILESTREAM attribute on a varbinary(max) column. This causes the Database Engine to store all data for that column on the file system, but not in the database file.
Application Design and Implementation
• When you are designing and implementing applications that use FILESTREAM, consider the following guidelines:
• Use NULL instead of 0x to represent a non-initialized FILESTREAM column. The 0x value causes a file to be created, and NULL does not.
• Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application’s performance to decrease over time.
• In applications that use replication, use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID() performs better than NEWID() for GUID generation in these applications.
• The FILESTREAM API is designed for Win32 streaming access to data. Avoid using Transact-SQL to read or write FILESTREAM binary large objects (BLOBs) that are larger than 2 MB. If you must read or write BLOB data from Transact-SQL, make sure that all BLOB data is consumed before you try to open the FILESTREAM BLOB from Win32. Failure to consume all the Transact-SQL data might cause any successive FILESTREAM open or close operations to fail.
• Avoid Transact-SQL statements that update, append or prepend data to the FILESTREAM BLOB. This causes the BLOB data to be spooled into the tempdb database and then back into a new physical file.
• Avoid appending small BLOB updates to a FILESTREAM BLOB. Each append causes the underlying FILESTREAM files to be copied. If an application has to append small BLOBs, write the BLOBs into a varbinary(max) column, and then perform a single write operation to the FILESTREAM BLOB when the number of BLOBs reaches a predetermined limit.
• Avoid retrieving the data length of lots of BLOB files in an application. This is a time-consuming operation because the size is not stored in the SQL Server Database Engine. If you must determine the length of a BLOB file, use the Transact-SQL DATALENGTH() function to determine the size of the BLOB if it is closed. DATALENGTH() does not open the BLOB file to determine its size.
If an application uses Message Block1 (SMB1) protocol, FILESTREAM BLOB data should be read in 60-KB multiples to optimize performance.
When to Use:
The size and use of the data determines whether you should use database storage or file system storage.
• Objects that are being stored are, on average, larger than 1 MB.
• Fast read access is important.
• You are developing applications that use a middle tier for application logic.
• For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.
• The sizes of the File system based BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.
Because of wordpress restriction, after downloading ‘final-filestream.doc’ , rename it to final-filestream.rar and then extract and enjoy code.