Using Replicate Databases as Warm Standby Databases

Use MSA to replicate DDL and other database objects to multiple replicate or warm standby databases.

You can create database replication definitions and database subscriptions to logical connections. See Replication Server Administration Guide Volume 2 > Manage Warm Standby Applications for detailed information about setting up logical connections.

In this example of a basic setup for a multiple warm standby architecture, you replicate from one primary database— dsA.db, to two replicate databases—dsB.db and dsC.db. There is a single Replication Server controlling replication, and only standby replication takes place to and from the primary database. Only dsA can replicate DDL and system stored procedures. If users are switched to dsB.db or dsC.db, DDL and system stored procedures are not replicated.

You can easily add syntax to this example to replicate DDL or system stored procedures.

Note: This example uses a different database replication definition for each subscribing site. You could also create a single database replication definition that handles the common set of replicated tables and functions, and then create table and function subscriptions for the tables and functions that are not common to both standby databases.

Suspend all database activities.
Mark dsA.db, dsB.db, and dsC.db for replication using sp_reptostandby.
At each data server, set send warm standby xacts to true for each RepAgent. For example:
```
sp_config_rep_agent dbname,
    ‘send warm standby xacts’, ‘true’
```
At Replication Server, set dsi_replication off for each connection. For example:
```
alter connection to dsB.db
		set dsi_replication ‘off’
```
Note: Sybase recommends that you set dsi_replication to off for warm standby connections as it prevents replicated data in the transaction log from being replicated again in the event of a switchover. dsi_replication should be turned on (the default) for normal replication.

Create a database replication definition for each database, defining each as the primary. For example

create database replication definition rep_2
  with primary at dsA.db
  replicate DDL

  replicate system procedures

create database replication definition rep_2
  with primary at dsB.db

create database replication definition rep_2
  with primary at dsC.db

As each database can be a primary or a standby database, create or define subscriptions so that each database subscribes to every other database. You can use different materialization methods for each subscription. For example:

create subscription sub_2B
  for database replication definition rep_2 
    with primary at dsB.db
  with replicate at dsA.db
  without materialization
  subscribe to truncate table

create subscription sub_2C
  for database replication definition rep_2 
    with primary at dsC.db
  with replicate at dsA.db
  without materialization
  subscribe to truncate table

define subscription sub_2A
  for database replication definition rep_2 
    with primary at dsA.db
  with replicate at dsB.db
  subscribe to truncate table
  use dump marker

create subscription sub_2C
  for database replication definition rep_2 
    with primary at dsC.db
  with replicate at dsB.db
  without materialization
  subscribe to truncate table

define subscription sub_2A
  for database replication definition rep_2 
    with primary at dsA.db
  with replicate at dsC.db
  subscribe to truncate table
  use dump marker

create subscription sub_2B
  for database replication definition rep_2 
    with primary at dsB.db
  with replicate at dsC.db
  without materialization
  subscribe to truncate table

Dump dsA.db.
With the dsB.db DSI suspended, load database to dsB.db.
Resume connection to dsB.db.
With the dsC.db DSI suspended, load database to dsC.db.
Resume connection to dsC.db.
Resume database activities.