Chapter 11 Setting Up a Mobile Intranet

A Replication Tutorial

This section includes a tutorial in which you set up a simple SQL Remote replication system between an Adaptive Server Enterprise consolidated database and a remote Adaptive Server Anywhere database to replicate a Dynamo Web site along with accompanying data.

In the tutorial you act as the system administrator of a consolidated Enterprise database, and set up a simple replication system. The replication system consists of a sales database with two tables, and a Web site.

The consolidated database holds all of the database and Web site, while the remote database has the entire Web site, all of one table, and some of the rows in the other table.

The goals of the replication design are to provide each sales representative with:

The complete SalesRep table
Those customers assigned to them
A Web site for querying and updating their data

The tutorial describes how to meet this goal using SQL Remote and the Sybase Central management utility.

The tutorial takes you through:

Creating a consolidated database on your Adaptive Server Enterprise.
Connecting to the consolidated database through Dynamo to create your Web site.
Creating a file-sharing replication system with a single Adaptive Server Anywhere remote database.
Replicating the Web site between the two databases.

The database

The tutorial uses a database that contains two tables and a Dynamo Web site that performs queries on the tables. One table holds information about sales representatives, and the other about customers.

Database schema

The database schema for the tutorial is illustrated in Figure 11-1.

Figure 11-1: Database schema

Each sales representative is represented by one row in the SalesRep table, and each customer is represented by one row in the customer table.

Each customer is assigned to a single Sales representative, and this assignment is built in to the database as a foreign key from the Customer table to the SalesRep table. The relationship between the Customer table and the SalesRep table is many-to-one.

The tables in the database

The tables are described in more detail as follows:

Table	Description
SalesRep	The SalesRep table has the following columns: rep_key An identifier for each sales representative. This is the primary key. name The name of each sales representative. The SQL statement creating this table is as follows: CREATE TABLE SalesRep ( rep_key CHAR(5) NOT NULL, name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) )
Customer	One row for each customer that does business with the company. The Customer table includes the following columns: cust_key An identifier for each customer. This is the primary key. name The name of each customer. rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table. The SQL statement creating this table is as follows: CREATE TABLE Customer ( cust_key CHAR(12) NOT NULL, name CHAR(40) NOT NULL, rep_key CHAR(5) NOT NULL, FOREIGN KEY ( rep_key ) REFERENCES SalesRep ( rep_key ), PRIMARY KEY (cust_key) )

Table

Description

SalesRep

The SalesRep table has the following columns:

rep_key An identifier for each sales representative. This is the primary key.
name The name of each sales representative.

The SQL statement creating this table is as follows:

CREATE TABLE SalesRep (
    rep_key CHAR(5) NOT NULL,
    name CHAR(40) NOT NULL,
    PRIMARY KEY (rep_key)
)

Customer

One row for each customer that does business with the company. The Customer table includes the following columns:

cust_key An identifier for each customer. This is the primary key.
name The name of each customer.
rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesRep table.

The SQL statement creating this table is as follows:

CREATE TABLE Customer (
    cust_key CHAR(12) NOT NULL,
    name CHAR(40) NOT NULL,
    rep_key CHAR(5) NOT NULL,
    FOREIGN KEY ( rep_key )
        REFERENCES SalesRep ( rep_key ),
    PRIMARY KEY (cust_key)
)

Setting up SQL Remote using Sybase Central

The first part of the tutorial describes how to set up a simple SQL Remote replication system using Sybase Central. You do not need to enter SQL statements if you are using Sybase Central to administer SQL Remote.

Create a login name and password

You must have system administrator privileges on an Adaptive server. The tutorial assumes that your login name is sa and that the password is sysadmin.

Create a database

Create a 20MB database named hq on your Adaptive Server Enterprise.

Connect to the Adaptive Server Enterprise from Sybase Central.
Open the Databases folder, and double-click Add database in the right pane.
Enter hq on the first page of the wizard.
Follow the instructions in the wizard.

For information on how to create databases and assign space to them, see your Adaptive Server Enterprise documentation. Install SQL Remote into the hq database.

Create the Web site

Create a Dynamo Web site in the Adaptive Server Enterprise database:

