sp_reptostandby

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'] [, use_index]

Parameters

dbname – The name of the active database.
L1 – 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.
all – 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.
none – Unmarks all database tables for replication and turns off data and schema replication to the standby database.
Note: 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.
use_index – Marks the database to use an index for replication on text, unitext, image, or rawobjects columns.

Examples

Example 1 – 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'
```

Example 2 – Displays the SQL statement replication status at the database level:

1> sp_reptostandby pubs2
2> go

The replication status for database 'pubs2' is 'ALL'.
The replication mode for database 'pubs2' is ' udis'.
(return status = 0)

Usage

Use sp_reptostandby with Adaptive Server version 11.5 or later databases. You must also 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.
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 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.
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.

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 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, these 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.

Permissions

sp_reptostandby requires “sa” or “dbo” permission or replication_role.