A replication tutorial

User's Guide
PART 5. The Adaptive Server Family
CHAPTER 32. Replicating Data with Replication Server

A replication tutorial

This section provides a step-by-step tutorial describing how to replicate data from a primary database to a replicate database. Both databases in the tutorial are Adaptive Server Anywhere databases.

Replication Server assumed

This section assumes you have a running Replication Server. It does not describe how to install or configure Replication Server. The Replication Server documentation is the primary source of information about Replication Server.

What is in the tutorial

This tutorial describes a simple replication, where only tables are replicated. For information about replicating procedures, see Preparing procedures and functions for replication.

The tutorial uses a simple example of a (very) primitive office news system: a single table with an ID column holding an integer, a column holding the user ID of the author of the news item, and a column holding the text of the news item. The primary key consists of the id column and the author column.

To work through the tutorial, it is recommended that you create a directory (for example, c:\tutorial) to hold the files you create in the tutorial.

Set up the Adaptive Server Anywhere databases

This section describes how to create and set up the Adaptive Server Anywhere databases for replication.

You can create a database using Sybase Central or the dbinit command-line utility. For this tutorial, we use the dbinit command-line utility.

To create the primary site database:

From the directory you are using for the tutorial (for example c:\tutorial), enter the following command.
```
dbinit -b -c -k primedb
```
This creates a database file primedb.db in the current directory.

To create the replicate site database:

From the directory you are using for the tutorial (for example c:\tutorial), enter the following command.
```
dbinit -b -c -k repdb
```
This creates a database file repdb.db in the current directory.

For information on the choice of command-line switches, see Creating a Transact-SQL-compatible database.

What's next?

The next step is to start database servers running on these databases.

Start the database servers

You need to run the primary site database server, with the primary database loaded.

To start the primary site database server:

Change to the tutorial directory and enter the following command line to start a network database server running the primedb database and using the TCP/IP network communication protocol on the default communications port (2638):
```
dbsrv6 -x tcpip primedb.db
```
The -n command line option defines the server name as PRIMESV. This is the name for Adaptive Server Anywhere applications. It is not the name used by Replication Server or other Open Client applications.

To start the replicate site database server:

Change to the tutorial directory and enter the following command line to start a network database server running the repdb database, but on a different port:
```
dbsrv6 -x tcpip{port=2639} -n REPSV repdb.db
```
The -n command line option defines the server name as REPSV. This is the name for Adaptive Server Anywhere applications. It is not the name used by Replication Server or other Open Client applications.

What's next?

The next step is to make entries for each of the Adaptive Server Anywhere servers into an interfaces file, so that Replication Server can communicate with these database servers.

Set up the Open Servers in your system

You need to add a set of Open Servers to the list of Open Servers in your system.

Adding Open Servers

Open Servers are defined in your interfaces file (sql.ini) using the dsedit utility. For NetWare and UNIX users, the interfaces file is named interfaces, and the utility is named sybinit.

For Info For full instructions on how to add definitions to your interfaces file, see Configuring Open Servers with DSEDIT.

Required Open Servers

For each Open Server definition you must provide a name and an address. Do not alter the other attributes of the definition. You need to add Open Server entry for each of the following:

The primary database Create an entry named PRIMEDB with address as follows:
- Protocol NLWNSCK
- Network address localhost,2638
The replicate database Create an entry named REPDB with address as follows:
- Protocol NLWNSCK
- Network address localhost,2639
The LTM at the primary database This is needed so that the LTM can be shut down properly. Create an entry named PRIMELTM with address as follows:
- Protocol NLWNSCK
- Network address localhost,2640
Your Replication Server This tutorial assumes you already have the Replication Server Open Server defined.

What's next?

You should confirm that the Open Servers are configured properly.

Confirm the Open Servers are configured properly

You can confirm that each Open Server is available by selecting ServerObject->Ping Server from the DSEDIT utility.

Alternatively, you can confirm that each Open Server is configured properly by connecting to the database using an Open Client application such as the isql utility.

To start isql running on the primary site database, type

isql -U dba -P sql -S PRIMEDB

Add Replication Server information to the primary database

You need to add Replication Server tables and procedures to the primary site database for the database to participate in a Replication Server installation. You also need to create two user IDs to be used by Replication Server. The SQL command file rssetup.sql is provided with Adaptive Server Anywhere to carry out these tasks.

