Version: Next

ServiceNow Mirror Planner — Ingestion & Range Sharding

This guide covers operational use of the Cortex ServiceNow table mirror planner with the high-throughput ingestion pipeline (Phase 4a), optional sys_id range sharding across multiple Table API nodes (Phase 4b), and the runtime schema drift (Phase 5) and relational integrity (Phase 6) enhancements that run during and after mirror jobs.

For first-time setup (connections, table selection, archive database), start with Getting your ServiceNow data.

Overview

High-throughput ingestion

Mirror jobs copy data through a producer–consumer pipeline:

The producer fetches keyset pages from the ServiceNow Table API.
The consumer batches rows and upserts them into the archive database (DELETE + multi-row INSERT in a single transaction, with row-by-row fallback on failure).

Multiple tables can copy in parallel, bounded by MaxConcurrentMirrorTables and a job-scoped API throttle (GlobalApiConcurrency).

Range sharding

Very large tables can bottleneck on a single keyset cursor and one API endpoint. When sharding is enabled, Cortex splits the 128-bit sys_id hex space into uniform partitions using BigInteger math, then runs independent keyset pipelines per partition.

Each shard:

Owns a non-overlapping sys_id range (for example, quartering the space when ShardCount = 4).
Can be pinned to a different Table API base URL when you configure extra instance nodes on the mirror job.
Persists its own cursor in ServiceNowMirrorJobTableShard so interrupted jobs resume shard-by-shard.

Sharding activates only when both EnableSysIdRangeSharding is true and the table’s expected row count meets ShardRowCountThreshold.

Runtime schema drift

While rows are copying, Cortex can detect new fields (for example custom u_* columns) that were not in the initial sys_dictionary snapshot:

Channel	When it runs	What it does
Proactive (dictionary poll)	Producer, every N keyset pages	Re-reads `sys_dictionary` and merges new column specs into the live registry
Reactive (JSON key scan)	Consumer, before each batch insert	Compares incoming API row keys against known fields; triggers online `ALTER TABLE` for unmapped columns

Schema mutations run autocommit under a per-physical-table lock so sharded pipelines do not corrupt each other. This feature is off by default (EnableRuntimeSchemaEvolution: false).

Relational integrity

After data lands, Cortex strengthens the physical mirror for archive navigation:

Stage	When	What
Per-table indexing (`PhaseIndexing`)	After row copy for each table	Creates idempotent non-clustered indexes on scalar reference columns (`IX_sn_r_*`)
Job-end reconciliation	After all tables finish	Metadata rescan, catch-up indexes, soft orphan validation, M2M junction views

Orphan validation never fails the job—it writes warnings to the sync log. Junction views (vw_sn_m2m_{parent}_{field}) accelerate many-to-many navigation in the archive viewer.

Prerequisites

Cortex admin access and a ServiceNow API data source with admin Table API access.
An empty ServiceNow archive database (MariaDB, PostgreSQL, or SQL Server).
For multi-node sharding: one or more extra instance base URLs on the mirror job (same credentials as the primary node). Each URL becomes an API client in the job’s pool; shards round-robin across clients up to MaxConcurrentShardsPerTable.

Open the planner at Utilities → ServiceNow table mirror planner (/utilities/servicenow-table-planner) and monitor jobs at Utilities → ServiceNow mirror jobs (/utilities/servicenow-mirror-jobs).

Configuration

Sharding and ingestion settings live under ServiceNowMirror:Ingestion in appsettings.json (or environment variables). All keys have defaults in code—the JSON block is optional and documents tunables for operators.

"ServiceNowMirror": {
  "Ingestion": {
    "EnableSysIdRangeSharding": false,
    "ShardCount": 4,
    "ShardRowCountThreshold": 250000,
    "MaxConcurrentShardsPerTable": 4
  }
}

Environment variable override example:

ServiceNowMirror__Ingestion__EnableSysIdRangeSharding=true
ServiceNowMirror__Ingestion__ShardCount=4

Sharding keys

Key	Default	Operational meaning
EnableSysIdRangeSharding	`false`	Master switch. When `true`, eligible tables split into parallel `sys_id` range pipelines.
ShardCount	`4`	Number of hex partitions (1–32). Example: `4` quarters the 128-bit space at `4000…`, `8000…`, `C000…` boundaries.
ShardRowCountThreshold	`250000`	Minimum expected rows before sharding is considered. Smaller tables use a single pipeline even when sharding is enabled.
MaxConcurrentShardsPerTable	`4`	Max shard pipelines running at once per table (1–16). Set ≤ number of API nodes you configured on the job.

