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 (optional, for faster extraction): If you set use_bcp = true in Worker TOML, install the SQL Server bcp bulk copy utility on each host that runs a Worker. The Worker invokes bcp locally to export table data. See Download and install the bcp utility and bcp utility.
Windows integrated security (optional): set use_windows_auth = true on Windows Workers.

Connectivity and extraction¶

SQL Server uses regular extraction (partitioned SQL through the Worker to Parquet, then the internal migration stage). Set use_bcp = true in Worker TOML to use the SQL Server bcp utility for bulk export instead of ODBC result sets. bcp must be installed on the Worker host; see Download and install the bcp utility.

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
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	BINARY	Yes
XML, SQL_VARIANT	VARIANT	Yes
HIERARCHYID	VARCHAR	Yes	Stored as its hierarchy path string
ROWVERSION, TIMESTAMP	BINARY	Yes	SQL Server TIMESTAMP is a synonym for ROWVERSION, not a datetime
GEOGRAPHY, GEOMETRY	GEOGRAPHY	Yes

Platform-specific suggestions¶

BCP for throughput: Set use_bcp = true on large tables when bcp is installed on every Worker host. Without bcp, the Worker falls back to ODBC 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.