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

Setting up the 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 should have system administrator 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, 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 hq; the directory you have created to hold messages for the consolidated database.

   The address (hq) for a file link is a directory in which files containing the message are placed. It 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.

  Create the necessary users and permissions

A set of users and permissions are required for SQL Remote installations. In this tutorial, the following are required:

• A publisher user name.

• A remote user or subscriber.

This section describes the steps you need to take to create each user and assign them the necessary permissions.

  To create the publisher:

1. From Sybase Central, open the container for the hq database.

2. Open the Users folder, and double-click Add User.

3. Create a user named hq_user, mapping the login name to an available login name on your server. In this tutorial, a password of hq_pwd is assumed.

4. Make this user the publisher of the HQ database. Open the SQL Remote folder, and double-click Set Publisher. Select hq_user from the list of users to set it as the publisher.

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 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. Double click the SQL Remote folder on the left panel, then click the Remote Users folder on the left panel.

2. If you do not have a login name that you can use for the remote user, open the logins folder directly under the server container, and add a login. The name is unimportant.

   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. 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 (field) is a directory 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. 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.

  Create the publication and subscription

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.

The first step is to mark the SalesRep and Customer tables for SQL Remote replication. Marking a table for SQL Remote replication enables it to be included in publications.

  To mark the tables for SQL Remote replication:

1. Open the SQL Remote folder in the hq database.

2. Open the Remote Tables folder, and double-click Add Remote Table.

3. Select SalesRep from the list of tables. You can leave the Conflict Resolution fields as they are. Click Apply to mark the table for SQL Remote replication.

4. Select Customer from the list of tables. Again, you can leave the Conflict Resolution fields as they are. Click OK to mark the table for SQL Remote replication.

  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.

   At this stage, the subscription is not started—that is, no data will be exchanged. The subscription is started by the database extraction utility.

You have now set up the consolidated database.