Skip to main content
DescriptionLoad replicated data into Microsoft SQL Server or Azure SQL
TypeRDBMS Destination
Supported ReplicationInitial Sync, Continuous Sync
Authentication TypePassword Authentication

​
Overview

Integrate.io ELT & CDC syncs data to SQL Server using a two-stage bulk-load pattern. Batches are buffered as gzipped JSONL files in Integrate.io’s S3, streamed into a staging table over SQL Server’s native TDS bulk-load protocol, and merged into the destination tables using inserts, updates, and deletes. Staging uses a global ##_iio_stg_<table> temp table on a pinned connection per batch, dropped at the end of each merge. Destination tables and schemas are created and evolved automatically as the source schema changes.

​
Requirements

  • SQL Server 2012 or later (any edition), or Azure SQL. No minimum version is enforced.
  • A login and database user with privileges to create and modify tables in the destination schema (covered in the steps below).
  • Network access from Integrate.io’s egress IPs, or an SSH tunnel.

​
Create sync user

Create the sync login and a corresponding database user. Run the following as the Azure SQL admin or a sysadmin login: 
CREATE LOGIN [integrateio] WITH PASSWORD = '<your_password>';
USE [<database_name>];
CREATE USER [integrateio] FOR LOGIN [integrateio];

​
Grant database privileges

Grant the sync user privileges on the destination database. The sink creates and alters destination tables, stages each batch in a global ##_iio_stg_ temp table, and merges with inserts, updates, and deletes: 
USE [<database_name>];
ALTER ROLE db_ddladmin ADD MEMBER [integrateio];
ALTER ROLE db_datareader ADD MEMBER [integrateio];
ALTER ROLE db_datawriter ADD MEMBER [integrateio];
To scope privileges more tightly, grant CREATE TABLE plus ALTER, SELECT, INSERT, UPDATE, and DELETE on the destination schema instead of using the fixed database roles.

​
Required privileges

PrivilegeWhy it’s needed
SELECT, INSERT, UPDATE, DELETE (db_datareader, db_datawriter)Read and merge replicated rows into destination tables.
CREATE TABLE, ALTER (db_ddladmin)Create and evolve destination tables and staging tables as the source schema changes.
Creating global temp tables in tempdb is allowed to all logins by default, so no BULK INSERT or ADMINISTER BULK OPERATIONS grant is required. The sink uses the client-side TDS bulk-load protocol, not server-side BULK INSERT.

​
Connection settings

SettingNotes
HostServer hostname or IP.
PortTDS port, usually 1433.
DatabaseDestination database where final tables are created.
SchemaDestination schema. Defaults to dbo.
EncryptTDS encryption. Defaults to true. Disable only for a local SQL Server container without a certificate.

​
Features

FeatureSupportedNotes
Full (Historical) syncYes
Incremental syncYes
UPSERTYesLast write wins, keyed by primary key.
Replicate DELETEYesSource deletes propagate through the staging merge.
Append only modeYesSame-batch replays are de-duplicated.
Schema evolutionYesNew source columns trigger ALTER TABLE ADD COLUMN on the destination.
SSH tunnelYesSSH Tunnel Guide

​
Frequently Asked Questions (FAQs)

SQL Server 2012 or later, including Azure SQL. All DDL is version-agnostic and runs without a version floor.
Yes. The sync user can create and alter tables and schemas inside an existing database, but it does not create the database itself. Create the database before saving the connection.
The schema configured on the connection. If you leave it blank, tables land in dbo. Missing schemas are created automatically using the db_ddladmin role.
No. Staging uses a global ##_iio_stg_<table> temp table on a pinned connection per batch and is dropped at the end of each merge.
No. Bulk loading runs over the client-side TDS protocol, not server-side BULK INSERT. The fixed database roles in the grant step are enough.
Decimals wider than about 16 significant digits, such as MySQL unsigned BIGINT, are staged as text and cast back to DECIMAL in the merge to preserve the exact value.

Microsoft SQL Server Source

SSH Tunnel

IP Allowlist

Initial Sync Process

Last modified on June 12, 2026