Legacy TMS and SQL architecture: carrier field guide

Begin with the operating contract

A slow dispatch board is an operating symptom, not a diagnosis. The same delay can originate in a blocked database session, an overloaded reporting server, an expensive address lookup, a network retry, or a third-party integration waiting for acknowledgment. Begin by recording exactly which action fails: opening an order, assigning equipment, calculating a tariff, releasing an invoice, or exporting settlements. Capture the time, affected terminal, user role, order identifier and application build. Compare an affected transaction with a successful transaction of the same kind before changing configuration.

Agree on the business acceptance test with dispatch and finance. For example, a synthetic order should move from accepted tender through a two-stop movement, proof of delivery, rated accessorials and an invoice export without duplicate charges. Choose response-time and completion targets from the carrier's operating needs, not an arbitrary benchmark. Record the workload size, concurrency, cache condition and integration traffic for every measurement. A faster isolated query is useful only if the live workflow remains correct during a normal dispatch shift.

Create a dependency map covering application servers, database instances, scheduled jobs, document storage, EDI translators and accounting exports. Identify owners and support contracts. Mark whether a component participates in transaction processing or reporting. A dashboard that silently queries the dispatch database every few seconds can consume capacity needed for load entry. Move heavy reporting to a supported replica, extract or warehouse only after defining freshness requirements and checking the effect of replication on recovery and maintenance.

Separate database engines and vendor schemas

TruckMate and McLeod environments must be assessed by their actual installed product version, database platform and vendor support agreement. Never translate a SQL Server maintenance recipe directly into a Db2 environment. Confirm the engine, edition, compatibility settings, database size and supported access mechanism with the system owner. This guide does not assert that every release of either product uses a particular database. Vendor table names, procedures and supported customization points vary; obtain the installation's data dictionary and approved integration documentation.

Prefer supported APIs, exports and reporting views for integrations. If read access is authorized, grant a dedicated account only the objects needed for the task. Direct writes to vendor transaction tables can bypass application rules for rating, audit history, dispatch state or accounting posting. Treat a request for a database fix as a controlled change requiring vendor guidance, a reproducible defect and recovery evidence. Keep credentials out of reports and scripts, and sanitize customer references before sharing diagnostics outside the carrier.

Map order to cash at the correct grain

Write down what one row means in every source. An order may have several stops, several movements, multiple freight items, split invoices and more than one settlement. Joining these children directly can multiply revenue and miles. Build an explicit crosswalk between customer load references, internal order keys, movement keys, invoice keys and accounting document identifiers. Include terminal or legal entity in composite business keys whenever identifier sequences overlap. Keep the original source key even when the target assigns a new surrogate key.

Describe each state transition with its responsible role and evidence. Dispatch acceptance establishes an operational commitment; delivery evidence supports billing; invoice posting establishes a financial document; cash application settles a receivable. These are different events. An invoice marked exported may still be rejected by accounting, and a delivered order may still lack billable accessorial documentation. The reconciliation should compare states as well as amounts. Store currency, tax treatment, rounding rules, void status and posting dates with each financial comparison.

The following synthetic SQL Server reporting contract assumes exactly one row per order in reporting.OrderControl and one row per charge in reporting.ChargeControl. Its names are illustrative, not TruckMate or McLeod tables. Run it against a controlled reporting extract with a stable snapshot boundary. The query preaggregates charges before joining orders, preserves zero-charge orders and exposes missing charge totals. Replace the contract only after validating keys, currency and void handling with the carrier's finance owner.

-- Synthetic reporting contract; SQL Server syntax, read-only.
DECLARE @FromUtc datetime2(0) = '2026-09-01T00:00:00';
DECLARE @ToUtc datetime2(0) = '2026-10-01T00:00:00';
WITH Charges AS (
    SELECT CompanyId, OrderId, CurrencyCode,
           SUM(CAST(Amount AS decimal(19,4))) AS ChargeAmount
    FROM reporting.ChargeControl
    WHERE IsVoided = 0
    GROUP BY CompanyId, OrderId, CurrencyCode
)
SELECT o.CompanyId, o.OrderId, o.CurrencyCode,
       o.ExpectedAmount, c.ChargeAmount,
       o.ExpectedAmount - COALESCE(c.ChargeAmount, 0) AS Difference,
       CASE WHEN c.OrderId IS NULL THEN 1 ELSE 0 END AS MissingCharges
