> ## 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.

# Microsoft SQL Server source for ELT & CDC

> How to set up and configure Microsoft SQL Server as a CDC source in Integrate.io ELT & CDC, including supported providers and prerequisites.

|                           |                               |
| :------------------------ | :---------------------------- |
| **Description**           | SQL Server                    |
| **Type**                  | CDC/Binlog Replication        |
| **Supported Replication** | Initial Sync  Continuous Sync |
| **Authentication Type**   | Password Authentication       |

## Setting up Microsoft SQL Server CDC for ELT & CDC

### Overview

ELT & CDC uses change data capture (CDC) for Microsoft SQL Server.

### Requirements

* **Microsoft SQL Server 2008 (10.0)** version or above.
* Sysadmin permissions are required for SQL Server or Azure SQL Managed.
* The db\_owner role is required to enable change data capture for Azure SQL Database.
* CDC must be enabled for the database.
* CDC must be enabled for the needed tables.

### Supported providers

* RDS SQL Server
* Azure SQL Database
* Others (self hosted)

### Setting up Microsoft SQL Server for Integrate.io

#### Create role for sync

Create a sync user for ELT & CDC by executing

```bash theme={null}
CREATE LOGIN integrateio WITH PASSWORD = '<your_password>';
```

#### Grant the privileges for the database and schema by running the following queries

```sql theme={null}
USE <enter database>
GO
CREATE USER integrateio FOR LOGIN integrateio WITH DEFAULT_SCHEMA=<enter schema>
GO
CREATE SCHEMA <enter schema> AUTHORIZATION integrateio
GO
EXEC sp_addrolemember 'db_ddladmin', 'integrateio';
EXEC sp_addrolemember 'db_datareader', 'integrateio';
EXEC sp_addrolemember 'db_datawriter', 'integrateio';
EXEC sp_addrolemember 'db_securityadmin', 'integrateio';
EXEC sp_addsrvrolemember 'integrateio', 'bulkadmin';
GO
```

#### Enable CDC for the database

<Steps>
  <Step>
    Go to AWS RDS dashboard
  </Step>

  <Step>
    Enable change data capture by running

    ```sql theme={null}
    EXEC sys.rds_cdc_enable_db
    ```
  </Step>
</Steps>

#### Enable CDC for the needed tables

<Steps>
  <Step>
    Go to AWS RDS/Azure dashboard (you may omit this step for others/self hosted)
  </Step>

  <Step>
    Enable change data capture by running

    ```sql theme={null}
    EXEC sys.sp_cdc_enable_table  
    @source_schema = N'MySchema',
    @source_name   = N'MyTable',
    @role_name     = N'flydata',
    @supports_net_changes = 1
    GO
    ```

    or enable change data capture for all tables by running

    ```sql expandable theme={null}
    DECLARE @Schema AS VARCHAR(300)
    DECLARE @Table AS VARCHAR(300)
    DECLARE @cdc_Role AS VARCHAR(300)
    SET @cdc_Role = 'flydata';
    DECLARE cdc_cursor CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
    OPEN cdc_cursor
    FETCH NEXT FROM cdc_cursor INTO @Schema, @Table
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXECUTE sys.sp_cdc_enable_table
    @source_schema = @Schema,
    @source_name = @Table,
    @role_name = @cdc_Role;
    PRINT 'CDC enabled for ' + @Schema + '.' +@Table + ' for ' + @cdc_Role;
    FETCH NEXT FROM cdc_cursor INTO @Schema, @Table
    END
    CLOSE cdc_cursor
    DEALLOCATE cdc_cursor
    ```
  </Step>
</Steps>

## Related

<CardGroup cols={2}>
  <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="Hooks" icon="arrow-right" href="/cdc/hooks" horizontal />
</CardGroup>