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.
  1. 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.
  2. 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.
  3. 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
  4. 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.

  1. Stop all user activity of the replicate database including the existing DSI connection:
    isql –Usa –P –SPRS2
    suspend connection to sunak1505.rep
    go
    Connection to 'sunak1505x.rep' is suspended.
  2. 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 -P –SPRS2
    suspend connection to sunak1505x.pri
    go
    Connection to 'sunak1505x.pri' is suspended.
  3. 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
  4. Dump the replicate database:
    dump database diffprim to
    '/c11014900/sybase1520x/rep.dmp'
    go
  5. Load the replicate database dump to the primary database:
    load database pri from
    '/c11014900/sybase1520x/rep.dmp'
    go
  6. Bring the primary database online:
    online database pri
    go
  7. 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
  8. 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)
  9. 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.
  10. Ensure that either the database or the tables on the updated primary database are marked for replication.
  11. 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
  12. 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.
  13. Truncate the rs_lastcommit table at the replicate database:
    use rep
    go
    truncate table rs_lastcommit
    go
  14. Purge queues of any existing work from primary to replicate:
    isql -Usa -P –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.
  15. Turn of the hibernation mode after the queues are purged:
    isql -Usa -P –SPRS2
    sysadmin hibernate_off
    go
    
  16. 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
  17. On the primary database, start the RepAgent:
    sp_start_rep_agent pri
    go
    Replication Agent thread is started for database
    'pri'.
    (return status = 0)
  18. 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
  19. Validate that replication is available and working.
  20. Allow users on the primary database.