Setting up the consolidated database

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:

Create a message type to use for replication.
Grant PUBLISH permissions to a user ID to identify the source of outgoing messages.
Grant REMOTE permissions to all user IDs that are to receive messages.
Create a publication describing the data to be replicated.
Create subscriptions describing who is to receive the publication.

You should have system administrator authority to carry out these tasks.

Create the message links and addresses

In this tutorial, messages are exchanged using the shared file link. You must create a FILE message type supplying the address of the consolidated database publisher.

To create the message type:

Execute the sp_remote_type stored procedure, using HQ as the address of the consolidated database publisher:
```
sp_remote_type file, hq
go
```
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.

With the message type defined, you can now make the necessary users.

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 remote user or subscriber, with name field_user.
A publisher user name, called hq_user.

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

To create the publisher:

Add a login called hq_user, with hq as the default database and with system administrator access:
```
exec sp_addlogin hq_user, hq_pwd, hq
go
exec sp_role 'grant', sa_role, hq_user
go
```
Add the login name as a user to the HQ database:
```
use hq
go
exec sp_adduser hq_user
go
```
Make this user the publisher of the HQ database:
```
exec sp_publisher hq_user
go
```

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

To create the subscriber:

If you do not have a login name that you can use for the remote user, add a login:
```
exec sp_addlogin field_user, field_pwd, hq
go
```
Add a user to the hq database:
```
exec sp_adduser field_user
go
```
Grant the user remote permissions. Execute the sp_grant_remote stored procedure, using field_user as the user name, file as the message type, and the appropriate directory as the address:
```
exec sp_grant_remote field_user, file, field
go
```
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.

Create the publication and subscription

The remaining task is to define the data to be replicated. To do this, you must first create a publication, which defines the available data, and then create a subscription for field_user, which defines the data that user is sharing.

In Adaptive Server Enterprise, they are created with the sp_create_publication procedure, which creates an empty publication, and the sp_add_article procedure, which adds articles to the procedure. Also, each table must be marked for replication before it can be included in a publication.

To create the publication:

Create an empty publication:

exec sp_create_publication SalesRepData
go

Mark both the SalesRep table and the Customer table for publication:

exec sp_add_remote_table SalesRep
go
exec sp_add_remote_table Customer
go

Add the whole SalesRep table to the SalesRepData publication:
```
exec sp_add_article SalesRepData, SalesRep
go
```
Add the Customer table to the SalesRepData publication, using the rep_key column to partition the table. The following statement should be typed all on one line, except for the go:
```
exec sp_add_article SalesRepData, Customer, NULL, 'rep_key'
go
```

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 create a subscription:

Create a subscription to SalesRepData for field_user, with a subscription value of rep1:
```
exec sp_subscription 'create', SalesRepData, field_user, 'rep1'
go
```
At this stage, the subscription is not started—that is, no data will be exchanged. The subscription is started by the database extraction utility.

Extract the remote database

There are three stages to producing a remote Adaptive Server Anywhere database:

Extract the schema and data into a set of files. You do this using the ssxtract command-line utility.
Create an Adaptive Server Anywhere database.
Load the schema and data into the database.

Extracting the schema and data

With all the information included, the next step is to extract an Adaptive Server Anywhere database for user field_user. The following command line (entered all on one line, from the tutorial directory) carries out this procedure:

ssxtract -v -c "eng=server-name; dbn=hq;uid=sa;pwd=sysadmin" C:\tutorial\field field_user

The command-line arguments have the following meaning.

-v Verbose mode. For development work, this provides additional output.
-c Connection string argument. The connection string is supplied in double quotes following the -c.
eng=server-name Specifies the server to which the extraction utility is to connect.
dbn=hq Specifies the database on the server to use; in this case hq.
uid=sa The login ID to use to log on to the database.
pwd=sysadmin The password to use to log on to the database.
C:\tutorial\field The directory in which to place files holding the data.
field_user The user ID for which to extract the database.

For Info For more information on extraction utility command-line switches, see The extraction command-line utility

Running this command produces the following files:

Reload script The reload script is named reload.sql, and is placed in the current directory.
Data files Files containing data to load into the database. In this case, these files are empty.

Creating an Adaptive Server Anywhere database

You can create an Adaptive Server Anywhere database using the dbinit command-line utility. A simple Adaptive Server Anywhere database is a file, unlike Adaptive Server Enterprise databases.

You should create the Adaptive Server Anywhere database so that it is compatible with Adaptive Server Enterprise database behavior, unless you have set options in your Adaptive Server Enterprise server that are different from the default.

To create a database file named field.db:

Enter the following command from the c:\tutorial\field directory:
```
dbinit -b -c -k field.db
```
The -b switch forces use of blank padding in string comparisons. The -c switch enforces case sensitivity for string comparisons. The -k switch makes the system catalog more compatible with Adaptive Server Enterprise.

Loading the data into the database

You can load the data into the database using the Adaptive Server Anywhere Interactive SQL utility or the rtsql command-line utility. rtsql is an alternative to Interactive SQL for batch processes only, and is provided for the runtime database.

To load the data into the database using Interactive SQL:

Start an Adaptive Server Anywhere server running on the field database:
```
dbeng6 field.db
```
Connect to the server using the Interactive SQL utility:
```
dbisql -c "eng=field;dbn=field;uid=DBA;pwd=SQL"
```
The user ID and password must be entered in upper case, as the Adaptive Server Anywhere database was created as case-sensitive.
Load the data using the READ command:
```
READ C:\TUTORIAL\RELOAD.SQL
```

To load the data into the database as a batch process:

Start an Adaptive Server Anywhere server running on the field database:
```
dbeng6 field.db
```
Run the script from Interactive SQL:
```
dbisql -c "eng=field;dbn=field;uid=DBA;pwd=SQL" reload.sql
```
The user ID and password must be entered in upper case, as the Adaptive Server Anywhere database was created as case-sensitive.

What next?

The system is now ready for replication.

For Info For the next step, inserting and replicating data, see the section Start replicating data.