User's Guide

Part III. Using SQL Anywhere

Chapter 27. Introduction to SQL Remote Replication

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

Preparing a consolidated database for replication 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.

Add 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 or MAPI. A VIM link and an SMTP/POP link are expected to be available in the first quarter of 1996.
• An address for this message link, to identify the source of outgoing messages.

SQL Anywhere databases already have MAPI and FILE message types created, but you need to supply an address for the message type you will use. To add an address to a message type:

1. Click the SQL Remote folder on the left panel.
2. Double-click the Message Types folder.
3. Double-click whichever message type (MAPI or FILE) you are going to use for the tutorial.
4. Enter a publisher address to provide a return address for remote users. For the file message link, enter C:\TUTORIAL\HQ; the directory you have created to hold messages for the consolidated database. (In a production setup, file addresses are taken relative to the value of the SQLREMOTE environment variable or Directory registry setting, which would have been set to C:\TUTORIAL for this tutorial.) For the MAPI message link, enter the valid MAPI address you are using for the consolidated database.
5. Click OK to save the message type.

Add the publisher and remote user to the database

In SQL Remote's hierarchical replication system, each database may have zero or one consolidated database immediately above it and zero or more databases immediately below it (remote databases).

In this tutorial, the current database is the consolidated database of a two-level system. It has no database above it, and only one remote database below it.

The following diagram illustrates the two databases:

For any database in a SQL Remote replication setup, there are three permissions that may be granted to identify databases on the hierarchy:

• PUBLISH permission identifies the current database in all outgoing messages
• REMOTE permission identifies each database receiving messages from the current database that is below it on the hierarchy
• CONSOLIDATE permission identifies a database receiving messages from the current database that is directly above it on the hierarchy.

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

Add a database publisher user ID

Any database, consolidated or remote, that distributes changes to other databases in the replication system is a publisher database. Each database in the replication system is identified by a single user ID. You set that ID for your database by adding a publisher to the database. This section describes setting permissions for the consolidated hq database.

First create a user ID named hq_user, who will be the publisher user ID.

1. Click the Users & Groups folder on the left panel.
2. Double-click Add User. The New User Wizard is displayed.
3. Enter the name hq_user, with password hq_pwd, and click Next.
4. On the next page, ensure that the user is granted Remote DBA authority; this enables the user ID to run the Message Agent. Then click Next.
5. On the final page, check the box identifying this user ID as the publisher. Then click Finish to create the user.

A database can have only one publisher. You can find out who the publisher is at any time by opening the SQL Remote folder.

Add a remote user

Each remote database is identified in the consolidated database by a user ID with REMOTE permissions. 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 no new users would need to be added; although they would need to be set as remote users.

When a remote user is added to a database, the message system they use and their address under that message system need to be stored along with their database user ID.

You can add a remote user as follows:

1. Click the SQL Remote folder on the left panel, then click the Remote Users folder on the left panel.
2. Double-click Add Remote User on the right panel. The New Remote User wizard is displayed.
3. Create a remote user with name field_user, password field_pwd, message type file, and address field. For the message type and address, select the type (FILE or MAPI) and the corresponding address you are using for this user.
4. You should ensure that the Send Then Close option is checked. (In many production environments you would not choose Send Then Close, but it is convenient for this tutorial.)
5. You should ensure that the Remote DBA authority is checked, so that the user can run the Message Agent.
6. When you have finished all the entries, click Finish to create the remote user.

Add publications and subscriptions

This section describes how to add a publication to a database, and how to add a subscription for that publication to a user. You can add a publication that replicates all rows of the table news as follows:

1. Click the Publications folder in the SQL Remote folder.
2. Double-click Add Publication. The Publication Wizard is displayed.
3. Name the publication pub_news on page one of the Wizard. On page two, click Add Table and select news from the list. Leave the All Columns button selected, press OK, and complete the Wizard to create the publication. This publication is one of the simplest that can be created. Publications can involve multiple tables, and subsets of the rows and columns in tables. These can all be defined using the Publication Wizard.

Add a subscription

Each user ID that is to receive changes to a publication must have a subscription to that publication. Subscriptions can only be created for a valid remote user.

Add a subscription to the pub_news publication for the remote database user field_user:

1. Double-click the Publications folder, which is in the SQL Remote folder, so that the pub_news publication is displayed in the left panel.
2. Click the Remote Users folder so that remote users are displayed in the right panel.
3. Drag the field_user user from the right panel onto the pub_news publication in the left panel. The Create Subscription window is displayed. You should leave both the Subscribe By and With Value text boxes empty; click OK to create the subscription.