Active transaction log via SQL access

Overview

With this method, Artie will read from the active transaction log via SQL access. This method is only available to SQL Server on VM and Azure managed instances.

Requirements

Database recovery model must be set to FULL or BULK_LOGGED
Our service account must have sysadmin permission
Each replicated table must have supplemental logging enabled
Each replicated table must have a primary key
Azure blob storage (as a storage location if using Azure managed instance)

Supplemental logging

SQL Server requires supplemental logging to capture complete row changes for replication:

Default behavior: Without supplemental logging, SQL Server only emits values for modified columns, not the entire row
Required for Artie: Supplemental logging must be enabled to capture complete row changes during replication
Implementation options:
- Enable CDC on each table you want to replicate (then disable the capture process, so no changes accumulate in the transaction log)
- Create a publication with articles for each table you want to replicate

Configuring this in the Artie dashboard

To configure Artie to use this method, change the replication method to “Transaction logs via SQL access” under the advanced settings in the source tab.

Artie dashboard - SQL Server source - Transaction logs via SQL access

Enabling supplemental logging

We recommend enabling supplemental logging by enabling CDC and then disabling the capture jobs so no changes accumulate in the cdc schema.

-- Enable CDC on the database
USE [DATABASE_NAME];
GO

EXEC sys.sp_cdc_enable_db;
GO

-- Then enable CDC for each table:
EXEC sys.sp_cdc_enable_table @source_schema = N'MySchema', @source_name = N'MyTable', @role_name = null;
GO

-- Make sure to disable capture to avoid accumulating changes in the "cdc" schema.
EXEC sp_cdc_drop_job @job_type = N'capture'
GO

Alternative: Make each table a replication article

-- Enable publication for the database
EXEC sp_replicationdboption @dbname = N'MyDatabase', @optname = N'publish', @value = N'true'
GO

-- Create a publication
EXEC sp_addpublication @publication = N'MyPublication', @status = N'active';
GO

-- Create a replication article for each table
EXEC sp_addarticle @publication = 'MyPublication', @article = 'MyArticle', @source_object = 'MyTable', @type = 'logbased'
GO

Home

Concepts

Connectors

Tables

Monitoring

Artie Dashboard

Legal

Active transaction log via SQL access

Overview

Requirements

Supplemental logging

Configuring this in the Artie dashboard

Home

Concepts

Connectors

Tables

Monitoring

Artie Dashboard

Legal

​Overview

​Requirements

​Supplemental logging

​Configuring this in the Artie dashboard

Overview

Requirements

Supplemental logging

Configuring this in the Artie dashboard