none
Merge replication copies Filestream (large blobs) to subscriber or just referenced? RRS feed

  • Question

  • Hi All,

    I am exploring SQL server replication feature. After reading through few articles and blogs, I am sure that I will be using merge replication for my work. However, I could not figure out whether merge replication actually copies Filestream data from publication to a subscriber or just gets referenced?

    Thanks for your help in advance.

    -- Tejas

    Tuesday, October 10, 2017 2:55 PM

All replies

  • As long as the subscriber is enabled for file stream and the subscription database is configured for filestream and your publication is replicated the file stream columns as filestream (not converting them to varbinary(max), the in the file stream directory will be copied over to the filestream directories on the subscriber.

    So - the data is copied, the references will be local to the subscriber.

    I hope this answers your question.

    Tuesday, October 10, 2017 3:56 PM
    Moderator
  • That makes sense. Thank you for your response, Hilary.

    Another quick question, assume that you have an image stored as VARBINARY(MAX) FILESTREAM in a table which will get replicated to a subscriber (publisher and subscriber both database is configured and have Filestream enabled). I was wondering if there is any way I can find that image file into subscriber machine?

    Example: 

    A publisher has a database named as FilestreamTestDB and contains a very basic table named as ContactInfo.

    CREATE TABLE ContactInfo(
       [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
       [Firstname] VARCHAR(30),
       [Lastname] VARCHAR(30),
       [Address] VARCHAR(100),
       [Phone] VARCHAR(50),
       [Image] VARBINARY(MAX) FILESTREAM NULL
    )

    I used following query to insert a record into Publisher's database

    DECLARE @img AS VARBINARY(MAX)
    SELECT @img = CAST(<g class="gr_ gr_532 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace gr-progress" data-gr-id="532" id="532">bulkcolumn</g> AS VARBINARY(MAX))
          FROM OPENROWSET( BULK 'C:\demo\test.jpg', SINGLE_BLOB ) AS x

    INSERT INTO [dbo].[ContactInfo] (ID, Firstname, Lastname, Address, Phone, Image)
    SELECT NEWID(), 'fn1','ln1', 'a1', 'p1', @img

    After merge replication, I could see a new record at subscriber's database. I was wondering if there is any way I can find 'C:\demo\test.jpg' image file in subscriber's machine?

    Tuesday, October 10, 2017 5:40 PM
  • I don't know how to do that correlation, nor do I believe that there is a supported way of getting that information.
    Tuesday, October 10, 2017 5:42 PM
    Moderator