Tip 75: Replication Overview

This is the text of a presentation to the November 25, 1997 meeting of the Toronto PowerBuilder User Group. It is an excerpt from Replication Step-By-Step - A Detailed Demonstration Of Publish Subscribe Replication Using Sybase SQL Anywhere With MESSAGE TYPE "FILE" (unpublished).

Some terminology...

• No: Watcom SQL

• No: Sybase SQL Anywhere

• No: SQL Central

• YES: Adaptive Server Anywhere

• YES: Sybase Central

One FACT, two VALUE JUDGEMENTS...

• Replication is the copying of data (insertions, deletions, updates) among multiple databases

• Replication is hard

• The hardest parts of all are the parts that look the easiest

Central administration

Oriented towards a mobile workforce

Small footprint

Disk and RAM requirements are reasonable

Large fan-out

Optimized for a large number of remote databases (often standalone) for each central database

Occasional connections

Designed for users that are only occasionally connected to the central database

High latency

Characterized by long time lags between data entry and replication to other databases

Low volume

Best suited for small amounts of replicated data per database

Homogenous databases

Designed for SQL Anywhere and SQL Server databases with very similar schema

Local availability

Unaffected by communications problems or a failure of the central server

Local performance

Response times and transfer rates are better than across a WAN

Server offloaded

Only updates are replicated, not repeated queries

Log-based architecture

Based on replication of committed transactions, .LOG file required

Full DBENG50*.EXE required

The royalty-free RTDSK50*.EXE Desktop Runtime engine is not sufficient

Tight integrity

Transactions are replicated atomically

Loose consistency

Consistent replication over time with differences in copies of data caused by time lags

Hierarchical configuration

As opposed to a peer-to-peer relationship among databases

Consolidated database

Contains all the data to be replicated, plus other data

Remote database

Contains part or all of the data to be replicated, plus other data

No system-wide locking

Conflicts must be designed out of the system or resolved at the consolidated database

Remote user

A single user id represents the remote database for replication purposes

Message-based data transfer

Via MAPI, VIM, SMTP or "message files"

Publish Subscribe

The send-and-receive replication method uses publications and subscriptions

Publication - Database object describing the subset of data to be replicated:

     CREATE PUBLICATION publication-name
        ( TABLE table-name
             SUBSCRIBE BY column-name )

Subscription - Identifies which user (database) is subscribing to which publication:

     CREATE SUBSCRIPTION
        TO publication-name ( 'column-value' )
        FOR target-db-user-id

Article

How a single table participates in a publication

Publisher

The user id which sends output messages from a consolidated or remote database

Subscriber

The user id which receives input messages sent to a remote or consolidated database

Two-way replication

Updates are replicated up and down in the consolidated-remote hierarchy by default

Replication between remote sites

This is possible by replicating up and down via the consolidated database

Synchronization

Making a remote database consistent with the consolidated database

Initialization utility

DBINIT creates the .DB and .LOG files for each database

Extraction utility

DBXTRACT creates SQL and data files to load a synchronized remote database from scratch

Message Agent

DBREMOTE runs on each database to gather and apply changes and to exchange messages

SQL Remote

Another name for DBREMOTE, or the replication product as a whole

Batch mode

Where DBREMOTE starts and then shuts down after processing all current messages

Continuous mode

Where DBREMOTE periodically sends and continuously receives messages

SSXTRACT, SSREMOTE

Extraction utility and message agent for SQL Server

SYNCHRONIZE SUBSCRIPTION

Synchronizes a subscription for one remote user via DBREMOTE

PASSTHROUGH

Facility for sending SQL commands via DBREMOTE to execute on remote databases

Importance of database design

To avoid errors, conflicts and complexity

Replication is not Data Distribution

Life is good within the limits of publish subscribe!