Connect to the hq Adaptive Server Enterprise database from Dynamo. When asked if you would like to create a Web site, select Yes.
Enter a name for the Web site and select the check boxes if you would like to include the Dynamo samples and documentation.
The following Dynamo tables are created in your hq database:
- WebConnection
- WebDocumentType
- WebSynchronize
- WebTemplate
- WebData
- WebVersion
Later, you will connect to this Web site through a remote database. To be able to do this, you must modify the connection settings in your consolidated database so they do not hold specific database information such as data source names and passwords of the consolidated database.
From the Web site's system folder, open the connect.ssc file. Modify the <default> connection so there is no information specific to the consolidated database. Your <default> connection should look like this:

site.LoadConnection( "<default>", "", "", "", "", "", "" );

Save the file.

Install SQL Remote

Install SQL Remote into the HQ database:

Open the hq database container in the left pane of Sybase Central.
Open the SQL Remote folder, double-click Setup SQL Remote, and follow the instructions. For the tutorial, install the stable queue in the hq database.
If your TEMPDB database is too small, you may have to add space to it.

For a full description of how to install SQL Remote, see the Data Replication with SQL Remote manual.

Create directories for messages

Make a directory for the files created in this tutorial. For example:

mkdir c:\tutorial

Under this directory, create a directory for each of the two users of the replication system:

mkdir c:\tutorial\hq
mkdir c:\tutorial\field

Add tables

The next step is to add two user-defined tables to the consolidated database:

Connect to the hq database from Sybase Central.
Click the User Tables folder of the hq database.
Double-click Add Table, and use the Table Editor to create a table named SalesRep with the following columns:
Key

Column

Data Type

Size/Prec

Primary key

rep_key

char

5

name

char

40

You do not need to use the Advanced Properties window. By default, the columns do not allow NULL.
Double-click Add Table again, and use the Table Editor to create a table named Customer with the following columns:
Key

Column

Data Type

Size/Prec

Primary key

cust_key

char

10

name

char

40

rep_key

char

5

Again, you do not need to use the Advanced Properties window. By default, the columns do not allow NULL.
Open the Foreign Keys folder of the Customer table container, and double-click Add Foreign Key. Using the wizard, add a foreign key to the rep_key column of the SalesRep table. Use the default settings for this foreign key.

Key	Column	Data Type	Size/Prec
Primary key	rep_key	char	5
	name	char	40

Key	Column	Data Type	Size/Prec
Primary key	cust_key	char	10
	name	char	40
	rep_key	char	5

Setting up the consolidated database

This section of the tutorial describes how to prepare the consolidated database of a simple replication system, including:

Creating a message type to use for replication.
Granting PUBLISH permissions to a user ID to identify the source of outgoing messages.
Granting REMOTE permissions to all user IDs that will receive messages.
Creating a publication describing the data to be replicated.
Creating 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.
An address for this message link, which identifies the source of outgoing messages.

Adaptive Server Anywhere databases have predefined message types, but you need to supply an address for the message type you will use. To add an address to a message type:

From Sybase Central, open the hq database container.
Click the SQL Remote folder on the left panel.
Double-click the Message Types folder on the right pane.
Double-click the file message type.
Enter hq as the publisher address. This provides a return address for remote users; the directory you created to hold messages for the consolidated database.
The address (hq) for a file link is a directory relative to the SQLRemote environment variable or registry entry. Because you have not set this value, the address is 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 .
Click OK to save the message type.

Create the necessary users and permissions

In this tutorial, the following are required for the SQL Remote installation:

A publisher user name
A remote user or subscriber

This section describes how to create each user and assign them the necessary permissions.

To create the publisher:

From Sybase Central, open the container for the hq database.
Open the Users folder, and double-click Add User.
Create a user named hq_user. Map the login name to an available login name on your server. The tutorial uses hq_pwd as the password.
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 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. If this is the case, you may not have to add any new users, but you do need to set existing users as remote users.

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

To add a remote user:

Double-click the SQL Remote folder in the left pane, then click the Remote Users folder.
If you do not have a login name to 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 in the right pane. The New Remote User wizard is displayed.
Create a remote user named field_user. Select FILE as the message type, then select 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 and may not be set appropriately for the tutorial. Run the Message Agent from your tutorial directory for the addresses to be interpreted properly.