Key	Default	Role
`MaxConcurrentMirrorTables`	`4`	Parallel table copies per job
`GlobalApiConcurrency`	`8`	Max concurrent Table API calls per job
`KeysetPageSize`	`500`	Rows per keyset page (application tables)
`PrefetchPageDepth`	`2`	Producer prefetch depth
`TargetBatchSize`	`50`	Rows per consumer batch insert

Schema drift & relational integrity (optional)

Section	Key	Default	Role
`ServiceNowMirror:SchemaDrift`	`EnableRuntimeSchemaEvolution`	`false`	Turn on proactive + reactive drift handling
	`DictionaryPollIntervalPages`	`50`	Pages between dictionary polls
`ServiceNowMirror:RelationalIntegrity`	`EnablePostCopyReferenceIndexes`	`true`	Per-table + job-end reference indexes
	`EnableJobEndReconciliation`	`true`	Job-end metadata rescan and catch-up
	`EnableOrphanValidation`	`true`	Soft FK orphan audit (warnings only)
	`EnableJunctionViews`	`true`	Create `vw_sn_m2m_*` views for M2M fields

How it works

Planner workflow (with sharding)

Connection — Primary instance URL + credentials (or saved API data source).
Select tables — Catalog shows storage strategy (TPH, TPC, TPP, Standalone) and physical storage table name.
Analyze & export — Dependency expansion (metadata, references, inheritance, attachments).
Archive database — Target ServiceNow archive connection.
Extra instance URLs (recommended for sharding) — Add additional Table API base URLs for multi-node instances. The job builds one API client per URL; shards distribute across them.
Start mirror job — Background worker runs copy, indexing, and job-end reconciliation.

Shard lifecycle

Table copy starts
  → Expected rows ≥ ShardRowCountThreshold and sharding enabled?
       No  → Single keyset pipeline (standard copy)
       Yes → EnsureShardState: N shard rows in job DB
            → Up to MaxConcurrentShardsPerTable parallel pipelines
            → Each shard: sys_id range filter + dedicated API client index
            → Cursor saved per shard on each page
  → All shards complete → table proceeds to PhaseIndexing

Shard phases in the database: Pending → Copying → Complete (or Failed). Resume an interrupted job from the mirror jobs dashboard—shards with saved cursors continue where they left off.

Schema drift channels

Proactive (dictionary poll)

Runs on the producer every DictionaryPollIntervalPages keyset pages.
Fetches updated sys_dictionary rows for the logical table.
Merges new ColumnSpec entries into the shared ServiceNowMirrorRuntimeColumnRegistry (shared across shards on the same physical table).

Reactive (JSON key scan)

Runs on the consumer immediately before each batch insert.
Compares each row’s API keys to KnownLogicalElements (ignores _display_value and _link suffix keys).
For new keys: infers SQL type (dictionary row first, then heuristics on batch values), then runs ALTER TABLE under the table schema coordinator lock.
Updates the registry so subsequent pages include the new column in sysparm_fields and insert mapping.

Enable both channels with EnableRuntimeSchemaEvolution: true. Leave off until you have validated drift behavior in a non-production mirror.

Indexing and job-end reconciliation

Per table — PhaseIndexing

After row copy and row-failure retry, before count verification:

Table phase transitions to Indexing (UI label: Creating reference indexes).
ServiceNowMirrorReferenceIndexEvolver scans scalar reference fields whose target table is in the mirror schedule.
Creates idempotent indexes IX_sn_r_{table}_{column}_{seq} (truncated to 63 characters for PostgreSQL).
Phase advances to Verifying.

Job-end — relational integrity coordinator

When all scheduled tables have finished (and the job is not re-queued for resume):

Full metadata rescan (sys_dictionary, sys_m2m).
Catch-up reference indexes for any columns added during Phase 5 drift.
Soft orphan validation — anti-join counts for broken reference links (TRIM(fk) <> '' skips unassigned fields). Warnings only; job still completes.
Junction views — CREATE VIEW vw_sn_m2m_{parent}_{field} projecting parent_sys_id, child_sys_id, junction_sys_id from M2M link tables. Archive viewer M2M relationships prefer these views over heuristic junction-table queries.

