Preparing your Adaptive Server Enterprise server

Data Replication with SQL Remote
PART 1. Introduction to SQL Remote
CHAPTER 3. Setting Up SQL Remote

Preparing your Adaptive Server Enterprise server

Before you start

This section assumes the following:

You have installed an Adaptive Server Enterprise server that is to contain the SQL Remote database.
You have installed the SQL Remote software on your computer. To install the SQL Remote software, run the setup program from the CD-ROM.
You have created a database in the Adaptive Server Enterprise server that will take part in your SQL Remote installation.
You have system administrator permissions on the Adaptive Server Enterprise server, and database owner permissions in the database.

Ensuring TEMPDB is large enough

SQL Remote uses the TEMPDB database for the following purposes:

The database extraction utility used to create remote databases uses TEMPDB to hold a temporary set of Adaptive Server Anywhere system tables.
The Message Agent creates a temporary table called #remote when it connects to the server.

For these reasons, you should make TEMPDB larger than the 2 Mb default size. The size required depends on the number of tables and columns in your SQL Remote installation, but a size of 10 Mb is generally sufficient.

Installing SQL Remote into a database from Sybase Central

You can install SQL Remote into a database from the Sybase Central graphical administration tool. Sybase Central is available on the Windows NT and Windows 95 operating systems.

To install SQL Remote into a database from Sybase Central:

Connect to Adaptive Server Enterprise from Sybase Central, as a user with system administrator privileges.
Open the Databases folder for the server.
Open the SQL Remote folder in the database.
In the right pane, double-click Setup SQL Remote, and follow the instructions in the Wizard.

Command-line installation of the SQL Remote system objects

For a database in your Adaptive Server Enterprise server to take part in a SQL Remote installation, you must install a number of SQL Remote system tables, views, and stored procedures in your database.

To install the SQL Remote system objects:

Locate the SQL Remote initialization script ssremote.sql in your SQL Remote installation directory.
Make a backup copy of the ssremote.sql script file. Then add the following two lines to the beginning of ssremote.sql:
```
use database_name
go
```
where database_name is the name of the database to take part in SQL Remote replication.

These two lines set the current database to database_name, so that the SQL Remote tables are created in the database_name database. The SQL Remote tables are owned by the database owner.
Run the script against your Adaptive Server Enterprise server.

Change to the directory containing the script file and enter the following command line (which should be entered all on one line) to run the script:
```
isql -S server-name -U login_id -P password -i ssremote.sql -o logfile
```
where server-name is the name of the Adaptive Server Enterprise, login_id and password correspond to a user with system administrator permissions on the server who owns the database, and logfile is the name of a log file to hold the log information from the script.
Inspect the log file to confirm that the tables and procedures were created without error.

The script creates a set of SQL Remote system objects in the database.

The SQL Remote system objects

The script creates the following objects in the database:

SQL Remote system tables A set of tables used to maintain SQL Remote information. These tables have names beginning with sr_.
SQL Remote system views A set of views that hold the SQL Remote information in a more understandable form. These views have names beginning with sr_, and ending in s.
SQL Remote system procedures A set of stored procedures used to carry out SQL Remote configuration and administration tasks. These procedures have names beginning with sp_, indicating their system management roles.

Caution: Do not edit the SQL Remote system tables
Do not, under any circumstances, alter the SQL Remote system tables directly. Doing so may corrupt the table and make it impossible for SQL Remote to function properly. Use Sybase Central or the SQL Remote system procedures to carry out all system administration tasks.

Command-line installation of the stable queue

The stable queue is a pair of database tables that hold transactions that may still be needed by the replication system. Every Adaptive Server Enterprise database participating in a SQL Remote installation needs a stable queue.

For Info For detailed information about the stable queue, see The stable queue.

The stable queue can exist in the same database as the database taking part in SQL Remote, or in a separate database. Keeping the stable queue in a separate database complicates the backup and recovery plan, but can improve performance by putting the stable queue workload on separate devices and/or a separate Adaptive Server Enterprise server.

To install the stable queue:

Locate the stable queue initialization script stableq.sql in your SQL Remote installation directory.
Make a backup copy of the stableq.sql script file. Then add the following two lines to the beginning of stableq.sql:
```
use database_name
go
```
where database_name is the name of the database that will hold the stable queue.

These two lines set the current database to database_name, so that the stable queue is created in the database_name database. The stable queue tables are owned by the database owner.
Run the script against your Adaptive Server Enterprise server.

Change to the directory holding the stable queue script, and enter the following command line (which should be entered all on one line) to run the script:
```
isql -S server-name -U login_id -P password -i STABLEQ.SQL -o logfile
```
where server-name is the name of the Adaptive Server Enterprise, login_id and password correspond to a user with system administrator permissions on the server who owns the database, and logfile is the name of a log file to hold the log information from the script.
Inspect the log file to confirm that the tables and procedures were created without error.