You can configure an MDrivenServer HistorySlave to retain time-stamped versions of data from one Master server, for administrators who need to investigate how current data reached its state.

A HistorySlave receives committed changes from the Master through MDrivenServerSynk. Before it applies those changes, it treats classes as versioned: it adds timestamps to stored data and inserts new versions rather than updating the existing row. The result is a history database that you can query from the perspective of the current model definition.

Before you start

Read Purpose of History Server before choosing this architecture. A history database records data history, not a history of model definitions.

For example, if the current model no longer contains an attribute, historic values for that attribute cannot be represented through the current definition. Keep the model definition in source control, and use database backups when you need complete snapshots of both data and definition.

Prepare the following:

• One MDrivenServer that will be the Master. A cluster has exactly one Master.
• A separate database for the HistorySlave. Start it from a backup of the Master database.
• Connectivity from the HistorySlave to the Master. The slave initiates communication with the Master.
• Master credentials for the HistorySlave configuration. Both the user name and password are case-sensitive.
• Sufficient database capacity. A HistorySlave retains versions instead of replacing rows, so its database grows as changes are received.

Do not rely on SQL Compact for a production history database. It can be used for testing, but a history database will normally require more space.

How synchronization works

The Master stores commit packages in the MDrivenServerSynk table as part of the transaction that updates business data. A slave polls for these packages and applies them to its own database. This means a HistorySlave follows the Master after the initial backup; it is not a database backup mechanism.

There can be a short delay between a change on the Master and its appearance on the HistorySlave. If the HistorySlave cannot reach the Master, it cannot receive updates until connectivity is restored.

The MDrivenServerSynk table holds commit packages until they are removed by the MaxQueueLengthInDays setting. Ensure that the Master retains packages long enough for the HistorySlave to collect them.

Set up the Master

1. Open the MDrivenServer that uses the production or source database.
2. Ensure that MDrivenServerSynk exists in the database.
3. Change the server mode from Normal to Master.
4. Confirm that the Master is operating before creating the history database.
5. Create a backup of the Master database.

Set up MDrivenServerSynk before taking the backup. The restored history database needs this table and its current synchronization state to begin following the Master.

Running a server in Master mode is also useful when no slaves are currently connected: refresh data is loaded from MDrivenServerSynk, rather than being held only in server memory. Consequently, refresh information survives a server restart.

Create the HistorySlave database

1. Restore the Master database backup as a new, separate database for the HistorySlave.
2. Configure a second MDrivenServer to use this restored database. Follow the applicable installation and server setup guidance in Documentation:Server.
3. Do not use the Master database as the HistorySlave database.

For example, if the Master uses MtDev, restore its backup to a separate database such as MtDevHistory. Put the history database on separate infrastructure when you need to isolate database load or storage from the Master.

Configure HistorySlave mode

1. Open the second MDrivenServer configuration.
2. Change its mode to HistorySlave.
3. Specify the Master MDrivenServer and enter the Master user name and password. User name and password are case-sensitive.
4. Check that the HistorySlave version is compatible with the Master version.
5. Retrieve the initial model from the Master by using Get model from master. This is the initial model setup for the slave.
6. Start or restart the HistorySlave as required by the server configuration.

After initial setup, the HistorySlave obtains model changes from the Master when it receives a commit package whose model checksum differs from its own. It evolves its database before applying that package, so do not maintain an independent model on the HistorySlave.

Verify the setup

1. Make a small, identifiable change through the Master. For example, change a company's name from Northwind to Northwind AB and save it.
2. Wait for the HistorySlave to poll the Master and apply the committed change.
3. Confirm that the current data appears in the HistorySlave.
4. Confirm that the previous version remains available in the HistorySlave rather than being overwritten.
5. Use a history-aware prototype and the oclAtTime OCL operator to inspect data at an earlier point in time.

A delayed update is expected because synchronization uses MDrivenServerSynk. For a repeatable functional test, see HowTos:Test Slave Server Setup.

Versioning effects and database changes

When HistorySlave mode prepares the history database, the versioning process adds StartTime and StopTime columns to tables. The operation that performs this is triggered by EnsureVersioningTablesInDB.

One-to-one associations can have unique constraints in a normal database. Those constraints are not valid across all time in a history database because multiple historic versions can exist. The versioning setup drops the relevant unique constraints as part of preparing the versioning tables.

Exclude classes from history

Use the class tagged value NoHistory=true when a class should not be versioned by the HistorySlave.

For example, an externally owned reference-data class that is imported every day may change frequently but have no business value as historic data. Add NoHistory=true to that class before history is created.

Tagged value	Effect	Important limitation
NoHistory=true	The HistorySlave skips the class during versioning.	Changing an existing class from versioned to not versioned is not supported. Set the value from the start, or recreate the table to force the changed behavior.
HistoryKeepWeeks=NoOfWeeks(int)	Retains history for the specified number of weeks for that class.	Culling can make historic references appear as null when the referenced object's older version has been removed.

Retain and cull history

History retention is configured per class with HistoryKeepWeeks=NoOfWeeks(int). Use shorter retention only when the resulting incomplete historical perspective is acceptable.

For example, an old version of an Order may refer to a Customer version that has been culled under a shorter retention period. When you inspect the old Order, that reference appears as null, even though it had a value at the time.

To run the currently documented culling action:

1. Open the server administration interface.
2. Go to admin/MasterSlave/TruncHistory3Years(156 weeks).
3. Run the action to cull history older than 156 weeks.

Review retention requirements before running this action. Culling removes historic data and can affect the interpretation of older versions.

Reduce synchronization data when creating history from production

When you restore a production database to create a history database, the copied MDrivenServerSynk table can already contain substantial historical synchronization data. Consider truncating most of this table while retaining its tip, so the new HistorySlave does not begin with an unnecessarily large synchronization backlog. This can reduce backup size and improve read and update performance for that table.

Plan this operation carefully. The retained synchronization data must still allow the HistorySlave to continue from the copied state and receive subsequent changes from the Master.

Find large tables in SQL Server

Use the following SQL Server query to identify tables with the most rows and allocated space in the history database. Large, rapidly growing tables can indicate classes that need a retention decision or NoHistory=true.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0 
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    p.rows DESC, t.Name

See also

• Documentation:Purpose of History Server
• Documentation:Slave and History Server
• HowTos:Test Slave Server Setup
• Documentation:Scaling MDrivenServer for multiple reasons
• Documentation:Server