Monitoring & telemetry

Mirror jobs dashboard

Utilities → ServiceNow mirror jobs shows macro job status and per-table phase. Watch for these table phase transitions during a healthy large-table run:

Phase	UI label	Meaning
`Copying`	Copying rows	Keyset / shard pipelines active
`Indexing`	Creating reference indexes	Post-copy reference index DDL
`Verifying`	Verifying	Row count and failure checks
`Complete`	Complete	Table finished successfully

For sharded tables, inspect shard rows in the job database (ServiceNowMirrorJobTableShard) if a table stalls in Copying—one failed shard can block completion.

Sync log panel

Each job stores the last 100 structured log lines (SyncLogJson). Log entries include a phase tag:

Phase tag	When it appears
`metadata`	Catalog / `sys_dictionary` / `sys_db_object` loading
`schema`	DDL, topology, per-table index creation messages
`datacopy`	Page progress during row copy
`relationalintegrity`	Job-end reconciliation, orphan warnings, junction views

Messages to expect at job end (phase tag relationalintegrity):

Job-end relational integrity reconciliation starting…
Catch-up reference indexes ensured for N physical table(s).
Relational integrity: {table}.{column} → {target}: N orphan reference linkage(s). Sample source sys_id(s): … (warnings only)
Orphan validation complete: X field(s) scanned, Y with orphan linkages, Z total orphan reference(s).
Junction navigation views ensured for N M2M link field(s).
Job-end relational integrity reconciliation complete.

Orphan messages indicate data quality gaps in the mirror (missing target rows), not a failed job. Investigate source ACL filtering, partial table selection, or timing if counts are high.

Server debug logs

When log level is Debug, Table API calls include ServiceNowApiCallContext tags:

(phase: datacopy, worker: 2, shard: 1)

Use these to correlate slow pages with a specific shard and API node. See Logging for enabling file or console debug output.

Operational checklist

Enabling sharding for a large mirror

Set EnableSysIdRangeSharding to true (config or env var).
Confirm ShardCount and MaxConcurrentShardsPerTable match your API node count.
Add extra instance base URLs on the mirror job in the planner (one per node).
Set ShardRowCountThreshold above your smallest table that should not shard.
Monitor mirror jobs dashboard and sync log during first production run.
Resume rather than restart if the job interrupts—shard cursors are persisted.

Enabling schema drift (optional)

Set ServiceNowMirror:SchemaDrift:EnableRuntimeSchemaEvolution to true.
Start with a test mirror; confirm new u_* fields appear without job failure.
Watch for DDL timeout messages; increase SchemaMutationTimeoutSeconds if needed.

After job completion

Confirm tables reached Complete or Complete with warnings.
Review sync log for relationalintegrity orphan warnings.
Spot-check archive viewer M2M relationships on tables with sys_m2m metadata.
Register or refresh the ServiceNow archive data source in Cortex pointing at the mirror database.

Troubleshooting

Symptom	Likely cause	Action
Large table slow; single API node saturated	Sharding off or no extra URLs	Enable sharding; add extra instance URLs; raise `MaxConcurrentShardsPerTable`
Table stuck in `Copying`	One shard failed or stalled	Check shard `LastError` in job DB; resume job; review API rate limits
Sharding not activating	Row count below threshold	Lower `ShardRowCountThreshold` or accept single-pipeline copy
New columns missing in mirror	Schema drift disabled	Enable `EnableRuntimeSchemaEvolution`
Job completes but orphan warnings	Expected with partial mirrors	Expand mirrored reference targets or accept soft warnings
M2M relationships slow in archive	Junction views missing	Confirm `EnableJunctionViews` and mirrored `sys_m2m` / junction tables

For connection, catalog, and first-time planner issues, see Getting your ServiceNow data.

Overview​

High-throughput ingestion​

Range sharding​

Runtime schema drift​

Relational integrity​

Prerequisites​

Configuration​

Sharding keys​

Related ingestion keys (not sharding-specific)​

Schema drift & relational integrity (optional)​

How it works​

Planner workflow (with sharding)​

Shard lifecycle​

Schema drift channels​

Indexing and job-end reconciliation​

Monitoring & telemetry​

Mirror jobs dashboard​

Sync log panel​

Server debug logs​

Operational checklist​

Enabling sharding for a large mirror​

Enabling schema drift (optional)​

After job completion​

Troubleshooting​

See also​