Lesson 5: 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 for use by Replication Server. The SQL command file rssetup.sql is included with SQL Anywhere and performs these tasks.

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

Run the rssetup script

  1. From Interactive SQL, connect to the SQL Anywhere database as a user with DBA authority.

  2. Run the rssetup script using the following command:

    read "install-dir\scripts\rssetup.sql"

    In this script, install-dir is your SQL Anywhere installation directory.

    Alternatively, you can choose File » Run Script, and browse to the file.

Actions performed by rssetup.sql

The rssetup.sql command file performs the following functions:

  • Creates a user named dbmaint, with password dbmaint, who has DBA authority. 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, who has DBA authority. 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 authority have full authority in a SQL Anywhere database.

The user ID sa and its password must match that of the system administrator account on the Replication Server. SQL Anywhere does not currently accept a NULL password.

Permissions

The rssetup.sql script performs 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, 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 user DBA owns the table, you should grant group permissions to DBA:

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;