You are planning to upgrade a database application that uses merge replication. The table currently has a column type of UNIQUEIDENTIFIER and has a DEFAULT constratin that uses the NEWID() function…

Question

Options

• ADrop and recreate the table that will use the FILESTREAM data type.
• BChange the DEFAULT constraint to use the NEWSEQUENTIALID() function.
• CPlace the table that will contain the FILESTREAM data type on a separate filegroup.
• DUse the sp_changemergearticle stored procedure and set the @stream_blob_columns

• A
  14% (9)
• B
  3% (2)
• C
  8% (5)
• D
  75% (49)

Explanation

Considerations for Merge Replication 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, see Making 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 uniqueidentifier column 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 of sp_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.

No community discussion yet for this question.