User's Guide

Part III. Using SQL Anywhere

Chapter 27. Introduction to SQL Remote Replication

This section of the tutorial describes how to set up the consolidated database of a simple replication system.

Setting up a consolidated database involves the following steps:

1. Create a message type to use for replication.
2. Grant PUBLISH permissions to a user ID to identify the source of outgoing messages.
3. Grant REMOTE permissions to all user IDs that are to receive messages.
4. Create a publication describing the data to be replicated.
5. Create subscriptions describing who is to receive the publication.

You require DBA authority to carry out these tasks.

Create a SQL Remote message type

All messages sent as part of replication use a message type. A message type description has two parts:

• A message link supported by SQL Remote. Currently, the link must be one of FILE, MAPI, VIM, or SMTP.
• An address for this message link, to identify the source of outgoing messages.

You can create a file message type using the following statement:

     CREATE REMOTE MESSAGE TYPE file ADDRESS 'hq'

You can create a MAPI message type using the following statement:

     CREATE REMOTE MESSAGE TYPE mapi ADDRESS 'hq_address'

Grant PUBLISH and REMOTE at the consolidated database

In the hierarchical replication system supported by SQL Remote, each database may have one consolidated database immediately above it in the hierarchy and many databases immediately below it on the hierarchy (remote databases).

PUBLISH permission identifies the current database for outgoing messages, and the REMOTE permission identifies each database receiving messages from the current database.

Permissions can only be granted by a user with DBA authority. To carry out these examples you should connect using the ISQL utility to HQ.DB as user ID DBA, with password SQL.

GRANT PUBLISH to identify outgoing messages

Each database that distributes its changes to other databases in the replication system is a publisher database. Each database in the replication system that publishes changes to a database is identified by a single user ID. You set that ID for your database using the GRANT PUBLISH statement. This section describes setting permissions for the consolidated database (HQ.DB).

The following statements create a publisher:

     GRANT CONNECT TO hq_user IDENTIFIED BY hq_pwd ;

     GRANT PUBLISH TO hq_user ;

You can check the publishing user ID of a database at any time using the CURRENT PUBLISHER special constant:

     SELECT CURRENT PUBLISHER

GRANT REMOTE for each database to which you send messages

Each remote database is identified using the GRANT REMOTE statement. Whether the remote database is a single-user database engine or a database server with many users, it needs a single user ID to represent it to the consolidated database.

In a mobile workgroup setting, remote users may already be users of the consolidated database, and so this would require no extra action on the part of the DBA.

The GRANT REMOTE statement identifies the message system to be used when sending messages to the recipient, as well as the address.

The following statements grant REMOTE permissions to a database with user ID field for a MAPI-based message system:

     GRANT CONNECT TO field_user IDENTIFIED BY field_pwd ;

     GRANT REMOTE TO field_user TYPE mapi ADDRESS 'field_address' ;

The address string is the MAPI address of field_address, enclosed in single quotes. The MAPI address is not the same as the mail user ID.

The following statements grant REMOTE permissions to a database with user ID field_user for a file-sharing message system:

     GRANT CONNECT TO field_user IDENTIFIED BY field_pwd ;

     GRANT REMOTE TO field_user TYPE file ADDRESS 'field' ;

The address string is the directory used to hold messages for field_user, enclosed in single quotes. The directory is a subdirectory of the SQLREMOTE environment variable or Directory registry entry, if set, or of the current working directory if SQLREMOTE is not set.

Create publications and subscriptions

A publication is created using a CREATE PUBLICATION statement. This is a data definition language statement, and requires DBA authority. For the tutorial, you should connect to the hq database as user ID DBA, password SQL, to create a publication.

Set up a publication at the consolidated database

Create a publication that replicates all rows of the table news using the following statement:

     CREATE PUBLICATION pub_news (    TABLE news)

This publication is about the simplest that can be created. Publications can involve multiple tables, and subsets of the rows and columns in tables. For more information on publications, see "Designing publications".

Set up a subscription

Each user ID that is to receive changes to the publication must have a subscription. The subscription can only be created for a user who has REMOTE permissions. The GRANT REMOTE statement contains the address to use when sending the messages.

Create a subscription to the pub_news publication for the remote user field_user:

     CREATE SUBSCRIPTION TO pub_news FOR field_user ;

The full CREATE SUBSCRIPTION statement allows control over the data in subscriptions; allowing users to receive only some of the rows in the publication. For more information, see "CREATE SUBSCRIPTION statement".

The CREATE SUBSCRIPTION statement identifies the subscriber and defines what they receive. However, it does not synchronize data, or start the sending of messages.