Wednesday, September 24, 2008

Using Merge with Replication

Replication was enhanced in version 9.0 to include an option for using SQL MERGE statements when performing a replication. The MERGE statement defines an UPDATE and INSERT statement which allows for the appropriate action to be taken. When matching records are found they are updated if a match is not found a new record is inserted.

In the past if a record that did not exist on the Subscriber(server receiving changes) was updated it would return an error.  Any replication errors will prevent further updates from being sent to the Subscriber until the error is resolved, by default. Changes will continue to be queued up but will not be sent to the Subscriber. All replication errors are logged with the error class 7600 in the Advantage Error Log making them easier to locate.

You can ignore all replication errors by specifying the “Ignore Replication Failures” option on the Subscription. This will simply skip records which generate an error on the Subscriber when replicated. You can also define a custom set of errors by setting the PERMITTED_REP_ERRORS configuration parameter (Advantage 8.1 and newer). Simply provide a comma (,) separated list of error numbers to be allowed, ensuring replication continues.

A MERGE statement can be used on replication INSERTS and UPDATES. You enable the use of MERGE statements in the Publication properties dialog (shown below). These are independent of each other so you can select none, one or both of these options. MERGE statements are not used by default.

PublicationSetupThis option can be used to assist with setting up replication to another Subscriber. By using MERGE statements for UPDATEs any record which does not exist at the Subscriber will be added to the table. This means you can put a copy of your database on the Subscriber which does not have any data in the tables, the table structure must exist on the subscriber. The next step is to update every record on the Publisher to insert all the records on the Subscriber.

You can do this with an SQL statement which does not change the data such as: UPDATE <table> SET <field> = <field>. For more details on this refer to this KB Item. It describes the process and provides code for a UDF which performs an UPDATE on every table in the database which would replicate all the records to the Subscriber(s).

No comments: