We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

SQL Server schema for database mirroring

In this article

    Microsoft SQL Server physical schemas are used on both the SuperOffice CRM Online and partner ends. This makes the mirroring independent of various functional complexities, such as dictionaries (CRM, CS extratables).

    All tables except a blacklist will be mirrored. This is (at least initially) a fixed set. Because the physical schema is used, we don't distinguish between dictionary and normal tables. There is also no distinction between tables belonging to Sales, Service, extra-tables in Service, or any partner-defined tables that might appear in the future.

    Mirroring uses a simplified table schema. This means that foreign key constraints, collating sequences, and indexes are not mirrored.

    We don't support row- or column-level filtering: tables are either mirrored or not.

    Default schema 

    The schema/prefix is not mirrored. The mirror always uses the default schema. The connection string/login user can be used to influence the default schema.

    Metadata

    The client must maintain 1 table in the target database for required metadata  The <context identifier>_mirroring table is automatically created the first time the mirroring task runs. For example, Cust10195_mirroring. For each named table, the client must store a schema hash and the Log Sequence Number.

    Database index

    If you need database indexes, you should create and maintain them before or after each replication cycle.

    In the NuGet implementation, you can do this in one of the event-handling methods in IMirrorAdmin:

    • In OnBeforeReplicateTable, which is called once for each table in each mirroring cycle
    • Inn OnReplicationCompleted, which is called once at the end of a complete cycle

    Blacklisted tables

    Database tables that fall into one of the following categories are not mirrored:

    • irrelevant (travelgeneratedtransaction)
    • not useful (traveltransactionlog, countervalue)
    • confidential (credentials)

    When mirroring schema changes fails

    This might happen if a customer creates an extra table containing a character field through SuperOffice Service.

    1. A mirroring cycle is run and the table is created and populated in the mirror.
    2. Later, the customer drops the table, and re-creates it with the same name… and this time adds a DateTime field with the same name as the old character field. Farfetched, but possible.
    3. The mirroring system will only see that the schema has changed with one column changing data type. This change will fail!
    4. The client code supplied by SuperOffice will react by dropping the table in the mirror database, creating it with the new schema, and request a full repopulation.