sp_reptostandby

Description

Marks or unmarks database for replication to the standby database. Enables replication of supported schema changes and data changes to user tables.

Syntax

sp_reptostandby dbname [, 'L1' | 'all' | 'none' ]

Parameters

dbname: is the name of the active database.

L1: Sets the schema replication support level to Replication Server version 12.0 and Adaptive Server version 12.0. If you upgrade the database, the feature set will remain at the Adaptive Server version 12.0 level.

all: Sets the schema replication support level to that of the current Adaptive Server. If you upgrade the database, the feature set of the later release is enabled automatically.

none: 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.

Usage

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:
- create trigger
- create view
- drop default
- drop index
- drop procedure
- drop rule
- alter table
- create default
- create index
- create procedure
- create rule
- create table
- drop table
- drop trigger
- drop view
- grant
- revoke
The supported system procedures are:
- sp_addalias
- sp_addgroup
- sp_addmessage
- sp_adduser
- sp_addtype
- sp_bindefault
- sp_bindmsg
- sp_bindrule
- sp_changegroup
- sp_chgattribute
- sp_commonkey
- sp_config_rep_agent
- sp_dropalias
- sp_dropgroup
- sp_dropkey
- sp_dropmessage
- sp_droptype
- sp_dropuser
- sp_foreignkey
- sp_primarykey
- sp_procxmode
- sp_recompile
- sp_rename
- sp_setrepcol
- sp_setreplicate
- sp_setreptable
- sp_unbindefault
- sp_unbindmsg
- sp_unbindrule
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 cannot selectively turn off replication for individual database objects. 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 and 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 and image data columns may be treated as always_replicate or replicate_if_changed.
- If text and image columns marked by sp_setreptable are specified always_replicate (the default), all text and image columns are treated as always_replicate.
- If text and image columns are specified by sp_setrepcol as do_not_replicate or replicate_if_changed, all text and image columns are treated as replicate_if_changed.

Restrictions and requirements

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 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

Permissions

sp_reptostandby requires sa or dbo permission or replication_role.