{"id":2648,"date":"2018-09-05T14:38:01","date_gmt":"2018-09-05T14:38:01","guid":{"rendered":"http:\/\/dbtut.com\/?p=2648"},"modified":"2018-12-06T13:08:25","modified_gmt":"2018-12-06T13:08:25","slug":"sap-sybase-ase-warm-standby-database-using-replication-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/09\/05\/sap-sybase-ase-warm-standby-database-using-replication-server\/","title":{"rendered":"SAP Sybase ASE Warm Standby Database using Replication Server"},"content":{"rendered":"
<\/p>\n
We are going to create warm standby backup of SAP Sybase ASE Prod1<\/strong> server to Standby1<\/strong> server using Sybase Replication Server REP15_7\u00a0<\/strong>for a database named : testdb. It comprises to the following 15 steps.<\/em><\/p>\n
Pre-Requisites : <\/strong><\/p>\n
a) 2 Sybase ASE instances<\/p>\n
b)1 replication server instance<\/p>\n
c)1 database named testdb<\/p>\n
<\/p>\n
Step 1: Enable Replication Agent on both Sybase ASE Servers<\/strong><\/p>\n
Step 2: Create a login that on both Sybase ASE Servers and assign replication_role <\/strong><\/p>\n
These logins will be used in Replication Process<\/p>\n
Create logins \u00a0on both server<\/p>\n
use master\r\ngo\r\nsp_addlogin test_maint,xxxxx\r\ngo\r\ngrant role replication_role to test_maint\r\ngo<\/pre>\n
<\/p>\n
Step 3: Add the user to the dbo profile of the database that is invlovled in this process.<\/strong><\/p>\n
use testdb\r\ngo\r\nsp_addalias test_maint,dbo\r\ngo<\/pre>\n
<\/p>\n
Step 4: Synchronize\u00a0the logins on both the servers if required<\/b><\/p>\n
execute this to export logins for primary server:<\/p>\n
bcp master..syslogins out \/tmp\/logins.txt -c -SProd1 -Usa -P\r\n\r\nexecute this to import logins on secondary server:\r\n\r\nbcp master..syslogins in \/tmp\/logins.txt -c -SStandby1 -Usa -P<\/pre>\n
<\/p>\n
Step 5: Install the replication script for both Primary & Standby Servers<\/b><\/p>\n
Login to rep server and browse the script from location $SYBASE\/REP15-5_5\/scripts and execute this:<\/p>\n
Step 7: Create a connection from replication serer to active database<\/b><\/p>\n
create connection to Prod1.testdb\r\nset error class to rs_sqlserver_error_class\r\nset function string class to rs_sqlserver_function_class\r\nset username to test_maint\r\nset password to xxxxx\r\nwith log transfer on\r\nas active for LASE.testdb<\/pre>\n
<\/p>\n
Step 8:\u00a0<\/b>Create a login in the replication server<\/b><\/p>\n
This user will be used to connection replication agent from Sybase ASE Servers<\/p>\n
create user rep_user\r\nset password '123456'\r\nset password_expiration to '0'\r\ngo\r\ngrant connect source to rep_user\r\ngo<\/pre>\n
<\/p>\n
Step 9:\u00a0Create a connection from the replication server to the standby server<\/b><\/p>\n
create connection to Standby1.testdb\r\nset error class to rs_sqlserver_error_class\r\nset function string class to rs_sqlserver_function_class\r\nset username to test_maint\r\nset password to xxxxx\r\nwith log transfer on\r\nas standby for LASE.testdb\r\nuse dump marker\r\ngo<\/pre>\n
<\/p>\n
Step 10:\u00a0Check the logical connections<\/b><\/p>\n
execute the following command on replication server<\/p>\n
Step 11:\u00a0<\/b>Mark the database for replication and grant permissions<\/span><\/strong><\/p>\n
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:<\/p>\n
grant execute on rs_update_lastcommit to public\r\ngrant execute on rs_marker to public\r\ngrant execute on rs_check_repl_stat to public\r\ngo\r\nsp_reptostandby 'testdb','all'\r\ngo<\/pre>\n
<\/p>\n
Step 12:\u00a0Configure Replication Agent on primary server<\/b><\/p>\n
Execute the following command on primary server to configure replication agent on primary server<\/p>\n
use testdb\r\ngo\r\n\r\nsp_config_rep_agent 'testdb', 'enable', 'REP15_7', 'rep_user', 'xxxxx'\r\ngo\r\nsp_config_rep_agent 'testdb', 'send warm standby xacts', true\r\ngo\r\nsp_setreplicate rs_marker,'true'\r\ngo\r\nsp_setreplicate rs_update_lastcommit,'true'\r\ngo\r\n\r\nExecute the following command to start replication agent on primary server\r\n\r\nsp_start_rep_agent 'testdb'\r\ngo<\/pre>\n
<\/p>\n
Check the error log to verify the status of replication agent.<\/p>\n
Step 13:\u00a0Dump the database on primary server<\/b><\/p>\n
dump database testdb to '\/tmp\/test_bk.dat'<\/pre>\n
<\/p>\n
Step 14:\u00a0Restore the database on standby server<\/b><\/p>\n
load database testdb from '\/tmp\/test_bk.dat'\r\ngo\r\nonline database testdb\r\ngo<\/pre>\n
<\/p>\n
Step 15:\u00a0Resume the connection on Primary & Standby Servers and verify the replication status<\/b><\/p>\n
Connect the replication server and execute the following commands to resume the connections of primary & secondary servers to replication server respectively.<\/p>\n
resume connection to Prod1.testdb\r\ngo\r\nresume connection to Standby1.testdb\r\ngo\r\nCheck the replication status by the following commands\r\nadmin logical_status\r\ngo\r\nadmin who_is_down\r\ngo<\/pre>\n
<\/p>\n
Step 16:\u00a0Validate the replication between primary & standby servers<\/b><\/p>\n
Primary:<\/p>\n
use testdb\r\ngo\r\nCREATE TABLE dbo.TEST1 ( COL1 int NOT NULL, COL2 varchar(25) NULL ) LOCK ALLPAGES\r\nGO\r\n\r\ninsert into dbo.TEST1 values (5,'B')\r\n\r\nselect * from TEST1\r\n\r\nSecondary:\r\n\r\nselect * from TEST1<\/pre>\n