Scenario 7

It is assumed that activity cannot be stopped at the primary database while the database dump is in progress. This scenario uses the primary database to populate the replicate database and can be used to populate multiple replicate databases with the same primary database dump.

This scenario is very similar to Scenario 3 with these additions:

The SAP Adaptive Server Enterprise (SAP ASE) and SAP Replication Server must be at 15.5 or higher.
This solution does not require an MSA replication definition or subscription.
This solution suspends multiple DSI connections at the same time, so that the same primary database dump can be used to populate multiple replicate databases.

Before You Begin

At the RSSD, obtain the maintenance users for the connections:

use PRS2_RSSD
go
rs_helpuser
go
User Name      Permission(s) Name
------------ ------------------------------
PRS2_id_user    no grants
sa              sa
PRS2_ra         connect source
PRS2_rsi        connect source
PRS2_RSSD_prim  source, primary subscr

User name       Destination DS.DB
------------ -------------------------------------
PRS2_RSSD_maint  sunak1505x.PRS2_RSSD
pri_maint        sunak1505x.pri
pri_maint        sunak1505x.rep
(return status = 0)

In this example, “pri_maint” is the maintenance user for both the connection to the primary database, sunak1505x.pri. and replicate database, sunak1505x.rep.

Begin the Resynchronization

On the primary SAP ASE, stop the Replication Agent on the primary database:

isql -Usa -P -Ssunak1505x
sp_stop_rep_agent pri
go
The Replication Agent thread for database 'pri' is
being stopped.
(return status = 0)

On the replicate SAP Replication Server, suspend the DSI to the replicate database:

isql -Usa -Psa_pass –SPRS2
admin who
go
Spid Name     State             Info
---- ---- ----------------     -------------------
68   DSI EXEC Suspended        105(1) sunak1505x.rep
67   DSI      Suspended        105 sunak1505x.rep
66   SQM      Awaiting Message 105:0 sunak1505x.rep

Resume the connection to the replicate database and instruct it to wait for the resync marker:
```
resume connection to sunak1505x.rep
skip to resync marker
go
Connection to 'sunak1505x.rep' is resumed.
```

Validate that the DSI thread for the replicate database connection is properly set:

admin who
go
Spid  Name     State                    Info
----  ----     ---------------- -------------------
120   DSI EXEC Awaiting Command 105(1) sunak1505x.rep
119   DSI      SkipUntil Resync 105    sunak1505x.rep
66    SQM      Awaiting Message 105:0  sunak1505x.rep

On the primary SAP ASE, start the Replication Agent with the resync option:
```
sp_start_rep_agent pri,'resync'
go
Replication Agent thread is started for database 'pri'.
```
Note: If you changed the location of the secondary truncation point, see the Reference Manual for other options on sp_start_rep_agent that are available.
On the SAP Replication Server, validate that the DSI thread status has now changed:
```
admin who
go
Spid   Name     State            Info
----   ----     ---------------- -------------------
120    DSI EXEC Awaiting Command 105(1) sunak1505x.rep
119    DSI      SkipUntil Dump   105 sunak1505x.rep
66     SQM      Awaiting Message 105:0 sunak1505x.rep
```
In the SAP Replication Server log, you see that the resync option has been acknowledged:
```
DSI for sunak1505x.rep received and processed Resync Database Marker. Waiting for Dump Marker.
```
Note: If the DSI thread does not change to “SkipUntilDump”, restart the process from step 1.
Dump the primary database:
```
dump database pri to 'pri.dmp'
go
```

On the SAP Replication Server, validate that the DSI thread is now suspended:

admin who
go
Spid Name     State            Info
---- ---- ----------------     -------------------
68   DSI EXEC Suspended        105(1) sunak1505x.rep
67   DSI      Suspended        105 sunak1505x.rep
66   SQM      Awaiting Message 105:0 sunak1505x.rep

In the SAP Replication Server log, you see that the dump marker has been processed:

DSI for ‘sunak1505x.rep’ received and processed Dump Marker. DSI is now suspended. Resume after database has been reloaded.
The DSI thread for database ‘sunak1505x.rep’ is shutdown.

Load the replicate database:

load database rep from '/c11014900/sybase1520x/ASE-
15_0/bin/pri.dmp'
go

Bring the replicate database online:
```
online database rep
go
```
Add the maintenance user of the replicate connection to the replicate database and grant the appropriate privileges before the resynchronization.
```
use rep
go
sp_adduser pri_maint
go
New user added.
(return status = 0)
grant execute on rs_get_lastcommit to pri_maint
go
grant all on rs_lastcommit to pri_maint
go
```
Note: If the maintenance user is new to this server, add the maintenance user login to this server, and grant the replication role:
```
sp_addlogin <maintenance user>, <maintenance userpassword>, <database>
go
sp_role "grant", replication_role, <maintenance user>
go
```
If the password to the maintenance user is unknown, set a new password in Adaptive Server. In the Replication Server, change the password of the maintenance user to match with what was assigned in the Adaptive Server:
```
alter connection to <dataserver>.<database>
set password to <new_password>
go
```
Truncate the rs_lastcommit table at the replicate database:
```
use rep
go
truncate table rs_lastcommit
go
```

Release the secondary truncation point from the replicate database:

dbcc settrunc (ltm,ignore)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- -----
2668                      0                  172
generation id database id database name ltl version
------------- ------------ ------------- -------
0                 8              rep        720

Resume connection to the replicate database. If the DSI stays suspended, look at the Replication Server logs for any errors encountered while resuming the DSI:
```
resume connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is resumed
```