I am being asked to create database replication definitions for a couple of databases (around 20 databases). I just wanted to clarify is the below-mentioned steps are the correct steps to be carried out? Let's says 'customer' is one of my database.

  1. Set ddl in tran to true sp_dboption customer,"ddl in tran", true
  2. Mark the primary database using sp_reptostandby sp_reptostandby customer,'all'
  3. Set the RepAgent parameter send warm standby xacts to true sp_config_rep_agent customer,'send warm standby xacts', 'true'
  4. Create db repdef/subs create database replication definition db_repdef_customer with primary at DS.customer replicate DDL replicate system procedures go
    create subscription db_sub_customer for database replication definition db_repdef_customer with primary at DS.customer with replicate at DS2.customer1 without materialization go

(Note: DS.customer and DS2.customer1 are ASE-ASE replication)

After I have followed the above steps to create db repdef/subs, I hit a lot of permission issues on my replication ID to do INSERT/UPDATE/DELETE operators on those tables I did not setup table replication yet. Further check on these tables in my 'customer' database (ex. I tried to do insert/update/delete operations manually on tables without setting table repdef, I realised that the data replication is working for all the tables under the 'customer' database with/without I setup table replication. Is this normal? Any steps I have missed out? Please help.


step 1 not needed and not normally recommended as it can lead to contention issues on system tables

setp 3 is needed if you want to replicate DDL and system procs (which coincides with what you've got in step 4 - replicate DDL/sysprocs)

create sub should be step 5

check sub should be step 6

step 7 would be sending a rs_ticket from the PDB and making sure it arrives in RDB's rs_ticket_history table

make sure your maintuser (in RDB) is aliased to dbo to get around permissions issues

  • What about Step2? Shall I mark my primary database using sp_reptostandby <primary db>, 'all'? What do you mean by check sub in Step6? For Step7, would you please advise how could I send the rs_ticket from PDB?
    – Tittus
    Mar 14 '18 at 6:17
  • you can mark the primary db or you can mark each table, it's your call; check subscription command is used to verify subscription is actually created; see documentation for the rs_ticket stored proc (installed in PDB when you create your PDB connection), eg, rs_ticket 'test' should get replicated into RDB's rs_ticket_history table if everything setup correctly
    – markp-fuso
    Mar 14 '18 at 13:02
  • But if I mark for each table, the DDL commands will not get replicated to my replicate database even there are database replication definitions and subs are created on my primary database replication definition.
    – Tittus
    Mar 15 '18 at 3:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.