Marking the Primary Database for Replication

Use database replication definition and subcription to replicate the entire primary database.

  1. Log in to the primary database with system administrator privileges:
    % isql -Usa -P -Ssunak1502i
  2. Connect to the pubs2 database:
    use pubs2
    go
  3. Mark the primary database for replication. For example:
    sp_reptostandby pubs2, β€˜all’
  4. Set the RepAgent parameter send warm standby xacts to true so that RepAgent sends Data Manipulation Transactions (DML) and Data Definition Language (DDL) to the replicate database. For example, at the primary data server, enter:
    sp_config_rep_agent
    pubs2,send_warm_standby_xacts,true
    go
    
    Parameter_Name Default_Value Config_Value Run_Value
    ------------- --------- ----------------- ---------
    send warm standby xacts false  true 	      true
    (1 row affected)
    
    RepAgent configuration changed for database pubs2.
    The changes will take effect the next time the
    RepAgent thread is started. (return status = 0)
  5. Stop and restart the RepAgent:
    sp_stop_rep_agent pubs2
    go
    
    sp_start_rep_agent pubs2
    go
  6. Exit the isql session.
  7. At the Replication Server, create a database replication definition that also replicates DDL:
    create database replication definition name
    with primary at pds.pdb
    replicate DDL
    where:
    • pds – is the name of the primary ASE data server.
    • pdb – is the name of the primary database.
    For example:
    create database replication definition pubs2_repdef
    with primary at sunak1502i.pubs2
    replicate DDL
    go
    
    Database replication definition pubs2_repdef for
    sunak1502i.pubs2 is created.
  8. Create a database subscription for the replicate database. This example creates a database subscription that uses no materialization method and will replicate the truncate table command:
    create subscription pubs2_sub
    for database replication definition pubs2_repdef
    with primary at sunak1502i.pubs2
    with replicate at sunak1505i.pubs2
    without materialization
    subscribe to truncate table
    go
    Note: For the subscription to be successfully created or dropped, the connection to the replicate database must be available.
  9. Check the subscription status at the primary and replicate data servers:
    check subscription pubs2_sub
    for database replication definition pubs2_repdef
    with primary at sunak1502i.pubs2
    with replicate at sunak1505i.pubs2
    go
    The status shows:
    Subscription pubs2_sub is VALID at the replicate.
    Subscription pubs2_sub is VALID at the primary.
    The database is now ready for replication
    Note: If the subscription results in errors, you can drop the subscription using the drop subscription command. For example:
    drop subscription pubs2_sub
    for database replication definition pubs2_repdef
    with primary at sunak1502i.pubs2
    with replicate at sunak1505i.pubs2
    without purge
    The replicate connection must be available before you can drop a subscription.