SAP RS - Create a new Replication

Before declaring a Replication

We should have:

• Primary database and Replicate database/s.
• Instances were set in sql.ini.
• RSSD database.

Create a primary database

use master

go

 

-- Create devices

disk init name="RepTestPrimaryDdevice", physname="/sybvol01/sap16/data/RepTestPrimaryDdevice1.dat", size="100M"

GO

 

disk init name="RepTestPrimaryDdeviceLog", physname="/sybvol01/sap16/data/RepTestPrimaryDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestPrimary on RepTestPrimaryDdevice='100M' log on RepTestPrimaryDdeviceLog='100M'

GO

 

use RepTestPrimary

go

-- Create a Segment

sp_addsegment RepTestPrimarySeg, RepTestPrimary, RepTestPrimaryDdevice

GO

Create a replicate database

use master

go

 

-- Create devices

disk init name="RepTestReplicateDdevice", physname="/sybvol01/sap16/data/RepTestReplicateDdevice1.dat", size="100M"

GO

 

disk init name="RepTestReplicateDdeviceLog", physname="/sybvol01/sap16/data/RepTestReplicateDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestReplicate on RepTestReplicateDdevice='100M' log on RepTestReplicateDdeviceLog='100M'

GO

 

use RepTestReplicate

go

-- Create a Segment

sp_addsegment RepTestReplicateSeg, RepTestReplicate, RepTestReplicateDdevice

GO

Create a replicated table

use RepTestPrimary

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

 

-- =============================================================

use RepTestReplicate

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

• The replicated table must have a primary key!

Create connections to the primary and replicated server

cd /sybvol01/sap16rs/REP-16_0/install/

./rs_init

Add a database to the replication system

Choose: "2. Configure a Server product"

Choose: "1. Replication Server"

Choose: "2. Add a database to the replication system"

In windows:

Replication Server Information

Choose: "1. Replication Server Information"

Choose: "1. Replication Server Name"Choose: "RS SA password"

Ctrl-a Accept and Continue

In windows:

Database Information

Add the primary database

Choose: "2. Database Information"

Choose: "1. SQL Server name"
For the primary database: set "5. Will the database be replicated" to "yes".
Ctrl-a Accept and Continue

Choose: "3. SA password"

Choose: "4. Database name"

Choose: "7. Maintenance password"

Choose: "3. Database Replication Agent"

Choose: "2. RS password"

Change RS user to sa.

Ctrl-a Accept and Continue

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestPrimary' exists.

Verified that SQL Server 'SERVERNAME' supports replication.

Added maintenance user login 'RepL16TestPrimary_maint' to database 'RepL16TestP

rimary'.

Verified that maintenance user 'RepL16TestPrimary_maint' can log into SQL Serve

r 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for primary data.

Loading script 'rs_install_primary.sql' into database 'RepL16TestPrimary'.

.....Done

Loaded script 'rs_install_primary.sql' successfully.

Done

Granting permissions on the lastcommit functions and rs_marker.

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions and rs_marker.

Granted replication role to maintenance user

Task succeeded: configure database for primary data.

Running task: configure the Replication Agent.

Task succeeded: configure the Replication Agent.

Running task: set connection to the database.

Adding database 'RepL16TestPrimary' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestPrimary' is now

managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Running task: start the Replication Agent.

Task succeeded: start the Replication Agent.

Configuration completed successfully.

Press <return> to continue.

Add a replicate database

Do the same as for the primary database, with the following changes:

1. For the replicate databases: set "5. Will the database be replicated" to "no".
2. "Database Replication Agent" definition is not required.

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestReplicate' exists.

Added maintenance user login 'RepL16TestReplicate_maint' to database 'RepL16Tes

tReplicate'.

Verified that maintenance user 'RepL16TestReplicate_maint' can log into SQL Ser

ver 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for replicate data.

Loading script 'rs_install_replicate.sql' into database 'RepL16TestReplicate'.

...Done

Loaded script 'rs_install_replicate.sql' successfully.

Done

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions.

Granted replication role to maintenance user

Task succeeded: configure database for replicate data.

Running task: set connection to the database.

Adding database 'RepL16TestReplicate' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestReplicate' is no

w managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Configuration completed successfully.

Press <return> to continue.

In windows:

Create replication definition, publication and subscription

Create a replication definition

Replication definition is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create replication definition RepL16TestDef

with primary at SERVERNAME.RepL16TestPrimary

with all tables named 'FirstTable'

(ID numeric,

IntColumn int,

StrColumn varchar(20))

primary key(ID)

replicate minimal columns

go

Expected output:

Replication definition 'RepL16TestDef' is created.

