SQL Server schema for Database Mirroring

• 4 minutes to read

The replica database uses Microsoft SQL Server dialect end-to-end. Because the physical schema is what gets replicated, the client does not distinguish between dictionary and normal tables — or between tables belonging to Sales, Service, extra tables in Service, or partner-defined tables that might appear in the future.

The schema is simplified: foreign key constraints, collating sequences, and indexes are not mirrored.

Note

Row- and column-level filtering is not supported. Tables are either mirrored or not.

Destination schema (`crm7` vs `dbo`)

The client preserves the source schema by default — typically crm7. Most replicated tables therefore end up as crm7.contact, crm7.person, crm7.sale, and so on.

If your downstream applications expect tables in dbo (or another schema), you can override the destination with the TablePrefix setting in appsettings.json:

{
  "ReplicaDatabase": {
    "TablePrefix": "dbo"
  }
}

`TablePrefix` value	Result
Empty or omitted (default)	Preserves source schema — typically `crm7`
`"dbo"` (or any other name)	All replicated tables created in that schema

Important

TablePrefix only takes effect during the initial snapshot. It must be set before you run provision. Changing it after provisioning does not move tables between schemas.

For details on choosing the right schema during a migration, see Schema alignment.

Session state: the `_ReplicationState` table

The client maintains one internal table in the replica database: _ReplicationState. It is created automatically during provision and stores:

The session identifier and tenant context.
The encrypted access and refresh tokens (encrypted with Windows DPAPI, LocalMachine scope).
Replication progress offsets per Kafka topic and partition.
The schema hash per replicated table.

Because the session state lives inside the replica database, it follows the database through backups, restores, and renames. No re-provisioning is required as long as the database itself moves with the client.

Warning

Do not edit, truncate, or drop _ReplicationState manually. If you need to start over, use resync-tables or, in extreme cases, re-provision against an empty database.

Indexes and other auxiliary objects

The client creates and maintains only the replicated tables themselves. Any indexes, views, synonyms, or triggers you need on the replica must be created and maintained by you.

Good places to do this in your own deployment:

As part of an initial post-provision setup script.
After table changes detected through update-schema.

Blocked tables

The replica contains a useful subset of the source database, not an exact copy. Tables that fall into one of the following categories are excluded:

Irrelevant — for example travelgeneratedtransaction.
Not useful for analytics — for example traveltransactionlog, countervalue.
Confidential — for example credentials.
Binary blobs and dictionary cache — the binaryobject table and dictionary information tables are excluded to keep the replica cleaner and faster.

See the full list of blocked tables.

When schema changes fail

This can happen if a customer creates an extra table containing a character field through SuperOffice Service, drops it, and re-creates it with the same name but a different data type for one of the columns.

In that case the client reacts by dropping the affected table in the replica, re-creating it with the new schema, and triggering a full re-snapshot for that table.