The rssetup.sql command file must be run on the Adaptive Server Anywhere server from the Interactive SQL utility.

To run the rssetup script:

From Interactive SQL, connect to the database as user ID DBA using password SQL.
Run the rssetup script using the following command:
```
read "path\rssetup.sql"
```
where path is your Adaptive Server Anywhere installation directory.

Actions carried out by rssetup.sql

The rssetup.sql command file carries out the following functions:

Creates a user named dbmaint, with password dbmaint and with DBA permissions. This is the maintenance user name and password required by Replication Server to connect to the primary site database.
Creates a user named sa, with password sysadmin and with DBA permissions. This is the user ID used by Replication Server when materializing data.
Adds sa and dbmaint to a group named rs_systabgroup.

Passwords and user IDs

While the hard-wired user IDs (dbmaint and sa) and passwords are useful for test and tutorial purposes, you should change the password and perhaps also the user IDs when running databases that require security. Users with DBA permissions have full authority in a Adaptive Server Anywhere database.

The user ID sa and its password must match that of the system administrator account on the Replication Server. A NULL password is not currently allowed by Adaptive Server Anywhere.

Permissions

The rssetup.sql script carries out a number of operations, including some permissions management. The permissions changes made by rssetup.sql are outlined here. You do not have to make these changes yourself.

For replication, you should ensure that the dbmaint and sa users can access this table without explicitly specifying the owner. To do this, the table owner user ID must have group membership permissions, and the dbmaint and sa users must be members of the table owner group.

To grant group permissions, you must have DBA authority.

For example, if the table is owned by user DBA, you should grant group permissions to the DBA user ID:

GRANT GROUP 
TO DBA

You should then grant the dbmaint and sa users membership in the DBA group. To grant group membership, you must either have DBA authority or be the group ID.

GRANT MEMBERSHIP
IN GROUP "DBA" 
TO dbmaint ;
GRANT MEMBERSHIP 
IN GROUP "DBA" 
TO sa ;

Create the table for the primary database

In this section, we create a single table in the primary site database, using isql. First, if you are not already connected to the primary site database, do so:

isql -U dba -P sql -S PRIMEDB

Once connected, you can create a table in the database:

CREATE TABLE news (
  ID int,
  AUTHOR char( 40 ) DEFAULT CURRENT USER,
  TEXT char( 255 ),
  PRIMARY KEY ( ID, AUTHOR ) 
)
go

Identifier case sensitivity
In Adaptive Server Anywhere, all identifiers are case insensitive. In Adaptive Server Enterprise, identifiers are case sensitive by default. Even in Adaptive Server Anywhere, ensure the case of your identifiers matches in all parts of the SQL statement to avoid incompatibility with Adaptive Server Enterprise.

In Adaptive Server Anywhere, user IDs and passwords are case insensitive in case-insensitive databases and case sensitive in case sensitive databases.

For news to act as part of a replication primary site, you must set the REPLICATE flag to ON for the table using an ALTER TABLE statement:

ALTER TABLE news
REPLICATE ON
go

This is equivalent to running the sp_setreplicate or sp_setreptable procedure on the table in Adaptive Server Enterprise. REPLICATE ON can not be set in a CREATE TABLE statement.

Add Replication Server information to the replicate database

You should run the rssetup.sql command file on the replicate database in exactly the same manner as it was run on the primary database.

You should also ensure that the dbmaint and sa users can access this table without explicitly specifying the table owner.

For Info These tasks are the same as those carried out on the primary database. For a description of how to carry them out, see Add Replication Server information to the primary database.

Create the tables for the replicate database

The replicate site database needs to have tables created to hold the data it receives. You should create these tables at this point. As long as the database elements are in place, no extra statements are required for them to act as a replicate site in a Replication Server installation. In particular, you do not need to set the REPLICATE flag to ON; this is required only at the primary site.

Replication Server allows replication between tables and columns with different names. As a simple example, however, create a table in the replicate database identical in definition to that in the primary database (except for the REPLICATE flag, which is not set to ON in the replicate database). The table creation statement for this table is:

CREATE TABLE news (
   ID int,
   AUTHOR char( 40 ) DEFAULT CURRENT USER,
   TEXT char( 255 ),
   PRIMARY KEY ( ID, AUTHOR ) 
)
go

For the tutorial, the CREATE TABLE statement must be exactly the same as that at the primary site.

