> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server destination for ELT & CDC

> Set up Microsoft SQL Server or Azure SQL as a destination in Integrate.io ELT & CDC, including required privileges and connection settings.

|                           |                                                             |
| :------------------------ | :---------------------------------------------------------- |
| **Description**           | Load replicated data into Microsoft SQL Server or Azure SQL |
| **Type**                  | RDBMS Destination                                           |
| **Supported Replication** | Initial Sync, Continuous Sync                               |
| **Authentication Type**   | Password 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](/cdc/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:

```sql theme={null}
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:

```sql theme={null}
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

| Privilege                                                                 | Why 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

| Setting  | Notes                                                                                                    |
| :------- | :------------------------------------------------------------------------------------------------------- |
| Host     | Server hostname or IP.                                                                                   |
| Port     | TDS port, usually `1433`.                                                                                |
| Database | Destination database where final tables are created.                                                     |
| Schema   | Destination schema. Defaults to `dbo`.                                                                   |
| Encrypt  | TDS encryption. Defaults to `true`. Disable only for a local SQL Server container without a certificate. |

## Features

| Feature                | Supported | Notes                                                                   |
| :--------------------- | :-------- | :---------------------------------------------------------------------- |
| Full (Historical) sync | Yes       |                                                                         |
| Incremental sync       | Yes       |                                                                         |
| UPSERT                 | Yes       | Last write wins, keyed by primary key.                                  |
| Replicate DELETE       | Yes       | Source deletes propagate through the staging merge.                     |
| Append only mode       | Yes       | Same-batch replays are de-duplicated.                                   |
| Schema evolution       | Yes       | New source columns trigger `ALTER TABLE ADD COLUMN` on the destination. |
| SSH tunnel             | Yes       | [SSH Tunnel Guide](/cdc/ssh-tunnel)                                     |

## Frequently Asked Questions (FAQs)

<AccordionGroup>
  <Accordion title="Which SQL Server versions are supported?">
    SQL Server 2012 or later, including Azure SQL. All DDL is version-agnostic and runs without a version floor.
  </Accordion>

  <Accordion title="Do I need to create the destination database first?">
    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.
  </Accordion>

  <Accordion title="What schema do destination tables land in?">
    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.
  </Accordion>

  <Accordion title="Does the sink leave staging tables behind?">
    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.
  </Accordion>

  <Accordion title="Does the sink need BULK INSERT or ADMINISTER BULK OPERATIONS?">
    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.
  </Accordion>

  <Accordion title="How are high-precision numerics handled?">
    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.
  </Accordion>
</AccordionGroup>

## Related

<CardGroup cols={2}>
  <Card title="Microsoft SQL Server Source" icon="arrow-right" href="/cdc/microsoft-sql-server" horizontal />

  <Card title="SSH Tunnel" icon="arrow-right" href="/cdc/ssh-tunnel" horizontal />

  <Card title="IP Allowlist" icon="arrow-right" href="/cdc/ip-list" horizontal />

  <Card title="Initial Sync Process" icon="arrow-right" href="/cdc/initial-sync-process" horizontal />
</CardGroup>