Destination guide · SQL Server
Give the SQL Server your application trusts the customer context Salesforce owns.
Many line-of-business and .NET applications already run on SQL Server or Azure SQL. The goal of a CRM mirror is the same as it is for Postgres or MySQL: make selected Salesforce records local, queryable, and recoverable—without teaching every service to manage Salesforce APIs and rate limits.
Scope the mirror around a product workflow.
SQL Server frequently backs internal platforms, billing systems, and customer-facing .NET applications. The cleanest CRM mirror brings only the Salesforce fields those workflows actually read. Start with a simple question: what customer question should the application answer without issuing a remote CRM query?
That usually produces a focused first mapping: Account and Contact for customer context, Opportunity or a custom entitlement object for account status, Case for support state. Document which system owns each field and whether a deleted Salesforce record becomes a soft-deleted local row.
Create tables that are safe to revisit.
Store the 18-character Salesforce record ID and put a unique index on it. The 15-character ID form is case-sensitive, and default SQL Server collations are not—the 18-character form is safe under case-insensitive comparison. Use nvarchar for Salesforce text (it is Unicode), datetime2 with a UTC convention for timestamps, and explicit decimal precision for currency fields.
Row-versioned reads keep batched sync writes from blocking the application's readers. Turning it on later means scheduling the change; turning it on first means never noticing the backfill.
For the upsert itself, a plain UPDATE-then-INSERT inside an explicit transaction is easy to reason about. A single MERGE statement can work too, but add HOLDLOCK and test concurrent paths before trusting it—its edge cases under concurrency are well documented.
Source ID: unique index on the 18-character Salesforce record identifier.
Relationships: retain parent record IDs so local joins remain possible.
Types: nvarchar for text, datetime2 for timestamps, explicit decimal precision for currency, validated picklist mappings.
Indexes: add them for your product queries, not just the ingestion worker.
Backfill once, then subscribe to changes.
Use a complete first backfill to establish the local starting point. Then subscribe to Salesforce Change Data Capture for the selected objects and apply each change with an idempotent upsert or deletion update. The database transaction and the event replay checkpoint belong together: if the local write fails, the checkpoint should not advance.
CDC is a latency path, not a complete operational policy. Pair it with a scheduled reconciliation for the selected objects, and decide in advance what happens when a record's permissions, field mapping, or source schema changes.
Operate the mirror like a small production service.
Track event lag, successful writes, source API errors, failed records, and reconciliation differences. Size backfill batches to stay under lock-escalation thresholds—around five thousand row locks per statement—so the sync never takes a table lock away from your application. On Azure SQL, confirm the service tier has headroom for the backfill's write burst.
SQL Server is also the destination most likely to live in a private subnet or on-premises network. That is the natural fit for a customer-network worker: it makes outbound connections to Salesforce and the connector control plane, while the database never requires inbound public access.
Implementation checklist
Map one workflow first: prove the tables solve a real application query before expanding object coverage.
Load safely: batched backfill writes under lock-escalation limits, with row-versioned reads enabled.
Make events repeatable: source ID plus idempotent writes and post-commit checkpointing.
Reconcile: compare a useful subset on a repeatable schedule and surface drift to an owner.
Running on SQL Server or Azure SQL?
We can help turn the customer data your app needs into a scoped, dependable local mirror.