Microsoft SQL Server
- Full
- Incremental
- It is highly recommended that a non-clustered, non-unique index on the column used for incremental queries
- Change Tracking (CT)
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.
USE [MyDB];
CREATE LOGIN verbdata WITH PASSWORD = 'a long and secure password';
CREATE USER verbdata FOR LOGIN verbdata;
GRANT SELECT ON DATABASE::MyDB TO verbdata;
GRANT EXECUTE ON DATABASE::MyDB TO verbdata;
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;
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
Last modified 2yr ago