Saturday, 13 February 2010

FILESTREAM Storage in SQL Server 2008.

Came across this white paper on Filestream storage in SQL Server 2008 – MSDN
In brief,
We often tend to have BLOB’s aka Binary Large Objects which takes memory storages ranging from MBs to GBs which may be or generally will be Images, Steaming Video , Sound etc.
Using FILESTREAM storage, we can take advantage of the NTFS file system. FILESTREAM storage can be used to store BLOB data using a combination of SQL Server 2008 and NTFS file system.
Good to know
1] Dual Programming Model Access to BLOB Data
- Transact-SQL Access
- Win32 Streaming Access
2] Replication
FILESTREAM is supported by both transactional replication and merge replication, But there are limitations on the ‘size’ of data that can be sent to down level instances.
3] Database snapshots
SQL Server does not support database snapshots for FILESTREAM data containers.
4]  Indexes on FILESTREAM data not allowed
5] FILESTREAM works on all recovery models (full, differential and log)
Something to note from the Conclusion
“FILESTREAM storage is not appropriate in all cases. Based on prior research and FILESTREAM feature behaviour, BLOB data of size 1 MB and larger that will not be accessed through Transact-SQL is best suited to storing as FILESTREAM data.”
Can you spot a tool requirement in the whitepaper ? , Well, A common scenario using SQL Server 2008 may be migrating existing BLOB data into FILESTREAM storage.There goes, A Tool to do exactly that will be a killer.

No comments:

Post a Comment