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.