SQL Server 2008 Replication & Filestream

  • Article

Ever wondered whether filestream will be supported with replication? Well before I give the answer to that, let me just give you a brief background on FILESTREAM:

Much of the data that is created every day is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

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. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

When to Use FILESTREAM?

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

  • 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.
     

Filestream and Replication

Yes replication of filestream data is supported. For more information see BOL topics:  

https://msdn2.microsoft.com/en-us/library/bb895334(SQL.100).aspx#Replication

 

https://msdn2.microsoft.com/en-us/library/ms186225(SQL.100).aspx

 

Note: FILESTREAM is not limited to 4GB, it is limited only by the volume size of the files systems, and this applied in replication as well.