Data Replication with SQL Remote
   PART 1. Introduction to SQL Remote
     CHAPTER 4. A Tutorial for Adaptive Server Anywhere Users       

Setting up a consolidated database

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. In this tutorial, we use the FILE link.

• An address for this message link, to identify the source of outgoing messages.

Adaptive Server Anywhere databases already have 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. From Sybase Central, connect to the HQ database, and open the HQ database container.

2. Click the SQL Remote folder on the left panel.

3. Double-click the Message Types folder on the right panel.

4. Double-click the FILE message type.

5. Enter a publisher address to provide a return address for remote users. Enter the directory you have created to hold messages for the consolidated database (hq).

   The address is taken relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

        For information about setting the SQLRemote value, see Setting message type control parameters.

6. 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 database immediately above it (the consolidated database) 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 the hq database 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.

  To create the publisher:

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 personal database server or a network 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.

  To add a remote user:

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 user ID field_user, password field_pwd, message type file, and address field. For the message type and address, select the FILE type and the corresponding address you are using for this user (such as field).

   As with the publisher address, the address of the remote user is taken relative to the SQLRemote environment variable or registry entry. As you have not set this value, the address is taken relative to the directory from which the Message Agent is run. You should run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

        For information about setting the SQLRemote value, see Setting message type control parameters.

4. On the next page, 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. On the next page 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.

You have now created the users who will use this system.

  Add publications and subscriptions

This section describes how to add a publication to a database, and how to add a subscription to that publication for a user. The publication replicates all rows of the table SalesRep and some of the rows of the Customer table.

  To add a publication:

1. Click the Publications folder in the SQL Remote folder.

2. Double-click Add Publication. The Publication Wizard is displayed.

3. Name the publication SalesRepData on the first page of the Wizard.

4. On the next page, click Add Table and select SalesRep from the list. Leave All Columns selected, and press OK to add the table.

   Then click Add Table again, and select Customer from the list. Again, leave All Columns selected. Click the Subscribe restriction tab, and choose to Subscribe by the column rep_key. Click OK to add the table to the publication.

5. Complete the Wizard to create the publication.

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. You need to add a subscription to the SalesRepData publication for the remote database user field_user.

  To add a subscription:

1. Double-click the Publications folder, which is in the SQL Remote folder, so that the SalesRepData 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 SalesRepData publication in the left panel. The Create Subscription window is displayed. Enter a value of rep1 in the With Value box. The value rep1 is the rep_key value we will give to the user field_user in the SalesRep table.

You have now set up the consolidated database.