Home

	Expand Search

Data Replication with SQL Remote

Part 1 Introduction to SQL Remote

Chapter 3: Setting Up SQL Remote

Chapter 3

Setting Up SQL Remote

About this chapter

This chapter describes how to add SQL Remote capabilities to your Adaptive Server Enterprise server.

Adaptive Server Enterprise users only

This chapter is required only for users of SQL Remote for Adaptive Server Enterprise. SQL Remote capability is automatically installed into Adaptive Server Anywhere databases.

This chapter assumes you have already installed the SQL Remote software onto your machine.

Setup overview

We call the collection of databases exchanging information using SQL Remote an installation. From a physical point of view, a SQL Remote installation may consist of hundreds or even thousands of databases sharing information; but as SQL Remote keeps the information in each physical database loosely consistent at a transactional level with that in other physical databases, you can also think of the whole installation as a single dispersed database.

Deploying a large-scale SQL Remote installation can involve setting up databases on many machines. While some changes to the design and setup configuration can be made on a running installation, it is highly recommended that you deploy only when you have completed a careful analysis and test of your design.

Setup tasks

Setup of a SQL Remote installation includes the following tasks:

• Preparing your server for SQL Remote

  You must take some steps to configure your Adaptive Server Enterprise to act as a SQL Remote site. These include installing the SQL Remote system objects and the stable queue system objects.

• Selecting message types

  You must decide whether you want to exchange information by file sharing, e-mail, some other message type, or a combination.

• Ensuring proper permissions are set

  Each user in the installation requires permissions on both their own database and on the consolidated database.

• Extracting remote databases

  You must extract an initial copy of each remote database from the consolidated database.

This chapter describes each of these tasks.

All administration is at the consolidated database

Like all SQL Remote administrative tasks, setup is carried out by a database administrator or system administrator at the consolidated database.

The Sybase System Administrator should perform all SQL Remote configuration tasks. See your Adaptive Server Enterprise documentation for more information about the Adaptive Server Enterprise environment.

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.

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.

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

Warning!

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 until they are no longer needed by the replication system. Every Adaptive Server Enterprise database participating in a SQL Remote installation needs a stable queue.

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.

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.

Upgrading SQL Remote for Adaptive Server Enterprise

This section describes the procedure for upgrading SQL Remote for Adaptive Server Enterprise.

As a SQL Remote installation may consist of a large number of databases, it is generally not practical to upgrade software on all machines at the same time. SQL Remote is designed so that upgrades can be carried out incrementally. It is not important what order SQL Remote machines are upgraded, as the message format is compatible with previous releases.

Back up both the consolidated database and, if it is separate, the stable queue database.

Install the new SQL Remote for Adaptive Server Enterprise software.

Run the script ssupdate.sql at the consolidated database to upgrade the SQL Remote system tables and procedures.

The ssupdate.sql script is held in your Sybase directory.

Run the script squpdate.sql at the stable queue database to upgrade the SQL Remote stable queue tables and procedures.

The squpdate.sql script is held in your Sybase directory.

The software is now upgraded.

Uninstalling SQL Remote

This section describes how to uninstall the SQL Remote objects from a database, and uninstall the stable queue from a database.

Connect to the database containing the SQL Remote objects, as a user with dbo permissions.

Run the sp_drop_sql_remote stored procedure to remove all SQL Remote objects apart from the procedure itself. The sp_drop_sql_remote procedure is installed along with the other SQL Remote objects.

exec sp_drop_sql_remote
go

Drop the sp_drop_sql_remote procedure to complete the uninsall procedure.

drop procedure sp_drop_sql_remote
go

Connect to the database containing the stable queue, as a user with dbo permissions.

Run the sp_queue_drop stored procedure to remove all stable queue objects apart from the procedure itself. The sp_queue_drop procedure is installed along with the other stable queue objects.

exec sp_queue_drop
go

Drop the sp_queue_drop procedure itself, to complete the uninstall procedure.

drop procedure sp_queue_drop
go