For information about setting the SQLRemote enviroment variable, see the Data Replication with SQL Remote manual.
Next, ensure that the Send Then Close option is checked.
When you have finished all the entries, click Finish to create the remote user.

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 the full Dynamo Web site as well as all rows in the table SalesRep and some of the rows of the Customer table.

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

Open the SQL Remote folder in the hq database.
Open the Remote Tables folder, and double-click Add Remote Table.
Select WebConnection from the list of tables. Leave the Conflict Resolution fields are they are and click Apply to mark the table for SQL Remote replication. Repeat this step for all the other Web site tables:
- WebDocumentType
- WebSynchronize
- WebTemplate
- WebData
- WebVersion
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.
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:

Click the Publications folder in the SQL Remote folder.
Double-click Add Publication.
On the first page of the wizard, name the publication SalesRepData.
On the next page, click Add Table and select SalesRep from the list. Leave All Columns selected, and press OK to add the table. Do the same for all the Dynamo Web tables:
- WebConnection
- WebDocumentType
- WebSynchronize
- WebTemplate
- WebData
- WebVersion
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.
Complete the wizard to create the publication.

Add a subscription

Each user ID that is to receive updates to a publication must have a subscription to that publication. You need to add a subscription to the SalesRepData publication for the remote database user field_user:

Double-click the Publications folder, which is in the SQL Remote folder, so that the SalesRepData publication is displayed in the left pane.
Click the Remote Users folder so that remote users are displayed in the right pane.
Drag field_user from the right pane onto the SalesRepData publication in the left pane. In the Create Subscription window, enter rep1 in the With Value box. This value represents the rep_key value for field_user in the SalesRep table.
You have now set up the consolidated database.

No data will be exchanged until you start the database extraction utility.

Setting up the remote database

This section of the tutorial describes how to create and configure the remote database to send and receive messages and participate in a SQL Remote setup.

Here's what happens when you extract (create) the remote database:

Creates a publisher (in this case, the field_user user ID) to identify the source of outgoing messages.
Creates the hq_user as a user with consolidated permissions.
Creates the SalesRepData publication and a subscription for the hq_user user ID.
Synchronizes the remote database with the consolidated database so that a current copy of the data is available.

The database extraction utility performs these steps to create a remote database complete with subscriptions and required user IDs.

You need to extract a database from the consolidated database for remote user field_user:

Click the Remote Users folder, which is in the SQL Remote folder.
Right-click field_user, and select Extract Database from the pop-up menu.
In the Extraction wizard, enter the user ID and password that you used to create the tables and users in the database.
On the next page, select Start Subscriptions Automatically. Also, check Create New Remote Database. Adaptive Server Anywhere must be installed for Create New Remote Database to be available.
Create the database as file c:\tutorial\field.db and specify a transaction log in the same directory.
Extract all parts of the schema (the default). Leave the other options at their default settings, and create the remote database.

You should connect to the field database as DBA and confirm that all the database objects are created:

From Sybase Central, choose Tools | Connect | Sybase Adaptive Server Anywhere.
Enter DBA as the user and SQL as the password. Use upper case, as the database was created as case sensitive. Select field.db, and Connect.
Open the database container, and confirm that the tables and user names are present.

In a real-life SQL Remote setup, you would load the remote database field onto the computer using it, together with an Adaptive Server Anywhere engine and any required client applications. For the tutorial, we leave the database where it is and use isql to input and replicate data.

The system is now ready for replication.

Replicating data

In this section of the tutorial, we'll replicate data from the consolidated database to the remote database, and from the remote to the consolidated database.

Enter data at the consolidated database

To enter data at the Adaptive Server Enterprise database:

Use isql to connect to the Enterprise server:
```
isql -S server-name -U sa -P sysadmin
```

Verify that you are using the hq database, and enter a series of rows:

use hq
go
insert into SalesRep (rep_key, name)
values ('rep1', 'Field User')
go
insert into SalesRep (rep_key, name)
values ('rep2', 'Another User')
go
insert into Customer (cust_key, name, rep_key)
values ('cust1', 'Ocean Sports', 'rep1')
go
insert into Customer (cust_key, name, rep_key)
values ('cust2', 'Sports Plus', 'rep2')
go
commit
go

