Replication Server Support for In-Memory Databases

Learn about how Replication Server works with in-memory databases.

Replication Server version 15.2 supports as the replicate database only:

In-memory databases
Relaxed-durability databases set with durability at no_recovery

Note: Replicating to an in-memory database may not be faster than replicating to a relaxed-durability database. DML on in-memory databases depends on several factors; see the In-Memory Databases Users Guide for more information.

The primary database must be a traditional, full-durability, disk-resident database. For convenience, this document refers to relaxed-durability databases with durability set to no_recovery as "relaxed-durability databases."

Note: Replication performance with an in-memory or relaxed-durability database acting as the replicate database is commensurate with replication between traditional full-durability disk-resident databases acting as both the primary and replicate database.

You can initialize either an in-memory database, or relaxed-durability database as a new replicate database by obtaining data, object schema, and configuration information from one of:

A template database that retains basic information.
A database dump from another database. Load the dump to the target in-memory database or relaxed-durability database.
The dump source database can be another in-memory database, relaxed-durability database, or a traditional disk-resident database.

In-memory and relaxed-durability databases lose their object definition, data, and RepAgent configuration once the host data server shuts down or restarts. You must reinitialize the database from the template or database dump from a source database.

Using a template database to initialize in-memory and relaxed-durability databases

You can use the same disk-resident database as a template for multiple in-memory or relaxed-durability databases.

To set up replication using a template database:

Create the template database – the template database uses the name of the database with the outbound connection to Replication Server, which is usually the replicate database name.
```
create database tokyo_db on publicdev=10 log on publicdevlog=10
go
```
On Replication Server, use rs_init to configure the replicate databases.
Suspend the DSI thread to the template database by stopping RepAgent on the template database. For example:
```
suspend connection to TOKYO_DS.ttokyo_db
```

Rename the template database to template1:

use master
go
sp_dboption tokyo_db, single, true
go
sp_renamedb tokyo_db, template1
go
sp_dboption template1, single, false
go

Create the in-memory or relaxed-durability database with durability set to no_recovery using the template created in step 1:

create inmemory database tokyo_db
use template1 as template
on imdb_cache_dev = '50' log on
imdb_cache_dev_log='50'
with DURABILITY=NO_RECOVERY
go

Connect to Replication Server and resume the connection to the replicate database:
```
resume connection to TOKYO_DS.tokyo_db
```

After a shutdown or termination and subsequent restart of Adaptive Server, Adaptive Server automatically re-creates the in-memory or relaxed-durability database from the template, wherever this was specified. When Replication Server resumes the connection to the replicate in-memory or relaxed-durability database that you created, Replication Server may reapply commands, as the information used by Replication Server to detect the last command applied is lost when you restart Adaptive Server.

Using a dump from another database to initialize in-memory and relaxed-durability databases

If you use a dump from another database to initialize the in-memory or relaxed-durability database as a replicate database, the replicate database inherits the configuration of the database from which you obtained the dump.

To set up replication using a database dump:

Create the in-memory or relaxed-durability database:

create inmemory database tokyo_db
on imdb_cache_dev2 = '50' log on
imdb_cache_dev_log2='50'
with DURABILITY=NO_RECOVERY
go

Create the objects such as tables and stored procedures, users, and permissions needed to receive replicated data; alternatively, you can load a database dump.
Use rs_init to create the Replication Server connection to the database.
Perform a dump to save the current state of the database:
1. Suspend the connection to the replicate in-memory or relaxed-durability database:
```
suspend connection to RDS.imdb1
go
```
2. Obtain a database dump of the database:
```
dump database imdb1 to '/databases/dump/tokyo_db.dump'
go
```
3. Resume the connection to the replicate database:
```
resume connection to RDS.imdb1
go
```

Re-creating relaxed-durability databases after an Adaptive Server shutdown

In-memory databases are re-created when you restart the server; you must therefore restore the data after each server restart:

Repopulate the re-created replicate in-memory or relaxed-durability database with a new dump from the dump source database or from any archived dumps taken on the replicate database.

Note: If the dump is not loaded from the dump of the source, there will be missing rows in the replicate tables.
For example, to load the tokyo_db database from the original tokyo_db.source dump when the host Adaptive Server restarts:
```
use master
go
sp_dboption tokyo_db, single, true
go
load database tokyo_db from
'/databases/dump/tokyo_db.dump'
go
online database tokyo_db
go
sp_dboption tokyo_db, single, false
go
```
Resume the connection to the re-created replicate in-memory or relaxed-durability database.

Enabling autocorrection

Enable autocorrection for the replication definitions used for subscriptions to the replicate in-memory or relaxed-durability database, to convert any update or insert into a delete and insert pair. You must set autocorrection before resuming a connection; this applies whether you used templates, or dump and load to create the replicate database. Enabling autocorrection allows Replication Server to continue replicating messages in Replication Server queues even if the Adaptive Server hosting a replicate in-memory or relaxed-durability database shuts down or terminates.

Minimal DML logging and replication

To optimize the log records that are flushed to the transaction log on disk, Adaptive Server can perform minimal to no logging when executing some data manipulation language (DML) commands—insert, update, delete, and slow bcp—on all types of low-durability databases, such as in-memory databases and relaxed-durability databases set with durability of at_shutdown or no_recovery. You can perform minimal logging for DMLs on a per-database, per-table, and session-specific basis. See Chapter 3, "Minimally-logged DML" in the Adaptive Server Enterprise In-Memory Database Users Guide.

Note: Minimal DML logging session-level settings take precedence over database-level settings and table-level settings.

Replication Server does not support a replication environment with in-memory databases, or where database durability set to no_recovery is acting as the primary database.

As replication uses full logging, replication and the minimal data manipulation language (DML) logging feature in Adaptive Server 15.5 are incompatible at the same level, such as the database level, or the table level. However, you can take advantage of the performance enhancements from minimal logging on some tables while allowing replication on others, as minimal DML logging and replication can coexist at different levels.

For example, if you set replication and minimal DML logging at the same level, such as table level, setting replication status fails and an error message appears, as described in these scenarios:

If you create a database to use minimal DML logging:
- And if you mark the database for replication using sp_reptostandby, the attempt fails:
```
Cannot set replication for database database_name
as it is minimally logged. Use ALTER DATABASE to
set full DML logging and try again.
```
- You can mark tables and stored procedures for replication to replicate a subset of tables. When you mark a table in a database using minimal DML logging, you see:
```
Warning: database_name is using minimal logging.
Replicated objects will continue to use full DML logging.
```
If a database is using full logging and you mark it for replication using sp_reptostandby, then try to alter the database to set minimal DML logging, the attempt fails:
```
Cannot alter database database_name to set minimal
logging because this database is marked for
replication. Remove replication and try again.
```
If a database is using full logging and has objects marked for replication, you can set minimal DML logging at the database level, but you see:
```
Warning: Database database_name has objects marked for
replication. Replicated objects will continue to use full logging.
```

If you create a table using minimal DML logging, and then mark the table for replication, the attempt fails:

Cannot set replication for table table_name because it is
using minimal logging. Use ALTER TABLE to set full logging and try again.

A database using full DML logging can contain tables defined to use minimal logging. If you mark the database for replication, you see:
```
Warning: Database database_name has tables that use
minimal DML logging. These tables will not be replicated.
```

If you create a table using full logging and mark the table for replication, then set minimal DML logging for the table, the attempt fails:

Cannot alter the table table_name to set minimal DML
logging because this table is marked for
replication. Remove replication and try again.