Database Replication

Owned by Michael Standal
Last updated: Jul 24, 2023 by Emma Fullilove (Deactivated)
1 min read

Overview

This guide provides recommendations for SQL Merge Replication to keep ACM working in case of a fail-over. However,  more can be done using replication. Below is an outline of the core tables to help guide you in setting up your system.

Temporary/Data Tables

The following tables are treated as temporary tables and the data is constantly changing. Table data is used only for calculation and/or movement which is re-generated on demand. These tables should NOT be replicated.

  • JobSchedules
  • ScheduledJobs
  • ScheduledJobSteps
  • tblArchiveStaging
  • tblCommStatsStaging
  • tblDailyRecs
  • tblDailySummary
  • tblHourlyRecs
  • tblHourlySummary
  • tblGcWritePending
  • tblItemStaging
  • tblObjectId
  • tblPubStaging

Required Data Tables

This set of tables contain the data that is necessary for ACM configuration, history collection sequence positions and item persistence. If you want a fail-over ACM system that starts up with current data and last known item data, then these are REQUIRED.

  • tblArchivePosition
  • tblItems
  • tblObjects
  • tblProperties
  • *tblServerDirectory : this is only required if you are using the Server Directory function

Historical EFM Data Tables

Historical EFM data is stored in multiple tables. If you replicate your EFM data, you should also replicate the publisher position pointers to prevent accidental republishing of your data on fail-over.

  • tblArchiveRecords
  • tblCommStats
  • tblLiquidRecords
  • tblMeterRecords
  • tblMeterRecordsHistory
  • tblPublishedRecord
  • tblPublishPosition

History Tables

These history tables contain no EFM records. They log changes, actions, and errors. Replicating them is optional.

  • ErrorLog
  • ScheduledJobsHistory
  • tblArchiveExec
  • tblCommandLog
  • *tblChanges : note this table is auto-populate on a trigger from tblObjects and tblProperties
  • tblGcWritePendingAudit
  • SchedulingErrors

Lookup Tables

This set of tables contain information to help decode records and store the database version information. They do not change between builds, so replication is optional.

  • tblDbVersion
  • tblGcWriteResultLookup
  • tblLookupCommType
  • tblLookupPeriod
  • tblLookupPollFailCode
  • tblLookupPollStatus
  • tblLookupRefProp
  • tblLookUpTypeID


For assistance, please submit a ticket via our Support Portal, email autosol.support@autosoln.com or call 281.286.6017 to speak to a support team member.