Create a publication

Publication is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create publication RepL16TestPub with primary at SERVERNAME.RepL16TestPrimary

go

Expected output:

-- Publication 'RepL16TestPub' for SERVERNAME.RepL16TestPrimary is created.

Create a subscription

Subscription is defined at the replicate database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription 'RepL16TestSub' is in the process of being created.

check subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription RepL16TestSub is VALID at the replicate.

Subscription RepL16TestSub is VALID at the primary.

Validation and checks for the connections and the definitions

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

1> admin who

2> go

Spid Name State Info

---- --------------- -------------------- ------------------------------------------------------------

...

304 DSI EXEC Awaiting Command 109(1) SERVERNAME.RepL16TestPrimary

299 DSI Awaiting Message 109 SERVERNAME.RepL16TestPrimary

301 DIST Awaiting Wakeup 109 SERVERNAME.RepL16TestPrimary

303 SQT Awaiting Wakeup 109:1 DIST SERVERNAME.RepL16TestPrimary

300 SQM Awaiting Message 109:1 SERVERNAME.RepL16TestPrimary

298 SQM Awaiting Message 109:0 SERVERNAME.RepL16TestPrimary

302 REP AGENT Awaiting Command SERVERNAME.RepL16TestPrimary

311 DSI EXEC Awaiting Command 110(1) SERVERNAME.RepL16TestReplicate

310 DSI Awaiting Message 110 SERVERNAME.RepL16TestReplicate

309 SQM Awaiting Message 110:0 SERVERNAME.RepL16TestReplicate

...

Connect to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RSFOG2_RSSD

2> go

1> select * from rs_databases

2> go

dsname dbname dbid dist_status src_status ....

----------------- -------------------- ----------- ----------- ----------- ....

...

SERVERNAME RepL16TestPrimary 109 1 1 ....

SERVERNAME RepL16TestReplicate 110 1 0 ....

...

(8 rows affected)

• rs_helpdb can also be good: exec rs_helpdb.

Add database and table to replication

Connect to the primary database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> USE RepL16TestPrimary

2> go

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently false, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- add table to replication

2> sp_setreptable FirstTable, true

3> go

The replication status for 'FirstTable' is set to true, owner_off.

(return status = 0)

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently true, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- check replication status for database:

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'NONE'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

1> -- activate replication status for database

2> sp_reptostandby RepL16TestPrimary, 'all'

3> go

Warning: The execution of the stored procedure sp_reptostandby will take time to process all the tables and all the text/image columns in database 'RepL16TestPrimary'.

The replication status for database 'RepL16TestPrimary' has been set to 'ALL'.

(return status = 0)

1> -- check replication status for database

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'ALL'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

Grant permissions to maint user on replicated DB

Connect to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

use RepL16TestReplicate

go

GRANT INSERT ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT UPDATE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT DELETE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

Activate Rep Agent

-- Connect to the primary database and to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RepL16TestPrimary

2> go

1> -- let RepAgent sends changes to the replicate database

2> sp_config_rep_agent RepL16TestPrimary, send_warm_standby_xacts, true

3> go

Parameter_Name Default_Value Config_Value Run_Value

----------------------- ------------- ------------ ---------

send warm standby xacts false true false

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> -- config connect dataserver to the ASE name

2> sp_config_rep_agent RepL16TestPrimary, 'connect dataserver', 'SERVERNAME'

3> go

Parameter_Name Default_Value Config_Value Run_Value

------------------ ------------- ------------ ------------

connect dataserver SERVERNAME SERVERNAME SERVERNAME 

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> sp_config_rep_agent RepL16TestPrimary, 'enable', 'RSFOG2', 'sa', 'password'

2> go

Msg 18382, Level 16, State 1:

Server 'SERVERNAME', Procedure 'sp_config_rep_agent', Line 664:

Database 'RepL16TestPrimary' is already configured to use Replication Agent. Request to enable Replication Agent has been ignored.

(return status = 1)

→ Bebause it already done in rs_init. No problem. 

1> -- stop RepAgent

2> sp_stop_rep_agent RepL16TestPrimary

3> go

The Replication Agent thread for database 'RepL16TestPrimary' is being stopped.

(return status = 0)

1> use RSFOG2_RSSD

2> go

1> rs_zeroltm 'SERVERNAME', 'RepL16TestPrimary'

2> go

Locater has been reset to zero.

(return status = 0)

1> use RepL16TestPrimary

2> go

1> dbcc settrunc(ltm, valid)

2> go

secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version

-------------------- --------------------- --------- ------------- ----------- ------------------------------ -----------

