> ## Documentation Index
> Fetch the complete documentation index at: https://artie.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Transaction log backups

> Replicate Microsoft SQL Server by reading its transaction log backups (.trn files), with supplemental logging so updates are read from the log instead of the database.

## 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

1. Database recovery model must be set to `FULL` or `BULK_LOGGED`
2. Transaction log backups taken on a schedule, uncompressed, to a location Artie can read
3. Each replicated table must have a primary key
4. 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:

<Tabs>
  <Tab title="CDC (recommended)">
    ```sql theme={null}
    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`.
  </Tab>

  <Tab title="Transactional publication">
    Use this if you cannot enable CDC. It requires a distributor and is not available on Amazon RDS.

    ```sql theme={null}
    -- 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.
  </Tab>
</Tabs>

To check, `is_replicated` should be `1` for each table:

```sql theme={null}
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:

```sql theme={null}
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
```

<Warning>
  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.
</Warning>

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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.