Restrictions and requirements when using sp_reptostandby

Consider the following issues when you set up your warm standby application and enable replication with sp_reptostandby.

• alter encryption key

• alter table

• alter key

• create default

• create encryption key

• create function

• create index

• create key

• create plan*

• create procedure

• create rule

• create schema*

• create table

• create trigger

• create view

• drop default

• drop encryption key

• drop function

• drop index

• drop procedure

• drop rule

• drop table

• drop trigger

• drop view

• grant

• installjava*

• remove java*

• revoke

• sp_addalias

• sp_addgroup

• sp_addmessage

• sp_add_qpgroup*

• sp_adduser

• sp_addtype

• sp_bindefault

• sp_bindmsg

• sp_bindrule

• sp_cachestrategy

• sp_changegroup

• sp_chgattribute

• sp_commonkey

• sp_config_rep_agentsp_dropalias

• sp_drop_all_qplans*

• sp_dropgroup

• sp_dropkey

• sp_dropmessage

• sp_drop_qpgroup*

• sp_droptype

• sp_dropuser

• sp_encryption

• sp_export_qpgroup*

• sp_foreignkey

• sp_import_qpgroup*

• sp_primarykeysp_procxmode

• sp_recompile

• sp_rename

• sp_rename_qpgroup*

• sp_setrepcol

• sp_setrepdefmode

• sp_setrepproc

• sp_setreptable

• sp_unbindefault

• sp_unbindmsg

• sp_unbindrule

The set of DDL commands and system procedures that are supported for replication in the master database is different than the set supported from replication in a user database.

In the master database, the supported DDL commands and system procedures are:

• alter role

• create role

• drop role

• grant role

• revoke role

• sp_addlogin

• sp_defaultdb

• sp_defaultlanguage

• sp_displaylevel

• sp_droplogin

• sp_locklogin

• sp_modifylogin

• sp_password

• sp_passwordpolicy

  sp_passwordpolicy is replicated for all options except allow password downgrade

• sp_role

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.

If a DDL command or system procedure contains password information, the password information is sent through the replication environment using the ciphertext password value stored in the source Adaptive Server system tables.

To enable replication of DML and DDL commands, execute sp_reptostandby in the Adaptive Server that manages the active database. The syntax is:

sp_reptostandby dbname, [[, 'L1' | 'ALL' | 'NONE' ] [, use_index]]

where dbname is the name of the active database and the keywords L1, all, and none set the level of replication support.

L1 represents the level of replication supported by Adaptive Server version 12.5.

Use the all keyword to make sure that schema replication support is always at the highest level available. For example, to set the schema replication support level to that of the latest Adaptive Server version, log in to Adaptive Server and execute this command at the isql prompt:

sp_reptostandby dbname, 'all'

Then, if the database is upgraded to a later Adaptive Server version with a higher level of replication support, all new features of that version are enabled automatically. Refer to Chapter 5, “Adaptive Server Commands and System Procedures,” in the Replication Server Reference Manual for more information about sp_reptostandby command.

When Adaptive Server performs an alter table ... add column_name default ... statement, the server creates a constraint for the default value using the objid. After Replication Server replicates this statement, the standby Adaptive Server creates the same constraint but with a different objid.

If the constraint is later dropped at the primary using alter table ... drop constraint ... , the statement cannot be performed at the warm standby because the objid is not the same.

To drop the constraint at both the primary and standby databases, use either of these two methods:

• Execute this statement at the primary:

  alter table table_name
  		...
  		replace column_name default null
  
  

• Execute this statement at the primary:

  alter table table_name
  		...
  		drop constraint constraint_name
  
  

  This statement causes the DSI to shut down. Execute the same command at the standby database with its corresponding objid, and then resume the connection to the DSI, skipping a transaction.

The user tables and user stored procedures are not replicated if the database used is the master database.

If the master database is replicated, the following system procedures must be executed in the master database:

• sp_addlogin

• sp_defaultdb

• sp_defaultlanguage

• sp_displaylevel

• sp_droplogin

• sp_locklogin

• sp_modifylogin

Both the source and target Adaptive Servers must support the master database replication feature if the database used is the master database.

If the database is the master database, both the source ASE server and the target ASE server must be the same hardware architecture type (32-bit versions and 64-bit versions are compatible) and the same operating system (different versions are also compatible).