text and image Replication

The process of replicating text and image datatypes is subject to the following considerations:

• INSERT, UPDATE, and DELETE statements are supported.
• Logged text operations can be replicated by using WRITETEXT and UPDATETEXT with the WITH LOG option on tables that are published for replication. A text or image column that is published for replication using WRITETEXT and UPDATETEXT operations with the WITH NO_LOG option are not supported, because the replication process monitors the transaction log.
• UPDATETEXT operations can be performed for Microsoft SQL Server 6.0 and later subscribers (not for other SQL Server subscribers). Also, the UPDATETEXT option will fail at the publisher if an ODBC subscriber has a subscription on the text or image column.
• WRITETEXT operations are replicated as UPDATE statements, enabling replication of WRITETEXT to ODBC subscribers as well as to SQL Server. (UPDATETEXT operations are replicated as UPDATETEXT.)
• Custom procedures are not used if multiple text columns are being modified, because the other text column values are not logged. A standard UPDATE statement is generated instead.
• A configurable parameter, max text repl size, controls the maximum size (in bytes) of text and image data that can be replicated. This permits support of ODBC drivers and other SQL Server databases that cannot handle large text and image values, and distributors that have system resource (virtual memory) constraints. When a text or image column is published and an INSERT, UPDATE, WRITETEXT, or UPDATETEXT operation is run that exceeds the configured limit, the operation fails.

  The max text repl size parameter is set by using the sp_configure system stored procedure or the SQL Enterprise Manager Server Configuration dialog box.

  For more information on setting max text repl size, see sp_configure.

• Because SQL Server is log-based, for text and image columns to be replicated, the text pointer must be retrieved within the same transaction as the UPDATETEXT or WRITETEXT operation.

  Also, once the text pointer has been obtained, you should not perform any operations that can alter the location of the text pointed to by the text pointer (such as updating the primary key), before exectuing the UPDATETEXT or WRITETEXT statement.

  This is the recommended way of using UPDATETEXT and WRITETEXT operations with data to be replicated:

  1. Begin the transaction.
  2. Obtain the text pointer.
  3. Use the text pointer in the UPDATETEXT or WRITETEXT operation.
  4. Commit the transaction.
• To replicate a text or image column, publish the table using SQL Enterprise Manager and include the text or image column in the published article. For more information about publishing tables and columns, see the Microsoft SQL Server Administrator's Companion.