Warm Standby setup instructions

Sybase Replication Server allows you to create and maintain a warm standby environment. The creation can be done with, for instance, the rs_init utility or Sybase Central. In some cases extra flexibility is needed and this can best be achieved when command line statements are used. For various other reasons people prefer using this method.

This page describes the steps to be taken to create a warm standby environment through a combination of commands given to ASE and Replication Server. It should be considered as a kind of worksheet. Feel free to make changes to suit your individual need. This might be needed when you are dealing with a database with big table(s) holding text or image datatypes or you want to optimize performance using replication definitions and subscriptions.

Assumptions of the environment

• The setup makes the following assumptions:
• The Replication Server is already configured and running.
• Replication is done between ASE servers
• Materialization will be done with a dump-load scenario
• Replication is performed without replication definitions and subscriptions
• User activity will continue throughout the creation process

The setup is divided into a number of steps, where each step is a separate unit of work.

Preparation of the Sybase ASE-servers

Create a maintenance user on both ASE's

The maintenance user is used by the replication server to apply transactions. Make sure that the name and password of the maintenance user on both servers are identical. For practical reasons make the maintenance user aliased to "dbo" in the database. Since a dump-load scenario is used to materialize the standby database, make sure that the suid of the maintenance user on both servers are identical.

sp_addlogin "<maintenance_user>","<maintenance_user_password>"
go
grant role replication_role to "<maintenance_user>"
go
-- check the value of the suid
select suser_id("<maintenance_user>")
go
use <database>
go
sp_addalias "<maintenance_user>","dbo"
go

Install the replication server stored procedures and tables

To do this, make a copy of the script rs_install_primary.sql located in the $SYBASE/$SYBASE_REP/scripts directory. Comment out 2 commands of the script: the dbcc settrunc and the sp_setreplicate commands. These can be found at the end of the file, or just above the definition of the rs_send_repserver_cmd stored procedure.

The commands that are skipped will be executed at a later stage, when the replication agent is configured. Apply the script on the active server in the right database.

isql -Usa -P<password> -S<active_server> -D<database> -i changed_rs_install_primary.sql 

Configure both servers for replication

sp_configure "enable rep agent threads",1

Preparation of the Replication Server

Create a logical connection on the replication server

The name of it does not have to match with the name of the primary database server and database, but this convention is widely used.

create logical connection to <active_server>.<database>

Create a connection from the replication server to the active database

create connection to <active_server>.<database>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <maintenance_user>
set password to <maintenance_user_password>
with log transfer on
as active for <active_server>.<database>

Create a login in the replication server

This login is used by the rep-agent running in the ASE to connect to the replication server.

create user <rep-agent_user>
set password <rep-agent_password>
set password_expiration to '0'
go
grant connect source to <rep-agent_user>
go

Configure the RepAgent

The configuration of the Replication Agent within the active ASE should now be done. All steps within this paragraph should be executed within a controlled time frame since the transaction log cannot be cleared between the execution of the first sp_config_rep_agent and the sp_start_rep_agent. The configuration is only needed on the primary server. Since we use a dump-load scenario the configuration is copied to the standby server during the materialization phase.

use <database>
go
sp_config_rep_agent "<database>", "enable", "<repserver>", "<rep-agent_user>", "<rep-agent_password>"
go
sp_config_rep_agent "<database>", "send warm standby xacts", true
go
sp_setreplicate rs_marker,"true"
go
sp_setreplicate rs_update_lastcommit,"true"
go
sp_start_rep_agent "<database>"
go

Mark the database for replication and grant permissions

Execute the sp_reptostandby stored procedure in the active ASE to activate replication from the active database to the replication server.

We also need to grant permissions to a few stored procedures.

use <database>
go
grant execute on rs_update_lastcommit to public
grant execute on rs_marker to public
grant execute on rs_check_repl_stat to public
go
sp_reptostandby "<database>","all"
go

Configure the replication server for the standby database

All steps within this paragraph should be executed within a controlled time frame since the stable queue in the replication server cannot be cleared between the execution of the create connection and resume connection.

Create a connection from the replication server to the standby server

create connection to <standby-server>.<database>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <maintenance_user>
set password to <maintenance_user_password>
with log transfer on
as standby for <active_server>.<database>
use dump marker 

Dump the database on the active server so it can be loaded into the standby server

dump database <database> to "<file-name>" 

Load the database dump into the standby server

load database <database> from "<file-name>"
go
online database <database>
go

Start the connection from the replication server to the standby database

resume connection to <standby-server>.<database>

Congratulations, you now have a working Warm Standby configuration!