> For the complete documentation index, see [llms.txt](https://docs.erathos.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.erathos.com/connectors/databases/sql-server.md).

# SQL Server

Find out how to connect SQL Server to your data warehouse effortlessly.

In this page you will learn how to connect **SQL Server** to **Erathos** to move your data seamlessly.

The available connections between databases and the Erathos platform are through **Open Connection**, **Static IP** and **SSH tunnel**.

{% hint style="info" %}
If your database is hosted on Azure, follow the **Firewall** and **Credentials** steps in [Azure SQL Server](/destinations/what-is-a-destination/azure-sql-server.md) to allow Erathos to access it and to locate your connection details.
{% endhint %}

## Connecting SQL Server:

1. Log in to your Erathos account
2. Select SQL Server as connector
3. Name your connection
4. Fill in the form with the required information: db\_host, db\_port, db\_name, db\_username and db\_password

On the **Advanced Connection Settings**, you can optionally activate connecting through a SSH tunnel by filling in the fields ssh\_host, ssh\_keepalive\_interval, ssh\_password, ssh\_port, and ssh\_user.

When using CDC, the following values may be used in cdc\_snapshot\_mode:

* *initial*: performs an initial snapshot, after it completes, streaming following changes from the CDC change tables.
* *no\_data*: does not perform any snapshot, only streams changes that are currently available in the CDC change tables.

Don't forget to click on **Save and close** to advance to the next steps of the integration.

With this connection created you can seemlessly move your SQL Server data to your BigQuery, Redshift or Postgres data-warehouse.

### CDC setup

In order to enable CDC in your SQL Server connection you need to confirm the following settings in your database.

CDC must be enabled on the database. Enabling it requires a member of the `sysadmin` role.

```sql
-- Check whether CDC is enabled on the database
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'your_database';

-- If is_cdc_enabled is 0, enable it with:
USE your_database;
EXEC sys.sp_cdc_enable_db;
```

{% hint style="info" %}
On AWS RDS, use `EXEC msdb.dbo.rds_cdc_enable_db 'your_database';` instead of `sys.sp_cdc_enable_db`.
{% endhint %}

The SQL Server Agent must be running, since it runs the jobs that copy changes from the transaction log into the CDC change tables.

```sql
-- Should return "Running."
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLSERVERAGENT';
```

{% hint style="info" %}
On Azure SQL Database there is no SQL Server Agent: CDC changes are captured by a built-in scheduler, and `sys.sp_cdc_enable_db` requires a member of the `db_owner` role instead of `sysadmin`.
{% endhint %}

CDC must also be enabled on every table you want to stream. Tables without CDC enabled will not produce any change events.

```sql
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'your_table_name',
    @role_name     = NULL,
    @supports_net_changes = 0;

-- List the tables that currently have CDC enabled
EXEC sys.sp_cdc_help_change_data_capture;
```

The user used for the connection must be able to read the captured tables and the `cdc` schema. If you set a `@role_name` when enabling CDC on a table, the user must also be a member of that role.

```sql
USE your_database;
GRANT SELECT ON SCHEMA::dbo TO your_existing_user;
GRANT SELECT ON SCHEMA::cdc TO your_existing_user;

-- Only when a @role_name was set on sys.sp_cdc_enable_table
ALTER ROLE your_cdc_role ADD MEMBER your_existing_user;
```

{% hint style="danger" %}
Important: SQL Server's CDC cleanup job removes changes older than its retention period (3 days by default). If changes that CDC still needs to read get purged before it does so, the connection will need to perform a fresh initial snapshot to recover. The retention can be increased (in minutes) with:

`EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;`
{% endhint %}

#### Schema changes

SQL Server CDC does not pick up columns added to a table after CDC was enabled on it. After altering a captured table, create a new capture instance so the new columns are streamed, then disable the old one once the connection has moved past it.

```sql
-- Create a new capture instance with the current table structure
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'your_table_name',
    @role_name     = NULL,
    @supports_net_changes = 0,
    @capture_instance = N'dbo_your_table_name_v2';

-- Disable the old capture instance
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'your_table_name',
    @capture_instance = N'dbo_your_table_name';
```