FROM reporting.OrderControl AS o
LEFT JOIN Charges AS c
  ON c.CompanyId = o.CompanyId
 AND c.OrderId = o.OrderId
 AND c.CurrencyCode = o.CurrencyCode
WHERE o.DeliveredAtUtc >= @FromUtc
  AND o.DeliveredAtUtc < @ToUtc;

Diagnose SQL performance before maintenance

Capture the query text, parameter values and execution plan for the slow workflow using the platform's supported diagnostic tools. Compare estimated and actual row counts, logical reads, elapsed time, CPU time and waits. Check whether a plan changed after a deployment, statistics refresh or unusual customer batch. Avoid exporting full query text if it contains personal or commercial data. A diagnosis should explain the expensive operation and its relationship to the dispatch symptom, not simply attach a large collection of database screenshots.

Look for non-sargable filters, accidental many-to-many joins and unnecessarily wide projections. Express a time window as an inclusive lower bound and exclusive upper bound, as shown above, rather than applying a conversion to every timestamp. Check implicit datatype conversions between joined keys. Separate data quality defects from plan defects: a duplicate customer crosswalk can inflate results even when the query runs quickly. Changing an index will not repair a broken business key, and adding a timeout may only conceal a lock problem.

Review blocking chains with the database administrator. Long transactions can retain locks while an application waits for a printer, document service or remote endpoint. Establish whether the transaction scope is supported by the application and whether the integration can use an outbox pattern. Do not add dirty-read hints as a universal performance fix. In a revenue or settlement report, reading uncommitted changes may produce an apparently balanced result that never existed as a committed business state. Select an isolation strategy through platform and application testing.

Inspect indexes without automatic rebuilds

For SQL Server rowstore indexes, inspect fragmentation alongside index size and workload behavior. Page density can matter as much as logical page order. Microsoft's index maintenance guidance explains these measures and the resource costs of maintenance. A fixed percentage rule is not evidence that a rebuild will improve dispatch. Small indexes, cached workloads and different storage systems can respond differently. Record the reason for any action and compare the same representative query before and after it.

The diagnostic below scopes its physical scan to one approved table. Set the object name deliberately; the guard prevents a missing object from becoming an unintended broad scan. It uses LIMITED mode to reduce inspection work and therefore does not request a page-density measurement. Even diagnostic scans can compete with production activity. Have the administrator verify the required permissions, execution location and maintenance window, especially on replicas. This script performs no reorganization, rebuild, statistics update or vendor data modification.

-- SQL Server rowstore diagnostic; set a reviewed table explicitly.
DECLARE @TargetObjectId int = OBJECT_ID(N'reporting.OrderControl', N'U');
IF @TargetObjectId IS NULL
    THROW 50001, 'Approved reporting table not found; stop.', 1;
SELECT OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
       OBJECT_NAME(p.object_id) AS TableName,
       i.name AS IndexName, p.partition_number,
       p.page_count, p.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
     DB_ID(), @TargetObjectId, NULL, NULL, 'LIMITED') AS p
JOIN sys.indexes AS i
  ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.index_id > 0 AND p.index_level = 0
  AND p.alloc_unit_type_desc = 'IN_ROW_DATA'
ORDER BY p.page_count DESC;

In Db2, use the database administrator's supported RUNSTATS and REORG workflow. IBM's REORGCHK documentation distinguishes inspecting existing statistics from updating them. REORGCHK UPDATE STATISTICS can perform statistics work; it is not a passive report. Review table size, available storage, logging, replication and concurrent workload before any reorganization. Record the exact engine version and chosen options. Keep SQL Server commands out of the Db2 runbook and have the vendor confirm maintenance choices for application-managed objects.

