SAP Sybase ASE Warm Standby Database using Replication Server

Engr. Mohammad Rizwan Yasin

6 years ago

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

sp_configure 'enable rep agent threads' ,1

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

use master
go
sp_addlogin test_maint,xxxxx
go
grant role replication_role to test_maint
go

use master

sp_addlogin test_maint,xxxxx

grant role replication_role to test_maint

Step 3: Add the user to the dbo profile of the database that is invlovled in this process.

use testdb
go
sp_addalias test_maint,dbo
go

use testdb

sp_addalias test_maint,dbo

Step 4: Synchronize the logins on both the servers if required

execute this to export logins for primary server:

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

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

isql -Usa -P<password> -SProd1 -Dtestdb -i changed_rs_install_primary.sql
&
isql -Usa -P<password> -SStandby1 -Dtestdb -i changed_rs_install_primary.sql

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

create logical connection LASE.testdb
go

1 2	create logical connection LASE.testdb go

Step 7: Create a connection from replication serer to active database

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

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

create user rep_user
set password '123456'
set password_expiration to '0'
go
grant connect source to rep_user
go

create user rep_user

set password '123456'

set password_expiration to '0'

grant connect source to rep_user

Step 9: Create a connection from the replication server to the standby server

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

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

Step 10: Check the logical connections

execute the following command on replication server

admin logical_status
go
LASE.testdb
[107] Prod1.testdb
Suspended/
[108] Standby1.testdb
Active/
[16777317] REP15_7
None
None

admin logical_status

LASE.testdb

[107] Prod1.testdb

Suspended/

[108] Standby1.testdb

Active/

[16777317] REP15_7

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:

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

grant execute on rs_update_lastcommit to public

grant execute on rs_marker to public

grant execute on rs_check_repl_stat to public

sp_reptostandby 'testdb','all'

Step 12: Configure Replication Agent on primary server

Execute the following command on primary server to configure replication agent on primary server

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

use testdb

sp_config_rep_agent 'testdb', 'enable', 'REP15_7', 'rep_user', 'xxxxx'

sp_config_rep_agent 'testdb', 'send warm standby xacts', true

sp_setreplicate rs_marker,'true'

sp_setreplicate rs_update_lastcommit,'true'

Execute the following command to start replication agent on primary server

sp_start_rep_agent 'testdb'

Check the error log to verify the status of replication agent.

Step 13: Dump the database on primary server

dump database testdb to '/tmp/test_bk.dat'

1	dump database testdb to '/tmp/test_bk.dat'

Step 14: Restore the database on standby server

load database testdb from '/tmp/test_bk.dat'
go
online database testdb
go

load database testdb from '/tmp/test_bk.dat'

online database testdb

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.

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

resume connection to Prod1.testdb

resume connection to Standby1.testdb

Check the replication status by the following commands

admin logical_status

admin who_is_down

Step 16: Validate the replication between primary & standby servers

Primary:

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

use testdb

CREATE TABLE dbo.TEST1 ( COL1 int NOT NULL, COL2 varchar(25) NULL ) LOCK ALLPAGES

insert into dbo.TEST1 values (5,'B')

select * from TEST1

Secondary:

select * from TEST1

DONE!!!