About triggers and non-transactional replication

If the Interval Type, as specified on the Replication Rules form, is not Transactional, but is either Immediate or a defined interval, replication triggers place the data in a staging area for later replication.

The staging area consists of these tables:

  • ReplicatedRows3_mst: This is the database table that stores general data about the replication request. This table includes these columns:
    • Operation Number: This is an incrementing integer that is used to ensure that the data are replicated to the remote site in the same order in which they were created at the original site. This helps to preserve data integrity.
    • Reference Row Pointer: For table objects, this is the Row Pointer being referenced.
    • Actual Object Name: This is the actual name of the table (not the caption). This can be used to rename the object being replicated, if necessary.
    • Actual Object Type: This is the actual type of the object.
    • Object Name: This is the name of the table, method, or XML document being replicated, as specified on the Replication Objects form.
    • Object Type: This can be a table, a method, or an XML-formatted document.
    • Operation Type: This indicates what type of operation is to be performed. These are the possible values:
      • 1 = Insert
      • 2 = Update
      • 3 = Delete
      • 4 = Method call
    • Processing Pointer: This is a unique ID value, used by the process, that actually moves the data to an outbound queue. This prevents other processes from attempting to transfer the same row.
    • Replication Transfer Type: This indicates what the replication transfer type is, whether IDO XML or Data Lake.
    • To Site: This is the name of the site to which the data is to be sent.
    • Update All Columns: This value indicates whether the replication rule specifies that all columns are to be updated.
    • Workflow List: This field is not currently used.
    • Data: This column is used for JSON data, which, for performance reasons, is not stored in the ShadowValues table.
      Note: JSON data for Data Lake does not go to MSMQ for later pickup by the Replication Queue Listener service. It is processed directly by the Replicator service. The volumes which go through for Data Lake are not suitable for MSMQ.
  • ShadowValues: This table contains the actual data being replicated.

    The ShadowValues table contains these columns:

    • OperationNumber: The OperationNumber and RowPointer join this table to the ReplicatedRows3_mst table, which contains all other needed information.
    • LineNum: Since there are sometimes more than 55 columns in the base table, including old and new rows, multiple lines are used.
    • OldNew: This field indicates whether the record contains the new values or the old values. For INSERT operations, only new values are stored. For UPDATE operations, both old and new values are stored. For DELETE operations, only old values are stored.
    • Valuen: Each of the Valuen columns contains the value of the column being replicated.

      This is an ntext column (BLOB). Because the text column was not originally used, notes are copied using a separate mechanism.

      These columns have more overhead associated with them, so the values default to 1000 bytes in place if they are small enough. This is accomplished by means of this T-SQL system stored procedure call: sp_tableoption N'ShadowValues', 'text in row', '1000'

    • Namen: Each Namen column contains the name of the replicated column.

    • RowPointer: For table objects, this is the RowPointer being referenced.

The Replicator service uses a single SELECT statement to retrieve information out of the ShadowValues table, which then moves the data to the outbound or inbound queue and creates the necessary MSMQ messages. The Replication Queue Listener service then processes those messages.

The replication process can also include data from other application database tables or views, including these:

  • ReplicatedRowsErrors: This table contains rows that failed to load on inbound replication. This failure could occur because of something like a table constraint failure, such as primary keys, check constraints, or foreign keys.
  • ShadowValuesErrors: This table contains the actual bad rows of data.
  • ReplicationOperationCounter: This table increments the unique operation counter.