# Microsoft SQL Server

## Supported Ingestion Methods

* Full
* Incremental
  * It is highly recommended that a non-clustered, non-unique index on the column used for incremental queries
* Change Tracking (CT)
  * More information about and instructions to enable Change Tracking can be found [here](https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15).
* Change Data Capture (CDC)
  * More information about and instructions to enable Change Data Capture can be found [here](https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15).
  * Schema changes require a bit more work to rebuild the CDC functions. Info [here](https://www.mssqltips.com/sqlservertip/4096/understanding-how-dml-and-ddl-changes-impact-change-data-capture-in-sql-server/).

For more information on ingestion methods, [click here](https://docs.verbdata.com/data-sources/ingestion-methods).

## User & Permission Configuration

It is highly recommended that you create a new user for Verb to access your data. That user should, in most cases, only have read-only access to your data. It is further recommended that you limit the access only to tables necessary for reporting.

### Create User

```
USE [MyDB];
CREATE LOGIN verbdata WITH PASSWORD = 'a long and secure password';
CREATE USER verbdata FOR LOGIN verbdata;
```

### Configure Permissions

For [Full](https://docs.verbdata.com/ingestion-methods#full-data-sync) or [Incremental](https://docs.verbdata.com/ingestion-methods#incremental-column-data-sync) ingestion methods, only the SELECT permission is required.

```
GRANT SELECT ON DATABASE::MyDB TO verbdata;
```

For [Change Tracking](https://docs.verbdata.com/ingestion-methods#change-tracking-ct) and [Change Data Capture](https://docs.verbdata.com/ingestion-methods#change-data-capture-cdc), the EXECUTE permission is also required.

```
GRANT EXECUTE ON DATABASE::MyDB TO verbdata;
```

For more information on SQL Server permissions, click [here](https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver15).

## Enable Change Tracking (CT)

First, enable Change Tracking at the database level:

```
ALTER DATABASE [MyDB] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
```

Next, enable Change Tracking and grant VIEW CHANGE TRACKING permission on **each** table to be ingested by Verb:

```
ALTER TABLE [MyDB].[dbo].[MyTable] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
GRANT VIEW CHANGE TRACKING ON [dbo].[MyTable] TO verbdata;
```

For more information on SQL Server Change Tracking, click [here](https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-ver15).

## Enable Change Data Capture (CDC)

First, enable Change Data Capture at the database level:

```
USE [MyDB]
GO
EXEC sys.sp_cdc_enable_db
GO
```

Next, enable Change Data Capture for **each** table to be ingested by Verb:

```
USE [MyDB]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = NULL,
@supports_net_changes = 1
GO
```

For more information on SQL Server Change Data Tracking, click [here](https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver15).