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'
   go

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:

   isql -Usa -Psa_pass -SPRS 
   create database replication definition name
   with primary at pds.pdb
   replicate DDL

   where:

   • name – is the unique identifer for this replication definition.
   • 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 sub_name
   for database replication definition name
   with primary at pds.pdb
   with replicate at rds.pdb
   without materialization
   subscribe to truncate table
   

   where:

   • sub_name – is the unique identifer for this subscription.
   • name – is the unique identifer for the replication definition.
   • pds – is the name of the primary ASE data server.
   • pdb – is the name of the primary database.
   • rds – is the name of the replicate ASE data server.

   for example:

   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
   go

   The replicate connection must be available before you can drop a subscription.