Design a reversible change window

For every proposed index or query change, define a single hypothesis and an expected observation. Save the current definition, execution plan and relevant configuration. Verify free space, log growth, backup completion and recovery capabilities. Assign a person who can stop the operation, and make the stop condition concrete: unacceptable dispatch latency, blocking duration, replication lag or storage pressure. Rehearse on a restored environment that resembles production, with synthetic or appropriately protected data. A restore that has never been attempted is not a demonstrated fallback.

Evaluate write amplification before adding a covering index. Every additional indexed column can increase storage, log traffic and update work. Confirm whether a proposed index duplicates an existing supported index and whether the application upgrade process recreates or removes custom objects. Use vendor-approved naming and change registration. After deployment, compare the original dispatch operation and neighboring workflows, including load entry, rate changes and invoice release. Keep a rollback definition that accounts for new dependent queries rather than assuming an index can always be dropped without consequence.

Build the migration evidence ledger

Define a migration batch with an immutable source boundary, extraction time, mapping version and checksum manifest. Preserve raw extracts separately from transformed staging data. Reconcile counts and values by company, terminal, status, currency and accounting period. A whole-database row count can look correct while open receivables move to the wrong entity. Track rejected rows individually, including the rule that failed, source key, owner and disposition. Never silently convert an invalid customer, currency or date into a default value merely to make the load finish.

Use a deliberate delta strategy for activity after the baseline extract. Record the high-water mark and prove how updates, deletes, voids and late postings are represented. A timestamp alone may be insufficient if application updates do not consistently refresh it. Test cancellation after extraction, invoice reissue, reassigned movement, split billing and settlement correction. The target must preserve the history needed to explain each outcome. Rehearse the final freeze with dispatch and accounting so everyone understands which transactions can continue and which must wait.

Accept the system through business evidence

Run a representative rehearsal through dispatch, document collection, rating, billing and settlements. Include a normal move, a changed appointment, a rejected tender, a multi-stop order, a voided invoice and a corrected charge. Compare both visible screens and downstream accounting records. Have users explain any difference before signing off. Collect the artifacts in a dated acceptance ledger with source and target identifiers. Strong Tower Consulting uses this kind of bounded evidence review to connect architecture decisions to freight-floor consequences rather than treating database performance as a standalone objective.

At go-live, assign an incident lead, a database owner and a finance reconciliation owner. State which system is authoritative at every point in the cutover. If rollback is triggered, stop new target writes, preserve the target journal and reconcile transactions that occurred after the switch. Restoring an old backup without accounting for those transactions can lose work. Close the migration only when unresolved exceptions have owners, required historical records remain accessible and the carrier has demonstrated its normal support and recovery procedures.

Related advisory and scope

Use legacy TMS advisory to decide whether to stabilize, optimize or migrate, and SQL data mining to define a reporting contract. The companion EDI implementation blueprint covers message evidence and replay. Acquisition teams can carry the same reconciliation evidence into the carrier technology due diligence playbook. These are proposed engineering methods, not claims of measured customer outcomes or vendor certification.

TruckMate, McLeod, Microsoft SQL Server and IBM Db2 names identify third-party products and remain the trademarks of their respective owners. The scripts are sanitized reference examples for engineering review and adaptation; they have not been executed against a customer database or certified for a particular vendor installation.

Frequently asked questions

Should every fragmented index be rebuilt?

No. Measure workload performance, page density, statistics, maintenance cost and blocking before choosing an index operation.

Do the SQL examples use vendor production tables?

No. The reconciliation example uses a synthetic reporting contract; the diagnostic query uses SQL Server system views. Neither is a vendor schema specification.

Scott Elliott, Founder and Principal Systems Architect. Scott Elliott, Founder and Principal Systems Architect, Strong Tower Consulting, LLC. Minnesota SOS filing 2948628-2. Continuous commercial operation since July 29, 2008. The firm name comes from Proverbs 18:10.