These commands assign the customer Ocean Sports to the sales rep Field User, and Sports Plus to Another User. You must commit the changes, as SQL Remote replicates only committed changes.

Next, you send the relevant rows to the remote Adaptive Server Anywhere database.

Send data from the consolidated database

The Message Agent, which, for the tutorial, is called SSremote, populates the remote database with the rows you added in the previous section. To send the rows to the remote database, you must run the Message Agent at the consolidated database.

To replicate the data from Adaptive Server Enterprise:

Enter the following statement (on a single line) at the command line to run the Message Agent against the consolidated database:
```
ssremote -c "eng=server-name;dbn=hq;uid=sa;pwd=sysadmin"
```
Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been sent.

Receive data at the remote database

The Message Agent first receives a message from hq, and then sends a message. This return message contains confirmation of successful receipt of the replication update; such confirmations are part of the SQL Remote message tracking system that ensures message delivery even in the event of message system errors.

To receive the insert statement at the remote database, you must run the Message Agent, dbremote, at the remote database:

With the database engine running, enter:

dbremote -c "eng=field;dbn=field;uid=DBA;pwd=SQL"

Click Shutdown on the Message Agent window to stop the Message Agent when the messages have been processed.

The Message Agent window displays status information while running. In a production setup, you will probably send this information to a log file for record keeping.

To verify that the data has arrived at the remote database, connect to the remote field database and inspect the SalesRep and Customer tables, to see which rows have been received:

Connect to the field database using isql.
Inspect the SalesRep table by typing:
```
SELECT * FROM SalesRep
```
You see that the SalesRep table contains both rows entered at the consolidated database. This is because the SalesRepData publication included all the data from the SalesRep table.
Inspect the Customer table by typing:
```
SELECT * FROM Customer
```
You see that the Customer table contains only one row (Ocean Sports) entered at the consolidated database. This is because the SalesRepData publication included only those customers assigned to the subscribed sales rep.

Create a template in your remote Web site that queries the data

In this section, we create a template that performs a query from the Adaptive Server Anywhere remote database. This template queries data that has been replicated from the consolidated database to the Adaptive Server Anywhere remote database.

From Sybase Central, create a ODBC Data Source name to connect to field.db.
Create a connection profile for field.db.
Connect to field.db.
In the left pane of Sybase Central, open the Site folder.
In the right pane, double-click Add Template.
On the first page of the wizard, enter a name for the template, for example, RepProducts. Do not use spaces. Click Next.
Enter an optional description for the template and click Next again.
Use the default connection.
On the Add SQL Query page, click Select and enter a query to display the cust_key, name, and rep_key columns.
Select one of the supplied formats from the list.
Click Finish.
You can view the output by right-clicking on the template and selecting Browse Output.

Creating a Template Wizard - tab editor
If the user-defined tables were created from the consolidated database with a user name of DBO, the tab editor needs to be set to display system tables. Adaptive Server Anywhere considers all tables created under DBO as system tables. They will not be visible to the tab editor if table type is set to table.

Replicate from the remote database to the consolidated database

You should now try entering data at the remote database and sending it to the consolidated database:

Use isql to connect to the field database.

Insert a row at the remote database. For example:

INSERT INTO Customer (cust_key, name, rep_key) 
VALUES ('cust3', 'North Land Trading', 'rep1')

Commit the row:
```
COMMIT;
```
With the field.db database running, run dbremote to send this message to the consolidated database.
```
dbremote -c "eng=field;dbn=field;uid=DBA;pwd=SQL"
```
Run ssremote to receive the message at the consolidated database:
```
ssremote -c "eng=server-name;dbn=hq;uid=sa;pwd=sysadmin"
```
From Sybase Central, connect to the Web site in the consolidated database and browse the RepProducts template, which has three rows:
cust_key

name

rep_key

cust1

Ocean Sports

rep1

cust2

Sports Plus

rep2

cust3

North Land Trading

rep1

cust_key	name	rep_key
cust1	Ocean Sports	rep1
cust2	Sports Plus	rep2
cust3	North Land Trading	rep1

In this example, there is no protection against duplicate entries of primary key values, however SQL Remote does provide for such protection. For information, see the Data Replication with SQL Remote manual.