Is file stream work with Merge replication?

Question

Hi,

A new technology called FileStream in SQL 2008.

Does this technology work when the database is published with Merge
replication?

Do all subcriptions get a replicated copy on disk?

Answer 1

The short answer is Yes, Filestream and merge replication technologies do work with each other, but there are a few items you need to consider ; 

If you use FILESTREAM columns in tables that are published for merge replication, note the following considerations:

• Both merge replication and FILESTREAM require a column of data type uniqueidentifier to identify each row in a table. Merge replication automatically adds a column if the table does not have one. Merge replication requires that the column have the ROWGUIDCOL property set and a default of NEWID() or NEWSEQUENTIALID(). In addition to these requirements, FILESTREAM requires that a UNIQUE constraint be defined for the column. These requirements have the following consequences:

  • If you add a FILESTREAM column to a table that is already published for merge replication, make sure that the uniqueidentifier column has a UNIQUE constraint. If it does not have a UNIQUE constraint, add a named constraint to the table in the publication database. By default, merge replication will publish this schema change, and it will be applied to each subscription database. For more information about schema changes, seeMaking Schema Changes on Publication Databases.

    If you add a UNIQUE constraint manually as described and you want to remove merge replication, you must first remove the UNIQUE constraint; otherwise, replication removal will fail.

  • By default, merge replication uses NEWSEQUENTIALID() because it can provide better performance than NEWID(). If you add a uniqueidentifiercolumn to a table that will be published for merge replication, specify NEWSEQUENTIALID() as the default.

• Merge replication includes an optimization for replicating large object types. This optimization is controlled by the @stream_blob_columns parameter ofsp_addmergearticle. If you set the schema option to replicate the FILESTREAM attribute, the @stream_blob_columns parameter value is set to true. This optimization can be overridden by using sp_changemergearticle. This stored procedure enables you to set @stream_blob_columns to false. If you add a FILESTREAM column to a table that is already published for merge replication, we recommend that you set the option to true by using sp_changemergearticle.

• Enabling the schema option for FILESTREAM after an article is created can cause replication to fail if the data in a FILESTREAM column exceeds 2 GB and there is a conflict during replication. If you expect this situation to arise, it is recommended that you drop and re-create the table article with the appropriate FILESTREAM schema option enabled at creation time.

• Merge replication can synchronize FILESTREAM data over an HTTPS connection by using Web Synchronization. This data cannot exceed the 50 MB limit for Web Synchronization; otherwise, a run-time error is generated.

These considerations are for SQL Server 2008, you will find similar list for 2008R2 and 2012 at this link to a Microsoft techNet article on this topic : http://technet.microsoft.com/en-us/library/bb895334(v=sql.100).aspx#Replication 

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 2

Thanks sanil,

I have set up Merge replication pull subscription for SQL server2008 r2 enterprise edition as publisher & two subscribers having express editiion.  But my client  needs solution for using FileStream in Merge Replication. how to use it & what are the prons & cause of this.

Answer 3

Hi M-DEW,

You can refer to this blog:

Example using Transactional Replication with Filestreams in SQL 2008

http://blogs.msdn.com/b/chrissk/archive/2009/02/08/example-using-transactional-replication-with-filestreams-in-sql-2008.aspx

this example is similar to Merge Replication.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Thanks for all you guys.

Finally I have done my job.

I have created one FILESTREAM database & table. & successfully replicate it to other server

using pull subscription on demand synchronization.