Scenario 4

You can materialize a primary database from a replicate database where user activity can be stopped and the replicate database is a duplicate of the primary database.

Before you begin

Use this materialization method if a problem ocuurs to your primary database and the replicate database, which is the duplicate of your primary database, is old data. In the current primary database, validate the objects that have been marked for replication. If the database has text and image columns and these columns will be replicated, decide whether to mark the database for replication prior to the copy or after replication from the primary database has been established.

Check whether the database has been marked for replication:
```
use pri
go
sp_reptostandby pri
go
The replication status for database 'pri' is 'ALL'.
The replication mode for database 'pri' is 'off'.
(return status = 0)
```
Note: In this scenario, the database has been marked for database replication and has not been marked for SQL statement replication.
If the database has not been marked for replication, check to see whether the tables are marked for replication:
```
use pri
go
sp_setreplicate
go
Name       Type
------- ----------------
t1         user table
t2         user table
rs_marker  stored procedure
(3 rows affected)
(return status = 0)
```
Note: Any table or stored procedure beginning with "rs_" is created by Replication Server. Therefore, in this scenario, t1 and t2 are application tables, which were marked for replication and rs_marker is a stored procedure created by adding the database to the replication system.

Get the current generation number of the primary database.

use pri
go
dbcc gettrunc
go
secondary trunc page secondary trunc state dbrepstat
------------------- --------------------- -------
2669                           1            173
generation id database id database name ltl version
------------- ------------ ------------- ---------
0                7            pri           720

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

Maintenance Users
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

You need not stop user activity at the source database; because when the new database goes online, Adaptive Server creates a database that is transactionally consistent at the time of the dump.

Stop all user activity of the replicate database including the existing DSI connection:
```
isql –Usa –Psa_pass –SPRS2
suspend connection to sunak1505.rep
go
Connection to 'sunak1505x.rep' is suspended.
```

Stop all user activity of the primary database including the existing Replication Agent and DSI:

sp_stop_rep_agent pri
go
The Replication Agent thread for database 'pri' is
being stopped.
(return status = 0)
isql -Usa -Psa_pass –SPRS2
suspend connection to sunak1505x.pri
go
Connection to 'sunak1505x.pri' is suspended.

Validate that the Replication Server is not actively using either the primary or the replicate database connection:

admin who
go
Spid    Name     State                Info
---- ---------- ------------------    ------------
     DSI EXEC   Suspended             104(1)sunak1505x.pri
     DSI        Suspended             104 sunak1505x.pri
129  DIST       Awaiting Wakeup       104 sunak1505x.pri
130  SQT        Awaiting Wakeup       104:1 DIST sunak1505x.pri
123  SQM        Awaiting Message      104:1 sunak1505x.pri
56   SQM        Awaiting Message      104:0 sunak1505x.pri
     REP AGENT  Down                  sunak1505x.pri
     NRM        Down                  sunak1505x.pri
     DSI EXEC   Suspended             106(1) sunak1505x.rep
     DSI        Suspended             106 sunak1505x.rep
124  SQM       Awaiting Message       106:0 sunak1505x.rep

Dump the replicate database:

dump database diffprim to
'/c11014900/sybase1520x/rep.dmp'
go

Load the replicate database dump to the primary database:

load database pri from
'/c11014900/sybase1520x/rep.dmp'
go

Bring the primary database online:
```
online database pri
go
```
Add the maintenance user of the primary connection to the primary database and grant the appropriate privileges. If the maintenance user already exists on this server, synchronize the “suid” of the maintenance user and any other user that will be logging in to the primary database.
```
use pri
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
```

If the replicate database dump had a Replication Agent, remove the secondary truncation point and the existing RepAgent from the new primary database:

use pri
go
dbcc settrunc (ltm,ignore)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2667                     0                      166
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7               pri         720

Note: A secondary truncation state of 0 indicates that the secondary truncation point is inactive.

sp_config_rep_agent pri,'disable'
go
Replication Agent disabled for database 'pri'. The
secondary truncation point
in the database is no longer active.
(return status = 0)

Enable the RepAgent at the primary database:
```
sp_config_rep_agent pri,
'enable','PRS2','PRS2_ra','PRS2_ra_ps'
go
Replication Agent enabled for database 'pri'. The
Replication Agent thread
needs to be started using sp_start_rep_agent.
(return status = 0)
```
Note: Any Replication Server user with connect source privilege can be used. If the password of the Replication Server is unknown, it can be reset using the alter user Replication Server command.
Ensure that either the database or the tables on the updated primary database are marked for replication.

Modify the settings in the RSSD and the primary database to have the Replication Agent start at the end of the transaction log:

use PRS2_RSSD
go
rs_zeroltm sunak1505x,pri
go
Locater has been reset to zero.
(return status = 0)
isql –Usa –P –Ssunak1505x
use pri
go
dbcc settrunc (ltm,valid)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2670                         1               167
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7          pri            720

Increase the generation number by 1 in case the log pages of the new database are numerically less than the log pages of the previous copy of the database:

dbcc settrunc (ltm,gen_id,1)
go
secondary trunc page secondary trunc state dbrepstat
-------------------- --------------------- --------
2670                         1               167
generation id database id database name ltl version
------------- ------------ ------------- ----------
0                7          pri            720

Note: The generation number was obtained prior to beginning the resynchronization.

Truncate the rs_lastcommit table at the replicate database:
```
use rep
go
truncate table rs_lastcommit
go
```
Purge queues of any existing work from primary to replicate:
```
isql -Usa -Psa_pass –SPRS2
sysadmin hibernate_on
go
The Replication Server has now entered hibernation mode.
```
Note: The sysadmin sqm_purge_queue command requires the Replication Server to be in hibernate or standalone mode. When the Replication Server is in hibernate or standalone mode, no work is performed.
```
sysadmin sqm_purge_queue, 104,1
go
sysadmin sqm_purge_queue, 106,0
go
```
Note: The queue number used in the sysadmin sqm_purge_queue command, is the connection number for the connection, as shown in the admin who command. The queue type is either 1 for inbound or 0 for outbound and is available from the admin who command.
```
sysadmin hibernate_off
go
The Replication Server has now finished hibernation
mode.
```
Turn of the hibernation mode after the queues are purged:
```
isql -Usa -Psa_pass –SPRS2
sysadmin hibernate_off
go
```

Resume the connection to the primary and replicate database:

resume connection to sunak1505x.pri
go
Connection to 'sunak1505x.pri' is resumed.
resume connection to sunak1505x.rep
go
Connection to 'sunak1505x.rep' is resumed

On the primary database, start the RepAgent:

sp_start_rep_agent pri
go
Replication Agent thread is started for database
'pri'.
(return status = 0)

In the Replication Server, ensure that both the RepAgent and the DSI threads have been successfully started:

admin who
go
Spid Name     State             Info
---- -------- ------------     ------------------
139  DSI EXEC Awaiting Command 104(1) sunak1505x.pri
135  DSI      Awaiting Message 104 sunak1505x.pri
129  DIST     Awaiting Wakeup  104 sunak1505x.pri
130  SQT      Awaiting Wakeup  104:1 DIST sunak1505x.pri
123 SQM       Awaiting Message 104:1 sunak1505x.pri
56  SQM       Awaiting Message 104:0 sunak1505x.pri
143 REP AGENT Awaiting Command sunak1505x.pri
145 NRM       Awaiting Command sunak1505x.pri

Validate that replication is available and working.
Allow users on the primary database.