Scenario 3

You can materialize a replicate database from a primary database where user activity cannot be stopped and using MSA replication.

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

  • 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

  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