Replicating DDL

Replicate DDL.

1. Log in to the primary database using a user ID that exists on both the primary and replicate data server and that has permission to create a table on both the primary and replicate databases.
   Note: Do not use the same maintenance user ID that you used to set up the primary connection.
2. Create a new table:

   % isql -Usa -P -Ssunak1502i
   use pubs2
   go
   create table t1 (a char(10), b integer, c text)
   go

3. Create unique indexes to ensure data integrity:

   create unique clustered index t1_idx1 on t1 (a,b)
   go

4. Log in to the replicate database:

   % isql -Usa -P -Ssunak1505i
   use pubs2
   go

5. Verify that the table and index exists in the replicate database. If the table and index do not exist, follow the instructions in steps 6 and 7 otherwise, go to step 8.
6. Check the Replication Server log file at: $SYBASE/REP- 15_5/install/PRS.log. Correct the errors and restart the connection to the replicate database:

   resume connection to rds.rdb
   go

   where:

   • rds – is the name of the data server that hosts the replicate database.
   • rdb – is the name of the replicate database.

   Note: Make sure that the user making the corrections is not same the maintenance user that was used to set up the primary connection.
7. If you want Replication Server to skip any current transactions when trying to resume connection to the replicate database, use:

   resume connection to rds.rdb
   skip transaction
   go

   See the Replication Server Reference Manual > Replication Server Commands > resume connection for other available resume connection options.

   Note: If you see this message:

   “Message from server: Message: 2762, State 3, Severity 16 - 'The
   'CREATE TABLE' command is not allowed within a multi-statement
   transaction in the 'pubs2 database.”
   

   Ensure that the Replication Agent has been configured to send warm standby transactions and that the RepAgent has been stopped and restarted since the last time when the RepAgent configuration parameter was changed.
8. Grant insert, update, and delete permission for the new table to the replicate database maintenance user at the replicate database:

   grant all on t1 to pubs2_maint
   go