SQL Server Apply Parameters

The following parameters are valid for the SQL Server Apply Process:

Process Parameters

• process – The only possible value is apply
• id – The Apply Process id
• dbtype – Type of RDBMS, the only possible value is mssql
• capturename – The name of the Capture Process which is providing the data to be used

Database Connectivity

• server – Host name or IP address of the database server
• port – Database port
• dbname – Database name
• user – Database User name
• password – Database User password

Transaction Processing Parameters

• ddlcreate – Used to define the Apply Process behaviour when creating a table, if a table with the same name is found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following options:
  • skip– do not run DDL create command if the table already exists
  • recreate – to drop the existing table and recreate it
• ddldrop – Used to define the Apply Process behaviour when dropping a table, if a table with the same name is not found. The table is specified in schema _name.table_name format. In order to specify the list of the tables, each table must be separated by comma. The % symbol may be used in order to match any number of characters. It has the following option:
  • skip – do not run DDL drop command if the table doesn’t exist
• ddlprocessing – Determines if DDL must be processed based on the user statement or generated based on the dictionary changes. The possible values are:
  • native – to process DDL based on the user statement (default value)
  • dictionary – to generate DDL based on the dictionary changes
  • map – to generate DDL based on the dictionary and the objects’ transformation rules

Note – For Homogeneous type of replication it is recommended to use "native" method of DDL processing. The "dictionary" value is commonly used in the case of Heterogeneous replication. “map” is used when the DDL statement must be reformatted following objects' transformation rules 

• ddldisabletriggers – Determines if the Apply Process must disable the triggers of a table after it has replayed a DDL statement on the table. It is used in SQL Server to SQL Server replication when all the DDL, including the creation of the triggers, is replicated: a trigger created in the Target Database together with its table would fire for the replicated DML and make the changes, which are already replicated, a second time. The statement that disables the triggers is run together with the replayed DDL statement. The parameter is valid for “ddlprocessing=native” only. The possible values are:
  • 0 – do not disable the triggers (default value)
  • 1 – disable all the enabled triggers of the table

Note – The parameter is valid for the tables of the default schema (usually dbo) of the database user of the Apply Process.

Note – The parameter does not affect the DML operations and the Initial Load: the triggers of the Target Database fire as usual (see also the “repuser” parameter).

• ldtxsize – Determines the number of records to be inserted in one transaction at the Initial Load stage. If the value is 0 the table is loaded in a single transaction. The default value is 500000
• batchmode – This parameter is to specify transaction processing method.  The possible values are:
  • 0 – apply all the data changes within transactions as they were occurred in Source (default value)
  • 1 – combine and processes set of transactions in a single transaction
  • 2 – combine set of the transactions into a single transaction and analyse data within it to produce the only one net change for each row that has been changed

Note – setting “batchmode” parameter to “2” requires all the foreign keys to be disabled, otherwise the Apply Process may failed with the “foreign key violation” error

• bulkthreshold – Minimum number of consecutive row changes of one table and of one operation (INSERT, UPDATE or DELETE) within a transaction, for which the Apply Process uses the bulk loading method instead of applying the changes one by one. The value 0 disables the bulk loading method. The default value is 1000.

Note – Setting this value too low may cause unnecessary transactional overhead for minor updates. Setting it too high may prevent the system from ever utilizing bulk mode, resulting in slower processing times during high-volume periods.

• mergetables – The list of the tables, whose INSERT operations and Initial Load rows must be applied as MERGE (upsert) operations instead of INSERT operations. The tables are specified in schema_name.table_name format, as the names are in the Target Database after the objects mapping (case-sensitive); the tables must be separated by comma. UPDATE and DELETE operations are not affected.

Note – Each table must have a primary or unique key.

• skiploopback – Used to define the behavior of the Apply Process when inserting data, which was originally produced by Apply Processes in the Source Database as part of the “loop-back” control function, in that, this Apply Process will insert, or ignore the data. The Apply Processes to be skipped are specified by ids separated by comma. In order to specify all Processes the “all” value to be used. In order to avoid skipping any Apply Processes the “none” value to be used. The default value is “none”.

Filters and Local Transformations 

• map – The set of the parameters to determine local objects’ transformation (see  Objects Mapping and Possible Transforms for the details)
• filter – The set of the parameters to determine objects’ filtering (see Objects Filtering for the details)

Performance Tuning Parameters

• chunkrows – Maximum number of the row changes (SQL statements) which the Apply Process sends to the Target Database in one batch. A batch is closed as soon as either this limit or the limit of the “chunkbytes” parameter is reached. A very small value results in a large number of round trips to the Target Database. The default value is 30000.
• chunkbytes – Maximum size, in bytes, of the SQL text which the Apply Process sends to the Target Database in one batch. The values of the changed rows are a part of the text, so a batch of wide rows is closed earlier. A batch is closed as soon as either this limit or the limit of the “chunkrows” parameter is reached. The default value is 10485760.
• readbytes – Approximate number of bytes of the trail data which the Apply Process reads in one cycle. The transaction that crosses the limit is still read completely. The default value is 5242880 (5 MiB).
• maxtxbytes – Size, in bytes, of a transaction above which the Apply Process reads the transaction from the trail files and applies it in parts, to keep the memory usage under control. The value 0 disables splitting of the transactions. The default value is 125829120 (120 MiB).

Other Parameters

• autostart – Determines if the Apply Process must be run automatically. Possible values are:
  • 0 – do not run the Apply Process automatically (default value)
  • 1 – to run the Apply Process automatically
• cleanuptrail – Determines number of days the Apply Process must keep trail files. The incremental data comparison reads the changes from these files and fails if the Apply Process has already deleted them, so use -1 or a number of days that is longer than the time between two comparisons (see Start/Stop Task and Tasks Control). Possible values are:
  • -1 – Never cleanup
  • 0 – Cleanup once the trail files is processed (default value)
  • 1 .. 999 – Number of days the Apply Process keeps the files
• restartattempts – Number of attempts to restart the failed Apply Process automatically. The server checks the processes every 5 minutes. The value 0 disables the automatic restart. When the attempts are used up, the notification channels (see the “notification” parameter) are informed. The default value is 5.

Note – The number of attempts is restored to the full value as soon as the process makes progress after a failure.

• notification – The ids of the notification channels, separated by comma (see Monitoring Replication for the details on the channels), which are informed by e-mail when the process has failed and can not be restarted automatically any more (see the “restartattempts” parameter). By default nobody is notified.
• debuglevel – The level of debugging. Possible values are 0-15
• preview – Determines if the configuration of the “prepare” or “alter” command must be saved. The possible values are:
  • 0 – save the configuration (default value)
  • 1 – do not apply the configuration to the Apply Process but provide the list of the tables to be applied along with the transformation rules. The trail files of the Capture Process must exist
• repuser – Determines if the Apply Process must represent itself as SQL Server replication process. The possible values are:
  • 0 – do not represent itself as SQL Server replication process (default value)
  • 1 – to represent itself as SQL Server replication process

Note – You should be aware that if the repuser parameter is set to “1” and is invoked, then when delivering data to a SQL Server Target Database, any changes of objects marked as ‘Not for Replication’ (Identity columns, table triggers, foreign keys and check constraints) will be handled in exactly the same way, as they would be produced by an SQL Server replication agent. As an example, any triggers which are created with the “Not for Replication” option will never be fired.