Transactional replication supports publishing LOBs and performs partial updates on LOB columns: if a LOB column is updated, only the fragment of data changed is replicated, rather than all the data in the column.
If a published table includes any LOBs, consider using the following Distribution Agent parameters: -UseOledbStreaming, -OledbStreamThreshold, and -PacketSize. The most straightforward way to set these parameters is to use the Distribution Agent profile titled Distribution Profile for OLEDB streaming. For more information, see Replication Agent Profiles. In addition to this predefined profile, you can specify the parameter in an agent profile you create or modify, or on the command line. For more information, see:
text, ntext and image Data Types
The process of replicating text, ntext and image data types in a transactional publication is subject to a number of considerations. We recommend that you use the data types varchar(max), nvarchar(max), varbinary(max) instead of text, ntext, and image data types, respectively.
If you do use text, ntext, or image, be aware of the following:
-
WRITETEXT and UPDATETEXT statements should be wrapped in explicit transactions.
-
Logged text operations can be replicated by using WRITETEXT and UPDATETEXT with the WITH LOG option on published tables. The WITH LOG option is required because transactional replication tracks changes in the transaction log.
-
UPDATETEXT operations can be used only if all Subscribers are running SQL Server. WRITETEXT operations are replicated as UPDATE statements, so they can also be used with non-SQL Server Subscribers.
-
A configurable parameter, max text repl size, controls the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), and image data that can be replicated. This permits support of: ODBC drivers and OLE DB providers; instances of SQL Server Database Engine that cannot handle large values for these data types; and Distributors that have system resource (virtual memory) constraints. When a column with one of these data types is published and an INSERT, UPDATE, WRITETEXT, or UPDATETEXT operation is run that exceeds the configured limit, the operation fails.
Use the sp_configure (Transact-SQL) system stored procedure to set the max text repl size parameter.
-
When publishing text, ntext, and image columns, the text pointer should be retrieved within the same transaction as the UPDATETEXT or WRITETEXT operation (and with read repeatability). For example, do not retrieve the text pointer in one transaction and then use it in another. It might have moved and become invalid.
In addition, 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:
-
Begin the transaction.
-
Obtain the text pointer using the TEXTPTR() function with REPEATABLE READ isolation level.
-
Use the text pointer in the UPDATETEXT or WRITETEXT operation.
-
Commit the transaction.
.gif) Note: |
|---|
|
If you do not obtain the text pointer in the same transaction, modifications are allowed at the Publisher, but changes are not published to Subscribers.
|
For example:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
DECLARE @mytextptr varbinary(16)
SELECT @mytextptr = textptr(Notes)
FROM Employees
WHERE EmployeeID = '7'
IF @mytextptr IS NOT NULL
BEGIN
UPDATETEXT Employees.Notes @mytextptr 0 NULL 'Terrific job this review period.'
-- Dummy update to fire trigger that will update metadata and ensure the update gets propagated to other Subscribers.
UPDATE Employees
-- Set value equal to itself.
SET Notes = Notes
WHERE EmployeeID = '7'
END
COMMIT TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
| Note: |
|---|
|
This example is based on the Northwind database, which is not installed by default. For information about installing this database, see Northwind and pubs Sample Databases at the Microsoft Download Center.
|
A consideration when sizing Subscriber databases is that the text pointer for replicated text, ntext, and image columns must be initialized on Subscriber tables, even when they are not initialized on the Publisher. Consequently, each text, ntext, and image column added to the Subscriber table by the distribution task consumes at least 43 bytes of database storage even if the contents are empty.