Migrating Data from SQL Server¶

This page covers SQL Server-specific setup for Data migration. For workflow and Worker field definitions, see Data migration configuration reference.

Prerequisites¶

Microsoft ODBC Driver for SQL Server on each Worker host (the Worker auto-detects a suitable driver when none is set). Pin a driver with odbc_driver in TOML.
bcp utility (for faster extraction on custom Worker hosts): On Snowpark Container Services Workers, the unified image installs bcp automatically (via mssql-tools18) and sets use_bcp = true by default. On custom Worker hosts, install the SQL Server bcp bulk copy utility and set use_bcp = true in Worker TOML. See Download and install the bcp utility (https://learn.microsoft.com/en-us/sql/tools/bcp/bcp-download-install) and bcp utility (https://learn.microsoft.com/en-us/sql/tools/bcp/bcp-utility).
Windows integrated security (optional): set use_windows_auth = true on Windows Workers.

Connectivity and extraction¶

SQL Server uses the default regular extraction strategy (not a separate BCP strategy). By default the Worker pulls partitioned result sets over ODBC and writes Parquet to the internal migration stage. Set use_bcp = true in Worker TOML to use the SQL Server bcp bulk copy utility instead: bcp exports CSV locally on the Worker, then the Worker PUTs those files to the same internal stage. BCP is typically faster than ODBC for large tables and does not require an external stage.

On SPCS Workers, use_bcp = true is the default and bcp is installed at container start. On custom Worker hosts, install bcp yourself and opt in with use_bcp = true; when bcp is unavailable, the Worker falls back to ODBC.

SQL authentication¶

[connections.source.sqlserver]
username = "username"
password = "password"
database = "database_name"
host = "127.0.0.1"
port = 1433

Explicit driver and encryption¶

[connections.source.sqlserver]
odbc_driver = "ODBC Driver 17 for SQL Server"
username = "sa"
password = "mypassword"
database = "mydb"
host = "my-server.example.com"
port = 1433
encrypt = true
trust_server_certificate = false

Optional encrypt and trust_server_certificate follow ODBC Driver 17 vs 18 defaults. ODBC Driver 18 enables encryption by default.

Workflow example:

tables:
  - source:
      databaseName: MY_DB
      schemaName: dbo
      tableName: orders
    target:
      databaseName: TARGET_DB
      schemaName: dbo
      tableName: orders
    columnNamesToPartitionBy:
      - order_id

Tune columnNamesToPartitionBy and partitionSize for large tables.

Data type mappings¶

SQL Server type	Snowflake target type	Supported for migration	Notes
BIT, TINYINT, SMALLINT, INT, BIGINT	NUMBER	Yes
DECIMAL(p,s), NUMERIC(p,s)	NUMBER	Yes	Precision/scale expanded: NUMBER(p+2, s+4)
MONEY, SMALLMONEY	NUMBER	Yes
FLOAT, REAL	FLOAT	Yes
DATE	DATE	Yes
TIME(n)	TIME	Yes
DATETIME, DATETIME2(n), SMALLDATETIME	TIMESTAMP_NTZ	Yes	NULL `DATETIME` values may appear as `1970-01-01 00:00:00` on the target when BCP extraction is used
DATETIMEOFFSET(n)	TIMESTAMP_TZ	Yes
CHAR(n), VARCHAR(n), NCHAR(n), NVARCHAR(n)	VARCHAR	Yes
BINARY(n), VARBINARY(n)	BINARY	Yes
UNIQUEIDENTIFIER	VARCHAR	Yes	Stored as an uppercase UUID string
SYSNAME	VARCHAR	Yes
TEXT, NTEXT	VARCHAR	Yes
IMAGE		No
XML, SQL_VARIANT	VARIANT	Yes
HIERARCHYID	VARCHAR(4000)	Yes	Stored as its hierarchy path string
ROWVERSION, TIMESTAMP	BINARY(8)	Yes	SQL Server TIMESTAMP is a synonym for ROWVERSION, not a datetime
GEOGRAPHY	GEOGRAPHY	Yes	Extracted as Well-Known Text
GEOMETRY	GEOMETRY	Yes	Extracted as Well-Known Text
VECTOR	VECTOR(element_type, n)	Yes

Platform-specific considerations¶

BCP for throughput: On SPCS Workers, BCP is enabled by default. On custom Worker hosts, set use_bcp = true when bcp is installed. BCP applies to migration only; validation always reads the source over ODBC.

Prompt:
```
Set up SQL Server data migration for my project, including the Worker connection and BCP extraction
```
Partitioning: Tune columnNamesToPartitionBy and partitionSize for large or uneven tables.

Prompt:
```
Partition the ORDERS table by ORDER_ID for parallel extraction
```
Encryption: Set encrypt and trust_server_certificate explicitly in lab or hardened environments.
Anti-locking: Hints are off by default. On busy source tables, set queryModifiers.objectModifier to " WITH (NOLOCK)" to avoid blocking on source locks, at the cost of dirty reads. See Anti-locking and query modifiers.