One advantage of using incremental subscriptions is most apparent with large database tables, since once the baseline has been run, only changes need to be transmitted. However, the execution of a baseline for a very large table may take a long time and can be subject to disruptions in the network or on the source or target system. This feature allows you to resume an interrupted baseline, appending new rows to the last committed row in the destination table, without having to start the baseline from the beginning.
This feature applies to both snapshot and incremental subscriptions.
In the Global Service properties for the SQDR service, set the ODBC or BCP commit interval to a relatively low non-zero value, or use an ODBC commit interval value of one, which means "use autocommit" - each insert is checkpointed; this provides the most granular level of recovery at the expense of performance.
For a SQL Server source, the subscription should include a derived column with a value of $ROWGUID and destination column type of CHAR(36). You do not need to specify a destination column name.
If a baseline fails to run, the icon associated with the subscription will be a yellow exclamation mark, indicating that a baseline is still required, and there will be an event with a red "X". If you view the details of the replication event, you may see an error such as:
Fetch failed at source.
ODBC message: SQLSTATE 08S01, native error
0, [StarSQL][StarSQL ODBC Driver] Communications link failure.
When the baseline runs again (either started explicitly by selecting "Run Subscription", or started as a scheduled or "as-needed" triggered operation), the details of the replication event should contain an informational message:
Subscription restarted after <N> rows.
Viewing the properties of the subscription and saving it will reset the subscription and remove all checkpoints; the next baseline will run from the beginning.
Note: Checkpoint Restart is not available if you have disabled the use of the ORDER BY clause for performance reasons - see the useOrderBy advanced setting.