Scenario 3

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 uses database replication definition and subscription.

If the primary database is replicating to multiple replicate databases, the complete process including defining the subscription, dumping the primary database, and loading the replicate database must be completed for each replicate database, defining the subscription for the next replicate database.

Before you begin

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

1. If the database subscription exists, drop the subscription:

   isql -Usa -P -Ssunak1505x
   use PRS2_RSSD
   go
   rs_helpdbsub
   go
   DBSub.Name ReplicateDS.DB ReplicateRS Status at RRS DBRep.Def.Name
   ---------- -------------- ----------- -----------  -----------
   rep_db_sub sunak1505x       PRS2        Valid           pri_db_rep
   
   PrimaryDS.DB PrimaryRS Status at PRS Method Trunc.Table Creation Date
   ------------ --------- -----------  ---------- -------------
   sunak1505x      PRS2    Valid Use  Dump Marker  Yes Dec  9 2009 3:38PM

   Note: The connection to the replicate database must be “Awaiting Command” prior to dropping the subscription.

   isql –Usa –P –SPRS2
   drop subscription rep_db_sub
   for database replication definition pri_db_rep
   with primary at sunak1505x.pri
   with replicate at sunak1505x.rep
   without purge
   go
   Subscription 'rep_db_sub' is in the process of being
   dropped.

2. Validate that the primary database is marked, enter:

   isql -Usa -P -Ssunak1505x
   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 “pri” is marked to replicate both DML and DDL and is not marked for SQL statement replication.
3. Validate that the RepAgent on the primary database is running and the connection to the replicate database exists and is not suspended:

   isql -Usa -P –SPRS2
   admin who
   go
   Spid Name       State              Info
   ---- ---------- --------------    ------------------
   62   DSI EXEC    Awaiting Command 104(1) sunak1505x.pri
   57   DSI         Awaiting Message 104 sunak1505x.pri
   59   DIST        Awaiting Wakeup  104 sunak1505x.pri
   60   SQT         Awaiting Wakeup  104:1 DIST sunak1505x.pri
   58   SQM         Awaiting Message 104:1 sunak1505x.pri
   56   SQM         Awaiting Message 104:0 sunak1505x.pri
   61   REP AGENT   Awaiting Command       sunak1505x.pri
   63   NRM         Awaiting Command       sunak1505x.pri
   68   DSI EXEC    Awaiting Command 105(1) sunak1505x.rep
   67   DSI         Awaiting Message 105 sunak1505x.rep
   66   SQM         Awaiting Message 105:0 sunak1505x.rep

4. Define the subscription with the option that we are going to use a dump to syncronize the data:

   define subscription rep_db_sub
   for database replication definition pri_db_rep
   with primary at sunak1505x.pri
   with replicate at sunak1505x.rep
   subscribe to truncate table
   use dump marker
   go
   Subscription 'rep_db_sub' is in the process of being
   defined.

   The connection to the replicate database is still not suspended.

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

   The above admin who command displays only the connections that are affected and not the complete list of connections.
5. Dump the primary database:

   dump database pri to 'pri.dmp'
   go

   The connection to the replicate database 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

6. Load the replicate database:

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

7. Bring the replicate database online:

   online database rep
   go

8. 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

9. Truncate the rs_lastcommit table at the replicate database:

   use rep
   go
   truncate table rs_lastcommit
   go

10. 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

11. 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