How to Install a warm standby Replication Server

This section will contain a step by step instruction on how to setup a warm standby replication.
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.

This assumes that the databases in and the databases on are present and exactly the same size. The databases on do not need to have data in it, but need to be created and online.

isql –Usa –P -S

sp_addlogin "maint_user","maint123"
go
grant role replication_role to "maint_user"
go
-- check the value of the suid
select suser_id("maint_user")
go
use
go
sp_addalias "maint_user","dbo"
go

isql –Usa –P -S

sp_addlogin "maint_user","maint123"
go
grant role replication_role to "maint_user"
go
-- check the value of the suid
select suser_id("maint_user")
go
use
go
sp_addalias "maint_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. Remove the last two commands of the script (the dbcc settrunc and the sp_setreplicate commands). 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 -S –D -i changed_rs_install_primary.sql
isql -Usa -P -S –D -i changed_rs_install_primary.sql


Configure both servers for replication

This step can be repeated several times. The setting is dynamic and no restart of ASE is necessary.

isql -Usa -P -S

sp_configure "enable rep agent threads",1
go

isql -Usa -P -S
sp_configure "enable rep agent threads",1
go


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.

isql -Usa -P -S_rs

create logical connection to .
go


Create a connection from the replication server to the active database

isql -Usa -P -S_rs

create connection to .
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to
set password to
with log transfer on
as active for .
go

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.

isql -Usa -P -S_rs

create user set password
go
grant connect source to
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.

isql -Usa -P -S

use
go
-- This will clear previously installed rep agents. How harm if none are present.
sp_config_rep_agent "", "disable"
go
-- Continue installing rep agent.
sp_config_rep_agent "", "enable", "_rs", "", ""
go
sp_config_rep_agent "", "send warm standby xacts", true
go
sp_setreplicate rs_marker,"true"
go
sp_setreplicate rs_update_lastcommit,"true"
go
sp_start_rep_agent ""
go


Mark the database for replication

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

isql -Usa -P -S

use
go
sp_reptostandby "","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


isql -Usa -P -S_rs

create connection to .
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to
set password to
with log transfer on
as standby for .
use dump marker
go

STOP: Before continuing perform the following checks.

isql -Usa -P -S_rs

admin logical_status
go


This should show both connections active and with the /Awaiting to enable marker clause at the standby connection. Nowhere should it read suspended.

isql -Usa -P -S_rs

admin who_is_down
go

There should be only 2 entries. DSI down . and DSI down .pcrdb. Everything else should be up.
Dump the database on the active server so it can be loaded into the standby server

isql -Usa -P -S

dump database to ""
go

Load the database dump into the standby server

isql -Usa -P -S

load database from ""
go
online database
go


Start the connection from the replication server to the standby database

isql -Usa -P -S_rs

resume connection to .
go


Test Replication Connection


isql -Usa -P -S

use
go
--create a dummy table. Skip this step if this table already exists.
create table test (a int)
go
insert test values(1)
go


isql -Usa -P -S

use
go
select * from test
go
--you should see the following
a
-----
1