Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We are going to create warm standby backup of SAP Sybase ASE Prod1 server to Standby1 server using Sybase Replication Server REP15_7 for a database named : testdb. It comprises to the following 15 steps.
Pre-Requisites :
a) 2 Sybase ASE instances
b)1 replication server instance
c)1 database named testdb
Step 1: Enable Replication Agent on both Sybase ASE Servers
1 | sp_configure 'enable rep agent threads' ,1 |
Step 2: Create a login that on both Sybase ASE Servers and assign replication_role
These logins will be used in Replication Process
Create logins on both server
1 2 3 4 5 6 | use master go sp_addlogin test_maint,xxxxx go grant role replication_role to test_maint go |
Step 3: Add the user to the dbo profile of the database that is invlovled in this process.
1 2 3 4 | use testdb go sp_addalias test_maint,dbo go |
Step 4: Synchronize the logins on both the servers if required
execute this to export logins for primary server:
1 2 3 4 5 | bcp master..syslogins out /tmp/logins.txt -c -SProd1 -Usa -P execute this to import logins on secondary server: bcp master..syslogins in /tmp/logins.txt -c -SStandby1 -Usa -P |
Step 5: Install the replication script for both Primary & Standby Servers
Login to rep server and browse the script from location $SYBASE/REP15-5_5/scripts and execute this:
1 2 3 | isql -Usa -P<password> -SProd1 -Dtestdb -i changed_rs_install_primary.sql & isql -Usa -P<password> -SStandby1 -Dtestdb -i changed_rs_install_primary.sql |
Step 6: Create logical connection on replication server
Connect to the replication server and execute this
1 2 | create logical connection LASE.testdb go |
Step 7: Create a connection from replication serer to active database
1 2 3 4 5 6 7 | create connection to Prod1.testdb set error class to rs_sqlserver_error_class set function string class to rs_sqlserver_function_class set username to test_maint set password to xxxxx with log transfer on as active for LASE.testdb |
Step 8: Create a login in the replication server
This user will be used to connection replication agent from Sybase ASE Servers
1 2 3 4 5 6 | create user rep_user set password '123456' set password_expiration to '0' go grant connect source to rep_user go |
Step 9: Create a connection from the replication server to the standby server
1 2 3 4 5 6 7 8 9 | create connection to Standby1.testdb set error class to rs_sqlserver_error_class set function string class to rs_sqlserver_function_class set username to test_maint set password to xxxxx with log transfer on as standby for LASE.testdb use dump marker go |
Step 10: Check the logical connections
execute the following command on replication server
1 2 3 4 5 6 7 8 9 10 | admin logical_status go LASE.testdb [107] Prod1.testdb Suspended/ [108] Standby1.testdb Active/ [16777317] REP15_7 None None |
Step 11: Mark the database for replication and grant permissions
Execute the following command on primary server to grant permissions and activate the database to capture all activities for production database by executing the commands:
1 2 3 4 5 6 | 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 'testdb','all' go |
Step 12: Configure Replication Agent on primary server
Execute the following command on primary server to configure replication agent on primary server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | use testdb go sp_config_rep_agent 'testdb', 'enable', 'REP15_7', 'rep_user', 'xxxxx' go sp_config_rep_agent 'testdb', 'send warm standby xacts', true go sp_setreplicate rs_marker,'true' go sp_setreplicate rs_update_lastcommit,'true' go Execute the following command to start replication agent on primary server sp_start_rep_agent 'testdb' go |
Check the error log to verify the status of replication agent.
Step 13: Dump the database on primary server
1 | dump database testdb to '/tmp/test_bk.dat' |
Step 14: Restore the database on standby server
1 2 3 4 | load database testdb from '/tmp/test_bk.dat' go online database testdb go |
Step 15: Resume the connection on Primary & Standby Servers and verify the replication status
Connect the replication server and execute the following commands to resume the connections of primary & secondary servers to replication server respectively.
1 2 3 4 5 6 7 8 9 | resume connection to Prod1.testdb go resume connection to Standby1.testdb go Check the replication status by the following commands admin logical_status go admin who_is_down go |
Step 16: Validate the replication between primary & standby servers
Primary:
1 2 3 4 5 6 7 8 9 10 11 12 | use testdb go CREATE TABLE dbo.TEST1 ( COL1 int NOT NULL, COL2 varchar(25) NULL ) LOCK ALLPAGES GO insert into dbo.TEST1 values (5,'B') select * from TEST1 Secondary: select * from TEST1 |
DONE!!!
Great article, replication explained in easy steps.
Thanks!
Dear Engr. Mohammad Rizwan Yasin,
Good article. A major step needs to be added. You will have to disable rep agent on standby and to disable the secondary truncation point in to avoid the transaction log on DR to fill up.
This can be done via connecting to Standby and run:
use db_name
go
sp_config_rep_agent db_name, ‘enable’
go
dbcc settrunc (‘ltm’,ignore)
go
Sorry!
Typo error:
sp_config_rep_agent db_name, ‘disable’
go
Dear Engr. Mohammad Rizwan Yasin,
I tried as mentioned in the article but it is not working. data is not getting replicated from primary to standby. I can see there are no issues from ‘admin who’ command. Please help me how can I achieve the replicaiton of data.
Hi Rizwan,
I need your help if possible for you. My setup detail is shared below;
SAPASESRV1: This is SAP ASE Database VM which i am considering as Primary
SAPASESRV2: This is SAP ASE Database VM which i am considering as Standby
SAPASEREP1: This is Replication Server VM where i am creating Replication Server Instance.
While creating replication server instance i am errors shared below;
2021/03/29 12:42:15 END ENVIRONMENT INFORMATION
2021/03/29 12:42:15 Directory service provider: ‘InterfacesDriver’.
2021/03/29 12:42:15 Using resource file ‘/sybase/SybaseRepServer/REP-16_0/init/rs/PRS.rs’ for product attributes.
2021/03/29 12:42:15 CONNECTIVITY ERROR: CT-Library error: ‘ct_connect():
directory service layer: internal directory control layer
error: Requested server name not found.’.
2021/03/29 12:42:15 CONNECTIVITY ERROR: Login attempt failed: server = ‘sunakl505i’.
2021/03/29 12:42:15 The attribute ‘do_add_id_server’ is set to no, but there is
no entry in the interfaces file for ID Server ‘PRS’.
2021/03/29 12:42:15 Attribute ‘do_add_id_server’ could not be set because ‘no’ is an invalid value.
2021/03/29 12:42:15 The attribute ‘do_add_replication_server’ is set to no, but
there is no entry in the interfaces file for Replication Server ‘PRS’.
2021/03/29 12:42:15 Attribute ‘do_add_replication_server’ could not be set because ‘no’ is an invalid value.
2021/03/29 12:42:15 There is no entry for server ‘sunakl505i’ in the interfaces
file. The server must have an existing entry in the interfaces file.
2021/03/29 12:42:15 Attribute ‘rs_rssd_sqlsrvr’ could not be set because ‘sunakl505i’ is an invalid value.
2021/03/29 12:42:15 Exiting.
2021/03/29 12:42:15 The log file for this session is
‘/sybase/SybaseRepServer/REP-16_0/init/logs/log0329.002’.
2021/03/29 12:42:15 Log close.