Planning for Transactional Replication

Transactional replication requires planning in the following areas:

• Transaction log space.
  
  
• Disk space for the distribution database.
  
  
• Primary keys for each table to be published.
  
  
• Immediate updating and queued updating.
  
  
• Transforming replicated data.
  
  
• text and image data types in transactional replication.
  
  
• Identity ranges.
  
  
• Constraints and NOT FOR REPLICATION.

Transaction Log Space

For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged 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 passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances.

Disk Space for the Distribution Database

If you plan to create transactional publications and make the snapshot files available to Subscribers immediately, allow enough disk space for the distribution database to store all of the transactions after the last snapshot. Although making the snapshot available to Subscribers immediately improves the speed with which new Subscribers have access to the publication, the option does require a larger disk storage area for the distribution database. It also means that a new snapshot will be generated each time the Snapshot Agent runs. If the option is not used, and if anonymous subscriptions are not allowed, a new snapshot needs to be generated only if there is a new subscription.

The distribution database begins collecting transactions immediately and continues to store them until the second time the Snapshot Agent is run (either scheduled or run manually). After the second time the Snapshot Agent is run, the cleanup task begins to clean up and reduce the size of the distribution database by deleting the rows from the first snapshot. Thus, if you use the default schedule of once a day for running the Snapshot Agent, you must have enough disk space to store all the transactions that occur in one day.

Similarly, if you plan to create transactional publications and allow anonymous subscriptions to a publication, you must allow enough disk space for the distribution database to store all of the transactions since the last snapshot. Allowing anonymous subscriptions also means that a new snapshot will be generated every time the Snapshot Agent runs.

An alternative to allocating more disk space in both of these situations is to run the Snapshot Agent more frequently than once a day (the default) so fewer commands must be retained in the distribution database. However, generating a snapshot can be resource-intensive and can affect performance temporarily. Reducing the distribution retention period (in Publisher and Distributor Properties) can also help maintain fewer commands because the Distribution Clean Up Agent is controlled by the distribution retention period and will remove replicated transactions from the distribution database.

Primary Keys

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

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 at the Publisher on text and image columns are supported with no special considerations. However, these columns cannot be updated by Subscribers that use snapshot replication or transactional replication and immediate updating or queued updating subscriptions.
  
  
• 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 is not supported because replication reads the transaction log.
  
  
• UPDATETEXT operations can be performed only if all Subscribers are running Microsoft® SQL Server™ version 6.0 or later Subscribers. 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 only UPDATETEXT.)
  
  
• Custom procedures are not used if multiple text columns are being modified because the other text column values are not logged. Instead, a standard UPDATE statement is generated.
  
  
• 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 instances of SQL Server 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.
  
  
• Using the sp_configure system stored procedure sets the max text repl size parameter.
  
  
• When publishing text 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 may 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 with read repeatable isolation.
     
     
  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.

An important consideration when sizing Subscriber databases is that the text pointer for replicated text and image columns must be initialized on Subscriber tables, even when they are not initialized on the Publisher. Consequently, each text and image column added to the Subscriber table by the distribution task will consume at least 43 bytes of database storage even if the contents are empty.