locked
FILESTREAM RRS feed

  • Question

  • I am planning to upgrade a database applicaton that uses merge replication. The table currently has a column type of UNIQUEIDENTIFIER and has a default contraint that uses the NEWID() function.

    A new version of the application requires that the FILESTREAM  data type be added to a table in the database. The data type will be used to store binary files. Some of the files will be larger than 2 GB  in size.

    Whiles testing the upgrade, i discover that replication fails on the articles that contains the FILESTREAM data. You find out that the failure when a file object is larger than 2 GB.

    QUESTION: Which is the best option and why?

    A. Drop and create the table that will use the FILESTREAM data type

    B. Change the Default contraint to use the NEWSEQUENTIALID() function

    C. Place the table that will contain the FILESTREAM data type on a seperate filegroup

    D. Use the sp_changemergearticle stored procedures and set the @stream_blob_columns option to true for the table that  will use the FILESTREAM data type.


    get it right always
    Monday, May 9, 2011 5:24 PM

Answers

  • I think the answer is D
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Wednesday, May 11, 2011 1:38 AM
  • Hi Basit59,

    In addition please refer to the FILESREAM Best Practice for more details.

    Hope this helps


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, May 11, 2011 7:36 AM
    Answerer
  • Wow - this is a tough one. I think all these answers could be chosen, however, I personally think that 'A' is the BEST choice. Here is why I say that. Look at the website (http://msdn.microsoft.com/en-us/library/bb895334.aspx) under Replication towards the bottom. It's the 4th bullet point under Considerations for Merge Replication:

     

    It states:

    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.

     

    So, according to the question/situation we found out that the failure occures when a file object is larger than 2 GB - and that why I believe the answer is 'A'.

     

    Please respond with comments - I don't like being wrong but I can admit when I am ;-)


    • Marked as answer by basit59 Tuesday, May 24, 2011 2:47 PM
    Tuesday, May 24, 2011 1:39 PM

All replies

  • I think the answer is D
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Wednesday, May 11, 2011 1:38 AM
  • Hi Basit59,

    In addition please refer to the FILESREAM Best Practice for more details.

    Hope this helps


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, May 11, 2011 7:36 AM
    Answerer
  • Wow - this is a tough one. I think all these answers could be chosen, however, I personally think that 'A' is the BEST choice. Here is why I say that. Look at the website (http://msdn.microsoft.com/en-us/library/bb895334.aspx) under Replication towards the bottom. It's the 4th bullet point under Considerations for Merge Replication:

     

    It states:

    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.

     

    So, according to the question/situation we found out that the failure occures when a file object is larger than 2 GB - and that why I believe the answer is 'A'.

     

    Please respond with comments - I don't like being wrong but I can admit when I am ;-)


    • Marked as answer by basit59 Tuesday, May 24, 2011 2:47 PM
    Tuesday, May 24, 2011 1:39 PM