Replicating DDL and System Procedures

Learn how to use MSA to replicate DDL to nonstandby databases.

See Replication Server Administration Guide Volume 2 > Managing Warm Standby Applications > What Information is Replicated for ASE Warm Standby Application? > Using sp_reptostandby to Enable Replication > Supported DDL Commands and System Procedures for a list of DDL commands supported for replication.

These constraints apply:

When replicating system procedures, and the primary and replicate databases have different names – filter out the sp_config_rep_agent and the sp_add_user system procedures in the database replication definition as they use database names as parameters. For example:
```
create database replication definition myrepdef
  with primary at PDS.pdb
  not replicate system procedures in
  (sp_config_rep_agent, sp_add_user) 
```

When replicating DDL – the primary and replicate databases must have the same login names and passwords; the DSI uses the original server login name and password to log in to the replicate database.
When replicating DDL contained in user-defined transactions – make sure that the Adaptive Server database option ddl in tran is set to true. Otherwise, the DSI will shut down when replicating DDL.
Replication Server does not support the replication of DDL commands after set proxy is executed on the primary Adaptive Server. If set proxy is executed on the primary Adaptive Server, Replication Server returns error 5517:
```
A REQUEST transaction to database '...' failed because the transaction    owner's password is missing. This prevents the preservation of transaction ownership.
```

Note: In a heterogeneous environment, non-Sybase data servers can replicate DDL if the Replication Agent can capture and send DDL commands in Transact-SQL or ANSI SQL (preferred).

To replicate DDL and system procedures:

Mark the primary database using sp_reptostandby.
Set the RepAgent parameter send warm standby xacts to true—even if there is no standby database.
Create a database subscription.
Make sure that both the primary and replicate data servers are the same version of Adaptive Server.