You must ensure that the users dbmaint and sa can access this table without specifying the owner name. Also, these user IDs must have SELECT and UPDATE permissions on the table.

Set up Replication Server

You need to carry out the following tasks on the Replication Server:

Create a connection for the primary site data server.
Create a connection for the replicate site data server.
Create a replication definition.
Create a subscription to the replication.

This section describes each of these tasks. It also describes starting the Adaptive Server Anywhere LTM.

Create a connection for the primary site

Using isql, connect to Replication Server and create a connection to the primary site Adaptive Server Anywhere database.

The following command creates a connection to the primedb database on the PRIMEDB Open Server.

create connection to PRIMEDB.primedb
set error class rs_sqlserver_error_class
set function string class rs_sqlserver_function_class
set username dbmaint
set password dbmaint
with log transfer on
go

If you have changed the dbmaint user ID and password in the rssetup.sql command file, make sure you replace the dbmaint username and password in this command.

The primedb database name is not actually used by Replication Server; instead, the database name is read from the command line of the PRIMEOS Open Server. You must, however, include a database name in the CREATE CONNECTION statement to conform to the syntax.

For Info For a full description of the create connection statement, see your Replication Server Commands Reference.

Create a connection for the replicate site.

Using isql, connect to Replication Server and create a connection to the replicate site Adaptive Server Anywhere database.

The following command creates a connection to the repdb database on the REPDB Open Server.

create connection to REPDB.repdb
set error class rs_sqlserver_error_class
set function string class rs_sqlserver_function_class
set username dbmaint
set password dbmaint
go

This statement differs from that for the primary site server in that there is no with log transfer on clause.

If you have changed the dbmaint user ID and password in the rssetup.sql command file, make sure you replace the dbmaint username and password in this command.

Create a replication definition

Using isql, connect to Replication Server and create a replication definition. The following statement creates a replication definition for the news table on the primedb database:

create replication definition news
with primary at PRIMEDB.primedb
( id int, author char(40), text char(255) )
primary key ( id, author )
go

For a full description of the CREATE REPLICATION DEFINITION statement, see your Replication Server Commands Reference.

Configure and start the Adaptive Server Anywhere LTM

For replication to take place, the Adaptive Server Anywhere LTM must be running against the primary site server. Before you start the Adaptive Server Anywhere LTM, it must be properly configured by editing an LTM configuration file.

A sample configuration file for the primedb database is as follows. If you are following the examples, you should make a copy of this file as prime.cfg:

#
# Configuration file for 'PRIMELTM'
#
SQL_server=PRIMEDB
SQL_database=primedb
SQL_user=sa
SQL_pw=sysadmin
RS_source_ds=PRIMEDB
RS_source_db=primedb
RS=your_rep_server_name_here
RS_user=sa
RS_pw=sysadmin
LTM_admin_user=dba
LTM_admin_pw=sql
LTM_charset=cp850
scan_retry=2
APC_user=sa
APC_pw=sysadmin
SQL_log_files=C:\TUTORIAL

If you have changed the user ID and password in the rssetup.sql command file from sa and sysadmin, you should use the new values in this configuration.

To start the Adaptive Server Anywhere LTM running on the primary site server use the following command:

dbltm -S PRIMELTM -C prime.cfg

The connection information is in prime.cfg. In this command line, PRIMELTM is the server name of the LTM.

You can find usage information about the Adaptive Server Anywhere LTM by typing the following statement:

dbltm -?

The Adaptive Server Anywhere LTM for Windows NT can be run as an NT service. For information on running programs as services, see Running the server as a Windows NT service.

Create a subscription for your replication

Using isql, connect to Replication Server and create a subscription for the replication.

The following statement creates a subscription for the news replication defined in Create a replication definition with replicate site as the repdb database.

create subscription NEWS_SUBSCRIPTION
for news
with replicate at REPDB.repdb
go

Your installation is now complete. You should try replicating data to confirm that the setup is working properly.

Enter data at the primary site for replication

You can now replicate data from the primary to the replicate database. As an example, connect to the primary database using the isql utility, and enter a row in the news table.

insert news (id, text)
values (1, 'Test news item.' )
commit
go

The Adaptive Server Anywhere LTM sends only committed changes to the Replication Server. The data change is replicated next time the LTM polls the transaction log.

Tutorial complete

You have now completed the tutorial. The following section describes in more detail the steps you have carried out.