SQL Server Trigger-Based Replication

A database trigger executes automatically in response to certain events on a database table. To define a trigger-based replication (mirroring or synchronization), you need to provide information in the Source or Target Connection wizards, such that triggers can be created to log table changes for replication.

For each table involved in the replication, Syniti Replicate creates three triggers in the source table that executes when a specific event occurs on a record:

• INSERT trigger that executes when a new record is being inserted in the table.

• UPDATE trigger that executes when a record is modified.

• DELETE trigger that executes when a record is deleted.

Note

If the replication is deleted later, Syniti Replicate removes the triggers. However, note that if you change a replication from mirroring to refresh, the triggers on the source table are not deleted. All transactions will continue to be recorded in the log tables. If you are not planning to reset the replication to mirroring, it is better to delete the replication, such that the triggers are removed and you can create a new refresh replication.

Data Management Using Triggers

Data retrieved using the triggers is stored in log tables that are specified in your Source or Target connection. The master log table can be an existing table or one created specifically to hold Syniti Replicate information. It contains general information about the transactions, for example user name, timestamp, and table name. A log table (_DBM__LOG_x) is also created for each source table in the replication and contains the data changes identified by the triggers, as well as trigger objects _DBM__TRG_OBJS. Note that Syniti Replicate does not create a tablespace. If you want to have a table space named SYNITIDR, you must create it beforehand using a SQL tool. Run the appropriate SQL statement for your environment. For example: CREATE TABLESPACE SYNITIDR

When creating a connection, it is important to set the retention time to keep the log table size under control. The higher the value, the more data is kept in the log tables. Try to estimate the number of transactions occurring in all the source tables during a retention period and ensure that the database and table space have enough storage capacity for all those transactions. The Replication Agent cleans up the log tables periodically, based on the retention setting in the connection dialog box. If the engine is not running, the log tables are not cleaned up. This might create space problems in the database as the logs grow in size. If you stop the engine and you are not planning to run it again, be sure to remove all the mirroring synchronization replications.

Additionally, if you have many table replications in a single group, using a single connection, all the replications share a master log table. Access to the log table for each source table can become a bottleneck if there are many transactions using the same master log and log tables. Syniti Replicate may report errors about locked tables during replication. Although Syniti Replicate is able to recover from these errors and continue replicating, a better approach is to prevent the errors by splitting the replications into multiple groups with multiple connections and multiple master log tables. First, create multiple source connections to the database. Use the Transaction Log Type field in the Connection Properties of each connection to open the Setup Info dialog box and create a new master log table for each connection.

During replication, the following triggers may get executed accordingly:

• When a record is inserted in the source table, the INSERT trigger executes and inserts one record in the master table and one record in the log table associated with the source table. The record inserted in the log table contains all the original values of the INSERT statement.

• When a record is deleted from a table, the DELETE trigger executes and inserts one record in the master table and one record in the log table associated with the source table. The record inserted in the log table contains the key values of the deleted record.

• When a record is updated, the UPDATE trigger executes and inserts one record in the master table and two records in the log table associated with the source table. The two records inserted in the log table contain all the record values before and all the records after the update.

A transaction ID is saved both on the master records and log record to maintain a link between the transaction and the data changes for that transaction. See Syniti Replicate Log Tables for more details on the structure of the Master and Log tables. Your system administrator needs to create and define appropriate table spaces and databases to hold the log tables. They should be large enough to handle the expected amount of replication data.

Log tables are used to record all data changes made to the source tables. They are populated by triggers that are fired when source tables are modified. Currently, log tables must reside in the replication source database. Note that log tables are created only if they do not already exist in the database.

There are two log tables associated with each replication: a master table, common to all replications using that connection, and a log table, one for each replication. The master table keeps track of all the transactions affecting the source tables and it records general transactional information.

Master Table Structure

Log Table Structure

The Log table contains the actual data changes for a specific source table. Its structure depends on the structure of the source table.

CREATE TABLE SOURCET 
(ID INTEGER, 
FNAME VARCHAR(30), 
LNAME VARCHAR(50))

CREATE TABLE _DBM__SOURCET 
(__TID DECIMAL(31,0), 
__OP CHAR, 
ID INTEGER, 
FNAME VARCHAR(30), 
LNAME VARCHAR(50))

Reading From or Managing Log Table

Syniti Replicate reads the log tables using the SELECT SQL statement. First, it queries the master table to see if new transactions came in since the last processed __TID. If transactions are found, Syniti Replicate queries the corresponding log table to collect the data changes and apply them to the target table.

