sp_reptostandby

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, and internal indexes are created on those tables not explicitly marked for replication.
The use_index option only takes effect in tables with LOB columns that you created with versions of Adaptive Server earlier than 15.7 SP100. With Adaptive Server 15.7 SP100, use_index is deprecated because the information RepAgent needs to replicate LOB columns is already available in the form of backlinking pointers and therefore RepAgent ignores use_index when you upgrade the database to version 15.7 SP100 and later.

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

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.
Replication of the Transact-SQL writetext command requires access to the data row pointing to the text page where the database stores the LOB data. To allow access to this data row, Adaptive Server uses either a backlink pointer in the first text page or indexes created for replication. The process of creating indexes at the column, table or database level requires an intensive operation to provide the information to support replication.

With an Adaptive Server version 15.7 SP100 and later database that you did not upgrade from an earlier version, sp_reptostandby takes effect immediately because by default, Adaptive Server creates and maintains LOB backlinking pointers to the database. Therefore, setting up replication for a table does not require the creation of indexes. Adaptive Server ignores the use_index parameter if the information needed to replicate LOB columns is already available in the form of backlinkingpointers.

However, if you have upgraded from, or are using a database that you created with a version of Adaptive Server earlier than 15.7 SP100, setting up replication may take a longer time due to the creation of indexes. To reduce processing time, run dbcc shrinkdb_setup at the relevant level—column, table, or database, to create backlinking pointers and to ensure the backlinking status is up to date.

dbcc shrinkdb_setup marks as suspect, replication indexes of columns, tables, or databases that you previously marked with use_index. You can use dbcc reindex to drop indexes for these objects because these indexes are not needed after the execution of dbcc shrinkdb_setup.
With Adaptive Server 15.7 SP100, use_index is deprecated. With versions earlier than Adaptive Server 15.7 SP100, if you use 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.