Advantage Developer Zone

 
 
 

Advantage Replication Options

Monday, May 04, 2009

There are several options available when using Advantage Replication. These include the ability to pause sending changes to subscribers, merging changed records and filtering options. The pause and merge options were added with the 9.0 release of Advantage Database server. The filtering options were available with the 8.1 release.

Pausing Replication

Advantage uses a push replication model which sends changes made at the publisher to its subscriber(s) as the changes are made. If a subscriber is unavailable the publisher will continue to attempt to connect to the subscriber until the change(s) are successfully sent to the subscriber. These connection attempts use an algorithm which delays each subsequent request to minimize the number of failed attempts.

If you know that one or more subscribers will be offline for a period of time you can pause replication. When replication is paused all changes will still be stored in the replication queue but the server will not attempt to connect to the subscriber. You can pause replication by checking the Pause Subscription checkbox on the General tab of the Subscription in ARC.

Note that the pause option continues to queue changes whereas the disable option does not. You can also pause a subscription by using the sp_ModifySubscriptionProperty system procedure.

EXECUTE PROCEDURE sp_ModifySubscriptionProperty( 'Store_2', 'PAUSED', 'TRUE' )
Using Merge with Replication

Support for an SQL Merge statement was also added in version 9 of Advantage (see the tech-tip on the Merge Statement). The Merge Statement allows Advantage replication to insert records that are updated in the source database, but not found at the subscriber. In addition, if records are insterted at the source database, but already exist in the subscriber, they can be updated at the subscriber. In the past this would cause an error that would pause replication until the error was corrected.

You can configure your publication to use Merge for inserts and/or updates on the Publication properties page in ARC. You can choose to use Merge with updates, inserts or both. The Merge will insert a record if it doesn’t already exist and update a matching record.

Filtering options

Advantage Replication supports two types of filtering. Horizontal filtering allows for records to be filtered using specific conditions. This filter is specified using an expression using the Advantage Expression Engine syntax. Any record that evaluates to TRUE when the expression is applied will be replicated.

Vertical filtering is the ability to specify which fields are replicated from individual tables. This allows for slightly different schemas to exist on the publisher and subscriber. This is done by specifying either an inclusion or exclusion list. All of the fields on an inclusion list will be replicated to the subscriber(s). Alternately all fields except those on an exclusion list will be replicated. By default all of the fields in the table are replicated.

All filters are defined at the publication level. These filters are specified on the general tab of the Publication dialog in ARC. The horizontal filter expression is specified below the field list. You can choose the fields for inclusion or exclusion by checking the appropriate column on the field list.

Summary

Advantage replication is a powerful tool for keeping data up to date at multiple locations. Replication has become more flexible with the Pause, Merge and filtering options.

Pausing allows you to take subscribers offline while still tracking changes in the database. When the subscriber is back online replication can be unpaused and will bring the subscriber(s) up to date. Merging allows replication to databases which may not contain the same records initially. The Merge allows for an insert of a new record if an updated record at the publisher was not found. It will also allow for an update of an existing record on the subscriber that was inserted at the publisher.

Finally, improved filtering allows for different table structures at the subscribers. The publication can be configured to only replicate specified fields or to exclude specific fields from a replication. Filtering also allows for the records to be limited based on filter expressions.