How to copy hundreds of on-premises SQL Server databases into a brand-new Snowflake platform while the source keeps running. This page walks through the options, what we recommend, what we advise against, and how the new Snowflake account is structured and secured.
Prepared by Jitendra Chauhan · +91 8368258062 · jitechau01@gmail.com
Part 1 · Understand01–03
01
The use case
The client runs Microsoft SQL Server on its own servers. It holds hundreds of databases, each with its own set of tables, about 3–4 TB in total. The client wants all of this data in Snowflake. This is the client’s first Snowflake purchase, so the account, environments, access model and security also need to be designed from scratch.
Three facts shape every decision on this page:
Only data moves. No stored procedures, views, SSIS packages or reports. Only tables.
SQL Server stays live. Applications keep writing to it, so Snowflake must be kept up to date after the first copy.
The schema is simple. At most 16 columns per table and only ten data types: bigint, int, bit, decimal, numeric, datetime, char, varchar, nvarchar, varbinary. This makes full automation realistic.
Snowflake tables generated from SQL Server metadata
Switching applications to Snowflake (future cutover)
One-time historical load of every table
Data modelling, marts and dashboards on top of the copy
Ongoing delta sync: inserts, updates and deletes
Decommissioning SQL Server
Validation, reconciliation, parallel run, runbooks, knowledge transfer
02
Terms in plain words
You don’t need a Snowflake background to follow this page. These are the terms used most often.
Historical load
The one-time copy of all data that exists today.
Delta sync
Regular copying of only what changed since the last run: new rows, updated rows, deleted rows.
bcp
Microsoft’s built-in “bulk copy program”. Very fast at dumping a table to a text file, but weak with Unicode, binary data and special characters.
Parquet
A compressed file format that stores each column with its exact data type. Safer and 3–5× smaller than text files.
Change Tracking (CT)
A lightweight SQL Server feature that records which rows changed, including deletes. We read it to find the delta.
Change Data Capture (CDC)
A heavier SQL Server feature that records every change with before and after values. More detail than a mirror copy needs.
Stage · PUT · COPY INTO
A stage is Snowflake’s landing area for files. PUT uploads files to it; COPY INTO loads them into a table.
MERGE
One SQL statement that inserts new rows, updates changed rows and removes deleted rows, so the copy matches the source.
Medallion layers
Bronze = exact copy of the source; Silver = cleaned and joined; Gold = business-ready. This project delivers Bronze.
Warehouse
In Snowflake, a warehouse is compute power you switch on to run work. You pay only while it runs.
RBAC
Role-based access control. People get roles, roles get permissions. Nobody gets permissions directly.
Openflow
Snowflake’s own data integration service, with a ready-made SQL Server connector.
03
Approach at a glance
Because the source keeps running, the migration has two parts that use different techniques: a bulk historical load that moves 3–4 TB once, and a delta sync that then keeps Snowflake current. Every load is checked automatically, and both systems run side by side until the client signs off.
On-prem · client network
SQL Server100s of databases · stays live
Record CT versionbefore the bulk extract starts, so no change is missed
Bulk extractPython → Parquet (bcp for numeric-only tables), chunked by date or key
Change Trackingenabled per table, DBA approved
Ingestion
Parquet files100–250 MB each · ~0.8–1.3 TB in total
PUTupload to internal stage over HTTPS · no extra cloud storage
delta · choose one
A · Openflow connectorSnowflake-native, near real-time
B · CT + Python + Airflow ★recommended · scheduled, then MERGE
C · Fivetran/HVR · Qliklicensed, managed
Snowflake · per environment
Internal stagein PRD_ADMIN
Landing tablesPRD_ADMIN · file loads and change batches
COPY INTO / MERGE ↓
PRD_RAW · Bronzeexact mirror · one schema per source DB_SCHEMA
The first copy moves 3–4 TB once. What matters: speed, restarting a failed piece without starting over, getting Unicode and binary data across exactly, and not slowing the live source.
H1Python extract → Parquet → PUT → COPY INTO
Recommended
Python (pyodbc + pyarrow) reads each table in chunks by date or key range, writes compressed Parquet files, uploads them to a Snowflake internal stage and loads them with COPY INTO. Driven by SQL Server metadata, so hundreds of databases need no hand-written code.
Pros
Keeps exact data types: nvarchar, varbinary and decimals arrive unchanged
No delimiter or line-break problems in text
3–5× compression cuts upload to ~0.8–1.3 TB
Restartable per chunk
No licence; same code reused for delta
Cons
Framework must be built and tested (~2–3 weeks)
Slightly slower than bcp on very large tables
Needs local disk on the extract server
When it fits: the default for every table in this migration.
H2bcp export → delimited text → PUT → COPY INTO
Use selectively
Microsoft’s bulk export tool dumps a table or query to a flat file. Example: bcp "SELECT … WHERE OrderDate >= '2024-01-01'" queryout orders.csv -c -t "|"
Pros
Fastest raw export from SQL Server
Built in, nothing to install on Windows
Simple to script
Cons
Text only: no Parquet, no proper CSV quoting
nvarchar can be corrupted without careful encoding settings
varbinary comes out as hex; char keeps trailing spaces
Compression and file-format tuning are extra work
When it fits: very large tables with only numeric and date columns, where export speed matters most.
H3Built-in snapshot of a replication tool
Not recommended
Openflow, Fivetran/HVR and Qlik all start with a full snapshot before switching to change capture.
Pros
One tool for history and delta
Switches from snapshot to changes automatically
Cons
Reads the live source for a long time at 3–4 TB
Little control over chunks, windows and re-runs
Long snapshots fail and restart more often
Licensed tools may charge for the volume
When it fits: up to a few hundred GB. Not this project.
H4Azure Data Factory or SSIS
Not recommended
Pros
Familiar to SQL Server teams
Visual development
Cons
ADF needs Azure Blob Storage as a middle step: an extra cloud service to buy and secure
SSIS needs third-party components or slow row-by-row loads
Another platform to learn, with no gain over H1
When it fits: only if the client has already standardised on ADF and accepts Blob staging.
05
Delta sync options
After the first copy, Snowflake must receive every new row, update and delete. The right choice depends on three things: how fresh the data must be, what network access the client allows, and whether the client prefers to build or buy.
Key rule when history and delta use different tools: record the Change Tracking version before the bulk extract starts and begin the delta from there. A few rows may arrive twice; MERGE absorbs them. Nothing is missed.
ASnowflake Openflow · SQL Server connector
Alternative · keep open
Snowflake’s own integration service. The SQL Server connector reached general availability in August 2026 and supports snapshot, incremental sync through CDC or Change Tracking, and schema changes.
Pros
Snowflake-native, no third-party contract
Near real-time possible
Handles schema changes and deletes
Monitoring inside Snowflake
Cons
Needs a network path from Openflow to the on-prem server (VPN, private link or firewall change), often the slowest approval
Runs continuously, using credits even when quiet
Newer product; fewer reference projects
Region availability must be confirmed
When it fits: the business needs data within minutes and the network team approves the connection.
BChange Tracking + Python + Airflow → MERGE
Recommended
A scheduled Python job reads rows changed since the last recorded version, writes Parquet, uploads it and runs MERGE, including deletes. Airflow schedules, retries and alerts.
Pros
No licence cost
Same framework and team as the historical load
Outbound-only connection; no inbound firewall rules
Lightweight on the source; captures deletes
Full control of schedule and error handling
Builds Snowflake skills in the client team
Cons
Built and maintained by the team
Batch latency (every 15 minutes to daily), not real-time
Schema changes need a controlled process
Needs an on-prem server for Airflow
Key-person risk, reduced by runbooks and handover
When it fits: daily or hourly freshness is enough, which is the expected case.
CLicensed CDC tool · Fivetran/HVR, Qlik Replicate
Alternative · keep open
Pros
Fastest to set up; little code to maintain
Mature monitoring and schema-drift handling
Agent connects outbound only
Handles high volumes and near real-time
Cons
Recurring licence (Fivetran bills by monthly active rows)
Vendor lock-in
Extra security and procurement review
Less control over processing
When it fits: the client prefers buying to building, has budget, or expects many more sources soon.
DADF or SSIS incremental pipelines
Not recommended
Same Blob-staging and component problems as H4, and Change Tracking plus MERGE still has to be hand-built. Two platforms to run instead of one.
EWatermark only (“updated_at” column)
Not recommended
Misses hard deletes, so deleted rows stay in Snowflake. It also depends on every application updating the column correctly, which often fails silently. Use only as a fallback for a single table where Change Tracking is impossible, with regular full reconciliation.
FDaily full reload
Not recommended
Moves 3–4 TB every day: wasteful in bandwidth, source load and Snowflake cost. Acceptable only for small reference tables of a few GB.
One framework, one code base and one team for both phases.
No licence costs and no extra cloud services, which suits a first-time Snowflake buyer.
Outbound-only network design avoids the longest approval: inbound access to production databases.
Parquet removes the Unicode, binary and padding risks present in this source.
Change Tracking captures deletes with little load on SQL Server.
The client team learns standard Snowflake loading (stages, COPY INTO, MERGE) for future work.
Keep open
Option A, Openflow: if data is needed within minutes and the network team approves Snowflake reaching the server.
Option C, Fivetran/HVR or Qlik: if the client would rather buy a managed product and has the budget.
Switching later does not redo the migration: the historical load and the Snowflake design stay the same.
Avoid
ADF / SSIS: needs Blob staging or third-party components, with no benefit.
Tool snapshot for the history: too slow and hard to control at 3–4 TB.
Watermark-only delta: misses deletes and drifts silently.
Daily full reload: moves 3–4 TB every day.
07
Data type mapping
All ten source types map directly. Four need attention, marked in orange.
SQL Server
Snowflake
Note
bigint
NUMBER(19,0)
Direct
int
NUMBER(10,0)
Direct
decimal / numeric(p,s)
NUMBER(p,s)
Same maximum precision (38)
bit
BOOLEAN
1/0 become TRUE/FALSE
datetime
TIMESTAMP_NTZ(3)
No time zone, millisecond precision
varchar(n)
VARCHAR(n)
Direct
char(n) ⚠
VARCHAR(n)
SQL Server pads with trailing spaces. Trim on load or compare trimmed values.
nvarchar(n) ⚠
VARCHAR
Unicode. Parquet keeps it intact; plain bcp text can corrupt it.
varbinary(n) ⚠
BINARY
Check the largest varbinary(max) values against Snowflake’s BINARY size limit.
collation ⚠
—
SQL Server is usually case-insensitive; Snowflake compares case-sensitively by default. Affects queries, not the copy.
Part 3 · Design Snowflake08–11
08
Where hundreds of databases go
There are three common ways to arrange hundreds of source databases in Snowflake.
Approach
Verdict
Why
Same databases in Snowflake one Snowflake DB per SQL Server DB
Avoid
With three environments, hundreds of databases become 300+. Each needs grants, roles and monitoring, so access control becomes unmanageable. The only gain is familiarity.
One database per environment, one schema per source DB
Recommended
Hundreds of schemas in one database is normal for Snowflake. Grants, cloning and monitoring happen in one place.
Medallion layers
Use as the frame
The migrated copy is the Bronze layer. Silver and Gold are added later, when the client builds on top.
The resulting layout
PRD_RAW ← Bronze: exact copy of SQL Server (this project)
├─ SALESDB_DBO schema = source database + source schema
│ ├─ ORDERS
│ └─ CUSTOMERS
├─ SALESDB_SALES
├─ HRDB_DBO
└─ … one schema per source DB_SCHEMA
PRD_ADMIN ← framework: control tables, CT versions, audit, stages, landing
PRD_CURATED ← Silver: cleaned and conformed (later)
PRD_ANALYTICS ← Gold: business-ready marts (later)
Name schemas <DBNAME>_<SCHEMANAME>. Source databases often have several schemas (dbo, sales). Flattening them to one schema per database causes clashes when two tables share a name.
Keep table and column names exactly as in the source, in upper case, so validation and lineage stay simple.
Create only RAW and ADMIN now. Empty Silver and Gold layers only add overhead.
Optional domain split. If the databases fall into clear business areas or sensitivity levels, use a few domain databases such as PRD_RAW_FINANCE and PRD_RAW_HR. This simplifies access and cost tracking.
Keep landing tables out of RAW. Change batches land in ADMIN or a transient schema, so RAW stays a clean mirror.
09
Accounts and environments
A Snowflake account is a fully separate platform with its own users, roles, settings and network rules. The question is how many to have for DEV, UAT and PROD.
Option
Pros
Cons
One account DEV / UAT / PRD as databases
Simplest and cheapest to run. Copying between environments is instant and free. One place to manage.
Prod is separated only by roles; one bad grant or admin mistake can reach prod data. Network and security settings are shared.
Two accounts ★ NONPROD (dev + uat) · PROD
Prod is truly isolated: no developer logins, its own admins, network policy and settings. Matches what audits usually expect. Still easy to manage.
Copying prod data to non-prod needs replication instead of free cloning. Some objects are set up twice.
Three accounts
Full isolation
Extra overhead with little benefit over two for this project.
Recommendation: two accounts in one Snowflake organization
Extra accounts have no fee of their own; billing follows usage, and capacity can be shared across the organization. Splitting one account later means rebuilding roles, users, integrations and pipelines, which costs far more than starting with two.
PROD account
PRD_RAW · Bronze mirror
PRD_ADMIN · framework, landing, audit
PRD_CURATED · PRD_ANALYTICS · later
PRD_LOAD_WH · PRD_QUERY_WH · auto-suspend 60s
No developer logins · own admins and network policy
NONPROD account
DEV_RAW · DEV_ADMIN · build and unit test
UAT_RAW · UAT_ADMIN · client testing
DEV_WH · UAT_WH · X-Small
Data from non-prod SQL Servers, or masked / subset copies of prod
If the client insists on one account
Separate warehouses per environment.
No role spans prod and non-prod, apart from admins.
Keep the DEV_ / UAT_ / PRD_ database split so prod can move to its own account later.
10
Role-based access
Snowflake’s standard model has two layers: access roles hold permissions on data, functional roles describe jobs. People and services receive functional roles only.
Who
People from Entra ID / Okta (SSO + MFA, SCIM sync) Service users: TYPE = SERVICE, key-pair
PRD_RAW and its schemas and tables future grants · managed access
System roles are for administration only. ACCOUNTADMIN for 2–3 named people with MFA, never used day to day. SECURITYADMIN and USERADMIN manage roles and users; SYSADMIN or a dedicated owner role owns objects.
Database roles as access roles. They live inside the database, stay tidy with hundreds of schemas, and travel with clones and replication.
Grant at database level by default, at schema level only for sensitive areas. That keeps it to roughly 10–15 roles instead of more than a thousand.
Managed access schemas and future grants, so new tables get the right permissions automatically.
Joiners and leavers are handled by the identity provider through SCIM: add someone to an AD group and they get the matching role.
Everything scripted with Terraform or generated SQL. Manual grants across hundreds of schemas drift.
11
Data security
Who can connect
SSO + MFA for people, key-pair for services, network policies limited to client IP ranges, private connectivity if policy requires.
Encryption
Automatic: TLS in transit, AES-256 at rest. Files uploaded with PUT are encrypted. Customer-managed keys only if required (Business Critical edition).
Find sensitive data
Run Snowflake’s sensitive-data classification on RAW after the load and tag PII columns, for example PII = 'EMAIL'.
Mask columns
Tag-based masking policies: every column with a given tag is masked by role. Scales to hundreds of schemas without per-column work.
Filter rows
Row access policies where some users should see only their region or business unit.
Protect non-prod
Mask or subset prod data before it reaches DEV/UAT, or source non-prod from non-prod SQL Servers.
Audit
ACCESS_HISTORY and LOGIN_HISTORY show who read what and when. The Trust Center checks security posture.
Recovery
Time Travel (1–7 days on RAW) plus Fail-safe. No separate backup tool needed.
Part 4 · Run the project12–18
12
Network and bandwidth
All traffic for the recommended design starts inside the client network and goes out over HTTPS (port 443). No inbound access to SQL Server is needed. The SQL Server login used for extraction is read-only plus permission to read Change Tracking.
The client’s sustained upload speed decides how long the historical load takes. About 0.8–1.3 TB of compressed files must be uploaded:
Sustained upload
Per 1 TB
For 0.8–1.3 TB
100 Mbps
~23 hours
~1–1.5 days
500 Mbps
~5 hours
~4–6 hours
1 Gbps
~2.5 hours
~2–3 hours
Theoretical at full speed; real throughput is often 60–80% of this. A bandwidth test in Phase 0 confirms the plan.
13
Validation and reconciliation
Every load is checked automatically. Results go to the ADMIN database and a daily reconciliation report.
Level
Check
When
L1
Row counts per table, chunk and partition
After every historical chunk and delta run
L2
Sum of numeric columns; min/max of dates and keys; null counts
After each table load; daily during the parallel run
L3
Row-level sample, hashed the same way in Python on both sides
Per table after history; weekly during the parallel run
Sign-off: suggested acceptance is a 100% row-count match and zero aggregate differences for an agreed number of consecutive business days, for example 10.
14
Plan and timeline
0 · Assess and discover 1–2 weeks
Inventory of databases, schemas, tables, sizes and keys; change volumes; bandwidth test; Change Tracking readiness; confirm delta option, account model and domain grouping.
Metadata-driven DDL, extract, load, MERGE and validation; tested on a few representative databases.
3 · Historical load 1–2 weeks
Record CT version, chunked extract, upload, COPY INTO, reconcile, in waves of databases.
4 · Delta sync 1–2 weeks
Scheduled delta jobs, MERGE, monitoring and alerts.
5 · Parallel run and sign-off 2–4 weeks
Daily reconciliation, fixes, client acceptance. Classification and masking applied.
6 · Cutover future
When applications move to Snowflake, under a separate plan.
Indicative total: 8–14 weeks to sign-off, firmed up after Phase 0. The main drivers are the number of databases, upload bandwidth and access approvals.
15
Cost drivers
Item
Notes
Storage
Compressed size in Snowflake, usually well below the 3–4 TB source, plus Time Travel and Fail-safe.
Compute
Short bursts during the historical load; small scheduled runs for delta. Auto-suspend keeps idle cost near zero.
Second account
No separate fee; non-prod warehouses are small and suspended most of the time.
Openflow (A only)
Continuous runtime compute while the connector runs.
Licences (C only)
Recurring vendor fees, usually based on rows changed or capacity.
On-prem server (B)
One server or VM for extract jobs and Airflow; can reuse existing hardware.
Start on on-demand pricing, measure real usage during the parallel run, then decide on a capacity commitment.
16
Risks and mitigations
Risk
Impact
Mitigation
Low or unstable upload bandwidth
High
Measure in Phase 0; compress; upload off-hours; load big tables early
Change Tracking data expires if delta jobs stop too long
High
Retention longer than the worst outage (e.g. 7 days); failure alerts; documented re-snapshot
Extract slows the live source
Medium
Chunked reads, off-peak windows, throttled parallelism; use a replica if available
Tables without a primary key
Medium
Find in Phase 0; use a unique index, or full reload if small
Unicode, char padding or binary mismatches
Medium
Parquet extract, trimming rules, targeted tests
Source schema changes
Medium
Daily metadata comparison; agreed change process
Grants drift across hundreds of schemas
Medium
Database roles, future grants, all grants scripted
Can source databases be grouped by business area or sensitivity?
If yes, split RAW into domain databases
4
Where does DEV/UAT data come from? Must it be masked?
Non-prod sources, or masked prod copies
5
Identity provider for SSO and SCIM
Entra ID or Okta, mapped to functional roles
6
Compliance needs: PII, HIPAA, GDPR, data residency
Decides Enterprise vs Business Critical, and region
7
Primary keys on all tables; how deletes happen
Confirm in Phase 0
8
SQL Server version and DBA approval for Change Tracking
Needed for Option B
9
Sustained upload bandwidth and firewall rules
Bandwidth test in Phase 0
10
Build in-house or buy a managed tool
Build (B), keeping C open
11
A server or VM for extract jobs and Airflow
Reuse existing infrastructure
12
Acceptance criteria and length of parallel run
100% match for 10 business days
18
Draw.io diagrams
The complete diagram set from MSSQL_to_Snowflake_Migration_Flow.drawio. Pick a page below. On a phone, scroll sideways inside the diagram. Download the file to open and edit it in app.diagrams.net (File → Open from → Device) or draw.io Desktop.
Phases, on-prem → ingestion → Snowflake, and the validation strip.
Prepared by Jitendra Chauhan · +91 8368258062 · jitechau01@gmail.comDraft for discussion. Volumes, timelines and bandwidth figures are indicative until Phase 0 is complete.