# 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](/data-sources/ingestion-methods.md).

## 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](/data-sources/ingestion-methods.md#full-data-sync) or [Incremental](/data-sources/ingestion-methods.md#incremental-column-data-sync) ingestion methods, only the SELECT permission is required.

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

For [Change Tracking](/data-sources/ingestion-methods.md#change-tracking-ct) and [Change Data Capture](/data-sources/ingestion-methods.md#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).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.verbdata.com/data-sources/database-engines/microsoft-sql-server.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.