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.

• Change Data Capture (CDC)

  • More information about and instructions to enable Change Data Capture can be found here.

  • Schema changes require a bit more work to rebuild the CDC functions. Info here.

For more information on ingestion methods, click here.

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 or Incremental ingestion methods, only the SELECT permission is required.

GRANT SELECT ON DATABASE::MyDB TO verbdata;

For Change Tracking and Change Data Capture, the EXECUTE permission is also required.

GRANT EXECUTE ON DATABASE::MyDB TO verbdata;

For more information on SQL Server permissions, click here.

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.

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.