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

Configuring databases for Replication Server

    Each Adaptive Server Anywhere database that participates in a Replication Server installation needs to be configured before it can do so. Configuring the database involves the following tasks:

• Creating a Replication Server-compatible database.

• Selecting a secure user ID for the maintenance user and the name used by Replication Server when materializing data.

• Setting up the database for Replication Server.

• Configuring the language and character set, where necessary.

This section discusses all but the first of these tasks.

     For a discussion of how to create a database that is compatible with Replication Server, see Creating a Transact-SQL-compatible database.

Configuring the LTM 

Each primary site Adaptive Server Anywhere database requires an LTM to send data to Replication Server. Each primary or replicate site Adaptive Server Anywhere database requires an Open Server definition so that Replication Server can connect to the database.

     For information on configuring the LTM, see Configuring the LTM.

  Setting up the database for Replication Server

Once you have created your Adaptive Server Anywhere database, and created the necessary tables and so on within the database, you can set the database up ready for use with Replication Server. You do this using a setup script supplied with the Adaptive Server Anywhere Replication Agent product. The script is named rssetup.sql.

When you need to run the setup script 

The setup script needs to be run at any Adaptive Server Anywhere database that is taking part in a Replication Server installation, whether as a primary or a replicate site.

What the setup script does 

The setup script carries out the following functions:

• Creates user IDs required by Replication Server when connecting to the database.

• Creates a set of stored procedures and tables used by Replication Server. The tables begin with the characters rs_, and the procedures begin with the characters sp_.

  Included in the procedures are some that are important for character set and language configuration.

  Prepare to run the setup script

Replication Server uses a special data server maintenance user login name for each local database that contains replicated tables. This allows Replication Server to maintain and update the replicated tables in the database.

The maintenance user 

The setup script creates a maintenance user with name dbmaint and password dbmaint. The maintenance user is granted DBA permissions in the Adaptive Server Anywhere database, which allows it full control over the database. For security reasons, you should change the maintenance user ID and password.

  To change the maintenance user ID and password:

1. Open the rssetup.sql setup script in a text editor.

2. Change all occurrences of the dbmaint user ID to the new maintenance user ID of your choice. The user ID occurs in the following places at the top of the setup script file:

   grant connect to dbmaint identified by dbmaint
   grant dba to dbmaint
   grant group to dbmaint
   grant connect to sa identified by sysadmin
   grant dba to sa
   grant membership in group dbmaint to sa
   go
   
   connect dbmaint identified by dbmaint
   go

3. Change all occurrences of the dbmaint password to the new maintenance user password of your choice. The password occurs in the following places at the top of the setup script file:

   grant connect to dbmaint identified by dbmaint
   grant dba to dbmaint
   grant group to dbmaint
   grant connect to sa identified by sysadmin
   grant dba to sa
   grant membership in group dbmaint to sa
   go
   
   connect dbmaint identified by dbmaint
   go

The materialization user ID 

When Replication Server connects to a database to materialize and initial copy of the data in the replication, it does so using the Replication Server system administrator account.

The Adaptive Server Anywhere database must have a user ID and password that match the Replication Server system administrator user ID and password. A NULL password is not allowed by Adaptive Server Anywhere.

The setup script assumes a user ID of sa and a password of sysadmin for the Replication Server administrator. You should change this to match the actual name and password.

  To change the system administrator user ID and password:

1. Open the rssetup.sql setup script in a text editor.

2. Change all occurrences of the sa user ID to match the Replication Server system administrator user ID. The user ID occurs in the following places at the top of the setup script file:

   grant connect to dbmaint identified by dbmaint
   grant dba to dbmaint
   grant group to dbmaint
   grant connect to sa identified by sysadmin
   grant dba to sa
   grant membership in group dbmaint to sa
   go

3. Change all occurrences of the sa password to match the Replication Server system administrator password. The password has the initial setting of sysadmin and occurs in the following place at the top of the setup script file:

   grant connect to dbmaint identified by dbmaint
   grant dba to dbmaint
   grant group to dbmaint
   grant connect to sa identified by sysadmin
   grant dba to sa
   grant membership in group dbmaint to sa
   go

  Run the setup script

Once you have modified the setup script to match the user IDs and passwords appropriately, you can run the setup script to create the Replication Server procedures and tables in the Adaptive Server Anywhere database.

  To run the setup script:

1. Start the Adaptive Server Anywhere database on a Adaptive Server Anywhere database engine or server.

2. Start the Interactive SQL utility, and connect to the database as a user with DBA authority. When a Adaptive Server Anywhere database is created, it has a user with user ID DBA and password SQL, which has DBA authority.

3. Run the script by entering the following command in the Interactive SQL command window:

   read path\rssetup.sql

   where path is the path to the setup script.

  Character set issues

Each Adaptive Server Anywhere database is assigned a specific collation (character set and sort order) when it is created. Replication Server uses a different set of identifiers for character sets and sort orders.

When Replication Server connects to a database, it runs a procedure named sp_serverinfo, which provides information to Replication Server about the server. Among those pieces of information are the character set and the sort order.

As Replication Server expects to receive an Open Client/Open Server character set and sort order when it connects, the Adaptive Server Anywhere sp_serverinfo procedure uses a translation table to convert the Adaptive Server Anywhere collation into the equivalent Open Client/Open Server names, for provision to Replication Server.

  To find the collation sequence used by a Adaptive Server Anywhere database:

1. Connect to the database from Sybase Central, right click on the database icon in the left pane, and select Properties from the pop-up menu. The collation is listed on the Extended Information tab. Or:

2. Run the dbinfo command-line utility. For example, the following command provides information about a database named asademo running on a server named asademo:

   dbinfo -c "uid=dba;pwd=sql;eng=asademo;dbn=asademo"

Automatically supported collations 

The setup script includes a set of mappings from Adaptive Server Anywhere collations to Open Client/Open Server character sets and sort orders. These mappings are as follows:

Adaptive Server Anywhere collation	Open Server character set	Open Server sort order (case sensitive)	Open Server sort order (case insensitive)
default	cp850	dictionary_cp850	nocase_850
850	cp850	dictionary_cp850	nocase_850
SJIS	sjis	bin_sjis	bin_sjis
EUC_JAPAN	eucjis	bin_eucjis	bin_eucjis

Adding a language mapping 

If you wish to use a character set and sort order other than those listed in the table, you must add a correspondence between the Adaptive Server Anywhere collation and the Open Client/Open Server character set and sort order to the Adaptive Server Anywhere database.

  To add a character set mapping to a database:

1. Run the sp_add_rs_collation procedure. The arguments are as follows:

   Parameter	Description
   @collation_name	Adaptive Server Anywhere collation name
   @cs_name	Open Client/Open Server character set name
   @so_name_case	Open Client/Open Server sort order (case sensitive)
   @so_name_caseless	Open Client/Open Server sort order (case insensitive)

   A final argument, @user_defined, has the default setting of 'Y'. You should not supply a @user_defined parameter.

     For a full description of Adaptive Server Anywhere collations, see Database Collations and International Languages.

     For more information on character set issues, see Language and character set issues.

Removing a language mapping 

If you wish to change a language mapping, you need to first remove the existing mapping and then add a new one.

  To remove a language mapping:

1. Run the sp_add_rs_collation procedure.

   The sp_add_rs_collation procedure takes a single argument, @collation_name, which is the Adaptive Server Anywhere collation name.