Technology Tips And Tricks

This post contains 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 <syb_src_db> in <syb_src_srvr> and the databases <syb_trgt_db> on <syb_trgt_srvr> are present and exactly the same size. The databases on <syb_trgt_srvr> do not need to have data in it, but need to be created and online.

isql –Usa –P<sa_password> -S<syb_src_srvr>

sp_addlogin "<rep_maint_user>","<rep_maint_pswd>"
go
grant role replication_role to "<rep_maint_user>"
go
-- check the value of the suid
select suser_id("<rep_maint_user>")
go
use <syb_src_db>
go
sp_addalias "<rep_maint_user>","dbo"
go

isql –Usa –P<sa_password> -S<syb_trgt_srvr>

sp_addlogin "<rep_maint_user>","<rep_maint_pswd>"
go
grant role replication_role to "<rep_maint_user>"
go
-- check the value of the suid
select suser_id("<rep_maint_user>")
go
use <syb_trgt_db>
go
sp_addalias "<rep_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<sa_password> -S<syb_src_srvr>
–D<syb_src_db> -i changed_rs_install_primary.sql

isql -Usa -P<sa_password> -S<syb_trgt_srvr>
–D<syb_trgt_db> -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<sa_password> -S<syb_src_srvr>

sp_configure "enable rep agent threads",1
go

isql -Usa -P<sa_password> -S<syb_trgt_srvr>
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<sa_password> -S<syb_trgt_srvr>_rs

create logical connection to <syb_src_srvr>.<syb_src_db>
go

Create a connection from the replication server to the active database

isql -Usa -P<sa_password> -S<syb_trgt_srvr>_rs

create connection to <syb_src_srvr>.<syb_src_db>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <rep_maint_user>
set password to <rep_maint_pswd>
with log transfer on
as active for <syb_src_srvr>.<syb_src_db>
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<sa_password> -S<syb_trgt_srvr>_rs

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

isql -Usa -P<sa_password> -S<syb_src_srvr>

use <syb_src_db>
go
-- This will clear previously installed rep agents.
-- Ho harm if none are present.
sp_config_rep_agent "<syb_src_db>", "disable"
go
-- Continue installing rep agent.
sp_config_rep_agent "<syb_src_db>", "enable",
"<syb_trgt_srvr>_rs", "<rep_maint_user>",
"<rep_maint_pswd>"
go
sp_config_rep_agent "<syb_src_db>", "send warm standby xacts", true
go
sp_setreplicate rs_marker,"true"
go
sp_setreplicate rs_update_lastcommit,"true"
go
sp_start_rep_agent "<syb_src_db>"
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<sa_password> -S<syb_src_srvr>

use <syb_src_db>
go
sp_reptostandby "<syb_src_db>","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<sa_password> -S<syb_trgt_srvr>_rs

create connection to <syb_trgt_srvr>.<syb_trgt_db>
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to <rep_maint_user>
set password to <rep_maint_pswd>
with log transfer on
as standby for <syb_src_srvr>.<syb_src_db>
use dump marker
go

STOP: Before continuing perform the following checks.

isql -Usa -P<sa_password> -S<syb_trgt_srvr>_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<sa_password> -S<syb_trgt_srvr>_rs

admin who_is_down
go

There should be only 2 entries. DSI down <syb_trgt_srvr>.<syb_trgt_db> and DSI down <syb_trgt_srvr>.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<sa_password> -S<syb_src_srvr>

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

Load the database dump into the standby server

isql -Usa -P<sa_password> -S<syb_trgt_srvr>

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

Start the connection from the replication server to the standby database

isql -Usa -P<sa_password> -S<syb_trgt_srvr>_rs

resume connection to <syb_trgt_srvr>.<syb_trgt_db>
go

Test Replication Connection

isql -Usa -P<sa_password> -S<syb_src_srvr>

use <syb_src_db>
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<sa_password> -S<syb_trgt_srvr>

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