First Guide to SQL Anywhere Studio
   PART 2. Getting Results with SQL Anywhere Studio
     CHAPTER 11. Replicating Data with SQL Remote       

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.

Notes 

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. This is a file sharing method, where the messages are left in a file on disk, and read by the intended recipient. SQL Remote also supports ftp and e-mail protocols.

• An address for this message link, to identify the source of outgoing messages. In this tutorial we use a file path to specify where the messages will be left.

Message types are created in all new databases, but you do 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 sample database as user ID dba using the password sql, and open the sample database container (asademo) on the left panel.

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. The publisher address is the directory you have created to hold messages for the consolidated database (dba).

6. Click OK to save the message type.

  Add the publisher to the database

Each database in a SQL Remote replication system needs a single user ID that identifies the publisher of the data. Here, we make the dba user ID the publisher.

  To set the publisher:

1. Click the Users & Groups folder on the left panel.

2. Right-click the dba user, and select Set As Publisher from the popup menu. The dba Type column then displays the entry 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 to the database

Each remote database is identified in the consolidated database by a user ID with REMOTE permissions.

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 in the dba group on the left panel, then click the Remote Users folder, also 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 name field, password field, message type file, and address field. You should select the defaults for all other entries in the Wizard, except that you should grant DBA permissions as well as REMOTE DBA permissions to the user on the Indicate Authorities page. When you have finished all the entries, click Finish to create the remote user.

Notes 

If you forget to set DBA permission in the Wizard, you can set it by double-clicking the user, and checking DBA in the Authorities tab.

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 department.

  To add a publication:

1. Click the Publications folder in the SQL Remote folder of the sample database.

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

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

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

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 DepartmentPub publication for the remote database user field.

  To add a subscription:

1. Open the Publications folder, which is in the SQL Remote folder.

2. Right-click DepartmentPub in the right panel, and select Subscribe For from the popup menu. A window is displayed, showing the field user in the list.

3. Select field and click Subscribe to subscribe the field user to the DepartmentPub publication.

You have now set up the consolidated database.