Transaction log backups

Overview

With this method, Artie reads changes from your transaction log backups (.trn files) as they land in a shared location, instead of from the live database.

Requirements

Database recovery model must be set to FULL or BULK_LOGGED
Transaction log backups taken on a schedule, uncompressed, to a location Artie can read
Each replicated table must have a primary key
Each replicated table should have supplemental logging enabled (see below)

Supplemental logging

By default, SQL Server logs only the bytes an UPDATE changed. For those tables, Artie looks the row up in the database when it reads the backup, so the message carries the row as it is at that point rather than as of the update. An update of a row deleted since is then lost. With supplemental logging, SQL Server logs the whole row for every update, and Artie reads updates straight from the backup. Enable it on each table in one of two ways:

CDC (recommended)
Transactional publication

USE [DATABASE_NAME];
GO

EXEC sys.sp_cdc_enable_db;
GO

-- For each table you want to replicate:
EXEC sys.sp_cdc_enable_table @source_schema = N'MySchema', @source_name = N'MyTable', @role_name = NULL;
GO

-- Artie does not read the cdc change tables, so stop SQL Server from filling them.
EXEC sys.sp_cdc_drop_job @job_type = N'capture';
GO

On Amazon RDS, enable CDC on the database with EXEC msdb.dbo.rds_cdc_enable_db 'DATABASE_NAME'; instead of sys.sp_cdc_enable_db.

Use this if you cannot enable CDC. It requires a distributor and is not available on Amazon RDS.

-- Once per server, if no distributor is configured:
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = N'';
EXEC sp_adddistributiondb @database = N'distribution';
EXEC sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = N'distribution';

EXEC sp_replicationdboption @dbname = N'DATABASE_NAME', @optname = N'publish', @value = N'true';
GO

USE [DATABASE_NAME];
GO

EXEC sp_addpublication @publication = N'artie', @status = N'active', @repl_freq = N'continuous';

-- For each table you want to replicate:
EXEC sp_addarticle @publication = N'artie', @article = N'MyTable', @source_owner = N'MySchema', @source_object = N'MyTable', @type = N'logbased';

-- SQL Server only logs whole rows once the publication has a subscription.
EXEC sp_addsubscription @publication = N'artie', @subscriber = @@SERVERNAME, @destination_db = N'SUBSCRIBER_DATABASE',
    @subscription_type = N'push', @sync_type = N'replication support only', @article = N'all';
GO

The subscriber database must already exist and cannot be master. No agents need to run.

To check, is_replicated should be 1 for each table:

SELECT s.name AS schema_name, t.name AS table_name, t.is_replicated
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id;

Artie lists the replicated tables without supplemental logging when it starts.

Letting the log truncate

Both options mark every transaction on these tables for replication, and SQL Server keeps it in the log until something marks it as distributed. Without the CDC capture job or a Log Reader Agent, log_reuse_wait_desc in sys.databases stays at REPLICATION and the log keeps growing, even with regular log backups. Since Artie reads your log backups, it is safe to mark everything as distributed on a schedule; each log backup still contains every change. Schedule this with SQL Server Agent:

USE msdb;
GO

EXEC sp_add_job @job_name = N'artie_mark_distributed';
EXEC sp_add_jobstep @job_name = N'artie_mark_distributed', @step_name = N'sp_repldone', @database_name = N'DATABASE_NAME',
    @command = N'EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1;';
EXEC sp_add_jobschedule @job_name = N'artie_mark_distributed', @name = N'every 15 minutes',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15;
EXEC sp_add_jobserver @job_name = N'artie_mark_distributed';
GO

Only run sp_repldone if nothing else reads these transactions: no CDC capture job, and no Log Reader Agent for another publication on the database.

Artie warns when a database’s log has been waiting on REPLICATION for over an hour.

Things to know

TRUNCATE TABLE is not allowed on tables tracked by CDC or published for replication.
Columns stored off-row (such as large VARCHAR(MAX) values) are not in the log with the rest of the row, so Artie still reads them from the database. If the row has been deleted by then, those columns keep their current values in the destination.

Getting Started

Pipelines

Connectors

Monitoring

Artie Dashboard

Transaction log backups

Overview

Requirements

Supplemental logging

Letting the log truncate

Things to know

​Overview

​Requirements

​Supplemental logging

​Letting the log truncate

​Things to know

Overview

Requirements

Supplemental logging

Letting the log truncate

Things to know