View source for 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>
 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==
Execute the sp_reptostandby stored procedure in the active ASE to activate replication from the active database to the replication server.

 use <database>
 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!


[[Category:RepServer]]