Search
K
Links

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.