The SELECT queries on the master and log tables are sorted by the unique column __TID which ensures that all records will be read in the order that they were written. Syniti Replicate also uses the unique __TID column to keep track of the point where the last record was read and processed from the log tables.

Syniti Replicate provides options to manage log records that have been read and replicated. They can be deleted from the log table as soon as they are processed or a retention time can be set to leave this record in the log tables for the specified number of hours.

Limitations

Data types such as image, text, and ntext are not supported. If you create a transactional replication on a table with fields where these data types defined, the Management Center warns you that the fields will not be replicated. In SQL Server 2005 and SQL Server Express, these types have been replaced with varbinary(max), varchar(max), and nvarchar(max). Microsoft recommends replacing the old data types with the new ones. The new data types are supported when using the Triggers option.

The source table does not need a primary key set in SQL Server, but it must have a primary key defined in Syniti Replicate. See the User Guide Topic Primary Key Settings for Mirroring and Synchronization for more information on how to create a primary key in Syniti Replicate.

SQL Server – Triggers User Permissions

When setting up replications that use SQL Server as a source database, you need to ensure that the user ID used for making connections to the database has sufficient privileges to complete all the operations required for Syniti Replicate to perform a replication. Use the following command to grant the required permissions:

grant connect to sdruser;
exec sp_addrolemember 'db_owner', 'sdruser';   
grant alter on schema::dbo to sdruser;
grant create table to sdruser;

If your security polices decline to grant db_owner role to the user, you can use the following command to enable a Triggers-based replication:

grant connect to sdruser;
exec sp_addrolemember 'db_datawriter', 'sdruser';   
grant alter on schema::dbo to sdruser;
grant create table to sdruser;

The security context of a trigger, unless otherwise specified in the trigger's definition, is the context of the principal that caused the trigger to execute (the caller), that is, if the changes on the SQL source are caused by a user called sdruser, like the previous example, then that user needs to be able to insert, update, and delete records from the master table that was created or chosen when the connection is created.

You can alter the trigger directly in SQL Server SMSS once it's created and use an execute as clause. For example, CREATE TRIGGER dbo.test WITH EXECUTE AS OWNER, or CREATE TRIGGER dbo.test WITH EXECUTE AS HOSTNAME\Username (if using a specific user, that user needs to have IMPERSONATE permissions to call it from an EXECUTE AS clause).

Enable Transactional Replication Wizard

For transactional replications (mirroring and synchronization), use the Enable Transactional Replication wizard after setting up a source connection.

Log Type Screen

Select the Triggers option.

Trigger Settings Screen

Master Table

Either specify an existing qualified table name, or click Change to create a new table to hold general information about replication transactions, including user name, timestamp, and table name for each transaction.

Two tables are associated with each replication:

• Master Table: common to all replications using that connection. The Master table keeps track of all the transactions affecting the source tables and it records general transactional information.

• Log Table: for each replication source table.

Master and Log tables are created in the schema specified when you set the Master table name. You can choose a Master table name, or use the default _DBM__MASTERLOG. Log tables are automatically generated by Syniti Replicate and the names are _DBM__LOG_#, where # is a number. The selected schema for the Master and Log tables must not contain other non-Syniti tables with names _DBM__LOG_#. The recommended approach is to create a new schema to use specifically for the Syniti Master and Log tables.

Table space

The recommended approach is to assign a table space for the Master table and Log Tables so that it is easier to control log table sizes. If you leave this field blank, the default table space value for your User ID will be used. Your system administrator should be able to provide you with the appropriate value for this field.

Retention Time

The amount of time in hours that a transaction is kept in the log tables. The default value is 72 hours. When the amount of time a transaction resides in the log exceeds the retention time, the transaction is permanently removed from the log tables. Tuning the retention time provides control over the size of the log tables. It is also possible to instruct Syniti Replicate to remove all the processed transactions at the end of each mirroring interval. Tuning the retention time provides control over the size of the log tables.

Delete Block Size

Based on the retention time, Syniti Replicate deletes items from the log. This field specifies the maximum number of records to delete from the Syniti Replicate log tables with a single SQL statement. The default value is 10,000 records. You do not typically need to edit this value.

Lower-Case Trigger Identifiers

Select this option if your database installation uses lower-case trigger identifiers.

Trigger Order

Always inactive for SQL Server sources.

Further Resources

• For a complete up-to-date list of supported sources and targets, and information on where to obtain a .NET provider, check the Supported .NET Providers article in the Help Center.

• The Help Center also provides database-specific setup notes in PDF format.

• Microsoft SQL Server

• Microsoft SQL Server - Always-On Environment