Marks or unmarks database for replication to the standby database. Enables replication of supported schema changes and data changes to user tables.
sp_reptostandby dbname [, 'L1' | 'all' | 'none'] [, use_index]
The name of the active database.
Sets the schema replication feature set support level to the support level first introduced in Adaptive Server version 12.0. If you upgrade the Adaptive Server to a later version that implements a higher support level (that is, L2, L3, and so on) the support level will remain at the Adaptive Server version 12.0 support level. To date, only support level L1 has been implemented in Adaptive Server version 12.0 and later.
Sets the schema replication feature set support level to the highest support level implemented by the current Adaptive Server. If you upgrade the Adaptive Server to a later version, the highest support level implemented by the later version is enabled automatically.
Unmarks all database tables for replication and turns off data and schema replication to the standby database.
If you turn replication off using sp_reptostandby with
the none keyword, Adaptive Server locks all user
tables in exclusive mode and writes log records for all tables that
are unmarked for replication. This can be time-consuming if there
are many user tables in the database.
Marks the database to use an index for replication on text, unitext, image, or rawobjects columns.
Sets the replication status for pubs2 to all and creates a global index on the text and image pointers:
sp_reptostandby pubs2,'all','use_index'
Use sp_reptostandby with Adaptive Server version 11.5 or later databases. You also must enable RepAgent at the active and standby databases.
Copies data manipulation language (DML) commands, supported data definition language (DDL) commands, and supported system procedures to the standby database.
The supported DDL commands are:
alter encryption key
alter key
alter table
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
The supported system procedures are:
sp_addalias
sp_addgroup
sp_addmessage
sp_add_qpgroup
sp_addtype
sp_adduser
sp_bindefault
sp_bindmsg
sp_bindrule
sp_cachestrategy
sp_changegroup
sp_chgattribute
sp_commonkey
sp_config_rep_agent
sp_drop_all_qplans
sp_drop_qpgroup
sp_dropalias
sp_dropgroup
sp_dropkey
sp_dropmessage
sp_droptype
sp_dropuser
sp_encryption
sp_foreignkey
sp_import_qpgroup
sp_primarykey
sp_procxmode
sp_recompile
sp_rename
sp_rename_qpgroup
sp_setrepcol
sp_setrepdefmode
sp_setreplicate
sp_setreptable
sp_unbindefault
sp_unbindmsg
sp_unbindrule
If the database is the master database, the DDL commands and system procedures that are supported for replication in a user database are not supported for replication in the master database.
If the database is the master database, the supported DDL commands are:
alter role
create role
drop role
grant role
revoke role
If the database is the master database, the supported system procedures are:
sp_addlogin
sp_defaultdb
sp_defualtlanguage
sp_displaylevel
sp_droplogin
sp_locklogin
sp_modifylogin
sp_password
sp_passwordpolicy
sp_passwordpolicy is replicated
for all options except for allow password downgrade.
sp_role
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 source ASE system tables.
sp_reptostandby marks the database for replication to the warm standby database. It does not enable replication to replicate databases.
After sp_reptostandby has been executed and the warm standby enabled, you can selectively turn off replication for individual database tables by setting their replication status to never. You can use the set replication command to control replication of DDL and DML commands and procedures for the isql session. See set replication for more information.
By default, sp_reptostandby marks text, unitext, or image data as replicate_if_changed. You cannot change the status to always_replicate or do_not_replicate.
If the warm standby application includes normal replication, text, unitext, or image data columns may be treated as always_replicate or replicate_if_changed.
If text, unitext, or image columns marked by sp_setreptable are specified always_replicate (the default), all text, unitext, or image columns are treated as always_replicate.
If text, unitext, or image columns are specified by sp_setrepcol as do_not_replicate or replicate_if_changed, all text, unitext, or image columns are treated as replicate_if_changed.
When the database contains one or more large tables holding text, unitext, image, or rawobject columns, the internal process performed by sp_reptostandby may take a long time. To speed up the process, you can use use_index which creates a global nonclustered index for every text, unitext, image, or rawobject column of tables not explicitly marked for replication.
With use_index, a shared-table lock is held while the nonclustered index is created.
When you run sp_reptostandby with the none option, and the database is initially marked to use indexes for replication, all those indexes created for replication are dropped.
The standby database must be of the same or later release level than the active database. Both databases must have the same disk allocations, segment names, and roles. Refer to the Adaptive Server Enterprise System Administration Guide for details.
Login information is not replicated to the standby database.
Replication of commands or procedures containing the name of another database will fail if the named database does not exist in the standby server.
Supported DDL commands, such as create table, may not contain local variables.
Some commands that are not copied to the standby database:
select into and update statistics
Database or configuration options such as sp_dboption and sp_configure
If the database is the master database:
User tables and user stored procedures are not replicated.
The target database cannot be materialized with dump or load. Use other methodologies, such as bcp, where the data can be manipulated to resolve inconsistencies.
Both the source ASE server and target ASE server must support the master database replication feature.
Both the source ASE server and the target ASE server must have the same hardware architecture type (32-bit versions and 64-bit versions are compatible) and the same operating system (different versions are compatible).
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
You cannot use drop index to manually drop indexes created for text, unitext, image, or rawobject replication. You can use only the supported replication stored procedures sp_reptostandby, sp_setreptable, and sp_setrepcol to change the replication index status.
sp_reptostandby requires “sa” or “dbo” permission or replication_role.
set replication, sp_setrepcol, sp_setreptable,sp_setreplicate, sp_setrepproc
