Provide support for text and image Data Types in Transactional Replication

The process of replicating text and image data types in a transactional publication 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™ version 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.

• 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, when 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 executing 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.

  ---

  Note If you do not obtain the text pointer in the same transaction, modifications are allowed at Publisher, but changes are not replicated to Subscribers.

  ---

• To replicate a text or image column, publish the table using SQL Server Enterprise Manager and include the text or image column in the published article.

An important consideration when sizing subscribing databases is that the text pointer for replicated text and image columns will be initialized on Subscriber tables, even when uninitialized on the Publisher. Consequently, each text and image column added to the Subscriber table by the distribution task will consume approximately 43 bytes of database storage.

See Also