25757 1 175 0 14 RepL16TestPrimary 760

1> -- truncate the log

2> dump tran RepL16TestPrimary with no_log

3> go

1> -- grant permissions

2> grant role replication_role to sa

3> go

1>

2> -- start RepAgent

3> sp_start_rep_agent RepL16TestPrimary

4> go

Replication Agent thread is started for database 'RepL16TestPrimary'.

(return status = 0)

View information about all definitions and declarations

In the primary database:

sp_config_rep_agent RepL16TestPrimary

go

sp_help_rep_agent "RepL16TestPrimary"

go

sp_displaylogin sa

go

In the Replication Server:

admin who

go

admin who_is_down

go

In RSSD:

rs_helpdbrep DETestDB_repdef, win16, DETestDBPrimary

go

rs_helpcheckrepdef

go

rs_helprep DETestDB_repdef

go

rs_helpdbsub DETestDB_pub_subscript, ubuntu16, DETestDB

go

select * from rs_subscriptions A

go

select * from rs_dbreps B

go

select * from rs_sites --B

go

select * from rs_databases --C

go

Check the replication

Connect to the primary database and to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1. Insert data to the primary table.
2. Check if it was replicated in the replicate database.

1> use RepL16TestPrimary

2> go

1> INSERT INTO dbo.FirstTable ( ID, IntColumn, StrColumn ) VALUES ( 1, 1, 'Str 1' )

2> go

(1 row affected)

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

1> use RepL16TestReplicate

2> go

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

SAP (sybase) RS - Displays connection data for the Replication Server

In order to displays connection data for the Replication Server, 

connect SRS, and run:

admin show_connections

go

SAP (sybase) RS - some basic and useful scripts and queries

Select RS version

1. Login RS and run:

admin version

2. Without login:

cd $SYBASE/REP-16_0/bin/

repserver -v

A list of running / not running components

admin who

admin who_is_down

(Executed from RS)

Get the name and path of the RS log

admin log_name

(Executed from RS)

Information about databases and replication definitions

select * from rs_databases

select * from rs_subscriptions

select * from rs_publications

select * from rs_dbreps

exec rs_helpdb

exec rs_helprep

exec rs_helpsub

(Executed from RSSD databse)

SAP (sybase) RS: Permission error while using rs_init

Error message:

Permission denided. You must either be the database/object owner or process any of the following roles in order to perform this operation: sa_role, replication_role

failed to execute 'dbcc is_replication_enabled' as user RS1_RSSD_prim

Solution:

Grant required roles to the relevant users:

use RS1_RSSD

go

grant role sa_role to RS1_RSSD_prim

go

grant role replication_role to RS1_RSSD_prim

go

SAP (sybase) RS: RSI USER become Inactive

Problem:

Replications via the routing don't work and RSI  is inactive (checking with"admin who_is_down") 

1> admin who_is_down

2> go

Spid     Name   State    Info

------- -------   ------- -------

            RSI       Down   RS2

Solution:

Resume route:

1> resume route to RS2

2> go

Route to 'RS2' is resumed.

Check that nothing is down:

1> admin who_is_down

2> go

Spid     Name   State    Info

------- -------   ------- -------

1>

SAP RS - RSI USER become Inactive

Error:

RSI USER become Inactive .

Replications via the routing don't work and RSI  is inactive (checking with"admin who_is_down") 

1> admin who_is_down

2> go

Spid     Name    State    Info

-------   -------      -------    -------

            RSI        Down   RS2

Solution:

Resume route:

1> resume route to RS2

2> go

Route to 'RS2' is resumed.

Check:

1> admin who_is_down

2> go

Spid     Name    State    Info

-------   -------      -------    -------

1> 

SAP Replication Server Routing

How does it work?

In a "standard" replication - we have a replication definition and subscription that declare the replication between database A to database B (and C, D, ...).

In a routing replication there is one more step before the target replicated database - another RS.

The second RS should know when and what to replicate to it's target DB.

The route from The first RS to the second, and specific between their RSSDs - replicate the required definitions (rep. definition, etc.) from the first RSSD to the second one.

When the second RSSD has these definitions - we can create a subscription on the second RS from the source database to the target database. This is how it works.

Before declare a routing

1. Two replication servers are installed and started.
2. All target databases must have the same columns and Primary key.
3. No identity in the target table (or subscription that know to update identity column).

Importent notes:

1. The passwords to all RSs in the process should be the same.
2. The RepAgent has to use a known servers names (in the ini files).
3. Source and target tables must have a primary keys.

Routing definitions

Init servers

