Considerations for Transactional Replication

Article
12/03/2008

There are a number of considerations for transactional replication:

Transaction log space.
Disk space for the distribution database.
Primary keys for each published table.
Triggers.
Large object (LOB) data types.
Updatable subscriptions (if they are used). For more information about considerations for updatable subscriptions, see Updatable Subscriptions for Transactional Replication.

Transaction Log Space

For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been delivered to the distribution database. We recommend that you set the transaction log file to auto grow so that the log can accommodate these circumstances. For more information, see CREATE DATABASE (Transact-SQL) and ALTER DATABASE (Transact-SQL).

We recommend that you set the sync with backup option on the distribution database, which delays the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up. This can result in a larger transaction log in the publication database. For more information about this option, see Strategies for Backing Up and Restoring Snapshot and Transactional Replication.

Disk Space for the Distribution Database

Ensure that you have enough disk space to store replicated transactions in the distribution database:

If you do not make snapshot files available to Subscribers immediately (which is the default): transactions are stored until they have been replicated to all Subscribers or until the retention period has been reached, whichever is shorter.
If you create a transactional publication and make the snapshot files available to Subscribers immediately: transactions are stored until they have been replicated to all Subscribers or until the Snapshot Agent runs and creates a new snapshot, whichever is longer. If the elapsed time between Snapshot Agent runs is greater than the maximum distribution retention period for the publication, which has a default of 72 hours, transactions older than the retention period are removed from the distribution database. For more information, see Subscription Expiration and Deactivation.

Although making the snapshot available to Subscribers immediately improves the speed with which new Subscribers have access to the publication, the option can result in increased disk storage for the distribution database. It also means that a new snapshot is generated each time the Snapshot Agent runs. If the option is not used, a new snapshot is generated only if there is a new subscription.

Primary Keys for Each Published Table

All published tables in transactional replication must contain a declared primary key. Existing tables can be prepared for publishing by adding a primary key using the Transact-SQL statement ALTER TABLE (Transact-SQL).

Triggers

Be aware of the following issues in using triggers on a subscription database:

By default, triggers execute with the XACT_ABORT setting ON. If a statement within a trigger causes an error while the Distribution Agent is applying changes at the Subscriber, the entire batch of changes will fail, rather than the individual statement. In transactional replication, you can use the -SkipErrors parameter of the Distribution Agent to skip statements that cause errors. If -SkipErrors is used with XACT_ABORT ON, the entire batch of changes is skipped if a statement causes an error. Unless you require XACT_ABORT to be set to ON in triggers, we recommend that you set it to OFF if you are using the -SkipErrors parameter. To set the option off, specify SET XACT_ABORT OFF in the trigger definition. For more information about XACT_ABORT, see SET XACT_ABORT (Transact-SQL). For more information about the -SkipErrors parameter, see Skipping Errors in Transactional Replication.
We recommend not including explicit transactions in triggers at the Subscriber. Transactional replication makes use of transaction batching to reduce network roundtrips, thereby enhancing performance. If triggers that include ROLLBACK statements are added at the Subscriber, batches of transactions can be cancelled and server error 266 can be raised (Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = %ld, current count = %ld.). A batch might contain commands from multiple transactions or be part of a large transaction at the Publisher, so rolling back transactions can compromise transactional integrity.
If you do include explicit transactions, ensure that all COMMIT statements in a trigger have corresponding BEGIN TRANSACTION statements. A COMMIT without a corresponding BEGIN TRANSACTION causes non-transactional application of row changes to the Subscriber. Additionally, a later failure can occur if the Distribution Agent encounters server error 266 and tries to roll back a transaction or batch of commands so that it can apply them again. When the agent attempts to apply commands that have already been applied, it results in duplicate key failures.

For more information about triggers, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

Large Object (LOB) Data Types

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:
1. Begin the transaction.
2. Obtain the text pointer using the TEXTPTR() function with REPEATABLE READ isolation level.
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 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 Downloading Northwind and pubs Sample Databases.

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.

Share via

Considerations for Transactional Replication

Transaction Log Space

Disk Space for the Distribution Database

Primary Keys for Each Published Table

Triggers

Large Object (LOB) Data Types

text, ntext and image Data Types

See Also

Concepts

Other Resources

Help and Information

Additional resources