1. Edit the ini file in the new server with:
   
   1. Current (new) RS.
   2. ASE instance of the RSSD.
   3. ASE instance of the replicatec(=target) database.
   4. First (=source) RS.
2. Edit the ini of the first (=source) RS with:
   1. New RS.
   2. ASE instance of the source RSSD.
3. Run rs_init:
   1. Two ways to do that:
      1. run rs_init
      2. create <SAP_RS_FOLSER>/REP-16_0/init/rs/RS2.rs (copied and edited from install.rs) and run it
         1. ./rs_init -r ../init/rs/RS2.rs
   2. In rs_init we define the servers, databases, RSSDs, users, etc.
4. Check that RS is up:
   1. isql64 -S RSFOG2 -Usa -P<password>
   2. admin version
      go
   3. admin who
      go

Manage the route

#		RS1 (source RS)	RSSD of RS1 (RS1_RSSD)	RS2 (second RS)	Target replicated database
1	Create route	alter connection to SERVERNAME.RS1_RSSD set log transfer ON go create route to RS2 set username sa set password PASSWORD go
1.a	Monitor the new route until it became active		1> rs_helproute 2> go  route        route_status                                                                                                   ---------------------------------- RS1 --------> RS2 Route is being created. Destination has not yet finished creating system table subscriptions.  Systable Subs to be created …… (list of what created) * re-run to re-check untio all created.
2	add connection RS2 (second RS) → target database			Use rs_init Declare DATABASE INFORMATION 1. SQL Server name: (target ASE) 2. SA user: sa 3. SA password: ****** 4. Database name: (target database name) 5. Will the database be replicated: no 6. Maintenance user: <target database name>_maint 7. Maintenance password: ****** 8. Is this a Physical Connection for Existing Logical Connection: no Can be done also with "create connection" command.
3	Configure a  rep_agent to the RSSD		1> use RS1_RSSD 2> go 1> sp_config_rep_agent RS1_RSSD , 'enable' , 'RS1' , 'sa' ,'PASSWORD' 2> go Replication Agent enabled for database 'RS1_RSSD'. The Replication Agent thread needs to be started using sp_start_rep_agent. (return status = 0) 1> sp_start_rep_agent RS1_RSSD 2> go Replication Agent thread is started for database 'RS1_RSSD'. (return status = 0)
4	Grant permissions in the target database				1> grant all on FirstTable to  <target database name>_maint 2> go
5	create a subscription to the replicate connection in the second RS			1> create subscription subTest3 for repdefTest with primary at PRIMARYDBSERVERNAME.PRIMARYDB with replicate at TARGETDBSERVER.TARGETDBNAME without materialization 2> go Subscription 'subTest3' is in the process of being created. 1> check subscription subTest3 for repdefTest with primary at PRIMARYDBSERVERNAME.PRIMARYDB with replicate at TARGETDBSERVER.TARGETDBNAME 2> go

SAP RS - The Replication Process

Before declare a Replication

We should have:

• Databases installations (SPA ASE or others), primary and replicates.
• Instances were set in sql.ini.
• RSSD database.

The Replication Process

1. Add a table to Replication

Create connections to the primary and replicated server. – (using rs_init)

Create replication definition

Create (and validate) publication

Create (and validate) subscription

Add table to replication - sp_setreptable FirstTable, true (without true/false – just display the current status)

The replicated table must have a primary key!

• In both sides – primary and replicated
• It’s part of the replication definition, and the replication won’t work without the PK.

2. Replication Agent - Rep Agent

Transactions to be replicated are extracted from the source database transaction log.

The component that do that is the RepAgent.

REP Agent is a component that scans the transaction log of the source database and picks up the changes for replication.

3. From Rep Agent to Distributer

SQM – Stable Queue Manager

• Write the changes to the queue.

In Bound Queue

• Store changes until they will be distributed.
• There is one inbound queue per primary database.
• contains data rows and transactions that have begun at the primary database but have no yet been committed (SQT will pass to the next steps only committed transactions).
• Transactions will be stored in IBQ until a copy will be distributed to all subscriptions (out-bound queue).

SQT – Stable Queue Transaction

• Alert the Distributer that changes are available.
• Move from the IBQ to the next steps only committed transactions

Distributer

The Distributer read transaction and determine who is subscribing to it.

Thread forward the transaction to the next SQM “on the way” to the out-bound Queue.

4. Distribute to target databases

SQM

A SQM for the out-bound queue - Writes the transaction to the out bound queue if there are subscription.

Out-bound Queue

There is one outbound queue for each RDB.

DSI / RSI

DSI / RSI “translate” between the source and target servers.

• DSI – Data Server Interface – the target is database.
• RSI – Replication Server Interface – the target is replication server.