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

# Microsoft SQL Server Source: High Availability and Failover

> How Artie replicates SQL Server in Always On availability groups: connecting through the listener, what carries over on failover, CDC jobs on every replica, log backups across replicas, and Artie's delivery guarantees.

This page covers running Artie against SQL Server in an Always On availability group (AG), and what happens to your pipeline when the AG fails over.

## What you'll need

* Artie connected through the **AG listener**, not an individual replica
* For [capture instances](https://artie.com/docs/sources/microsoft-sql-server/capture-instances.md): the CDC capture and cleanup jobs on every replica that can become primary
* For [transaction log backups](https://artie.com/docs/sources/microsoft-sql-server/transaction-log-backups.md): log backups from every replica written to one folder with one naming prefix
* CDC or change tracking retention longer than your longest expected outage

## What carries over on failover

CDC and change tracking are database settings. Their data lives in the database, so the AG copies it to every replica.

| | Copied to the new primary | Needs action |
| - | - | - |
| CDC enabled on the database and tables | Yes | None. **You don't need to re-enable CDC after a failover.** |
| Capture instances and change tables | Yes | None |
| LSNs, and the position Artie saved | Yes. The new primary continues the same log. | None |
| Change tracking settings and versions | Yes | None |
| CDC capture and cleanup jobs (SQL Server Agent) | **No.** Agent jobs are stored in `msdb`, which isn't in the AG unless you use a contained availability group (SQL Server 2022 and later). | Create them on every replica. See [Set up CDC jobs on every replica](#set-up-cdc-jobs-on-every-replica). |
| Logins | **No** | Create the `artie` login on every replica with the same SID. |

## What Artie does during a failover

1. The AG fails over, and the connection to the old primary drops.
2. The pipeline's current query fails and the reader process restarts.
3. Artie reconnects through the listener, which now points to the new primary.
4. Artie resumes from the last position it saved. Changes after that position are read from the new primary.

Artie saves its position only after the changes it read are written to its Kafka buffer. Changes Artie read but hadn't saved a position for are read again. The destination merges on the primary key, so the result is the same.

## Set up CDC jobs on every replica

SQL Server creates the CDC capture and cleanup jobs on the replica where you ran `sys.sp_cdc_enable_db`. After a failover, the new primary has no capture job unless you create one.

Without a capture job, `sys.fn_cdc_get_max_lsn()` stops moving. Artie sees no new changes and the pipeline keeps running without errors. No changes are lost while the job is missing, because SQL Server keeps them in the log. They're replicated once the job runs.

On each secondary replica, create the jobs once:

```sql theme={null}
USE [ANALYTICS];
EXEC sys.sp_cdc_add_job @job_type = N'capture';
EXEC sys.sp_cdc_add_job @job_type = N'cleanup', @retention = 10080; -- minutes, 7 days
GO
```

For how the jobs behave on secondary replicas, see [Microsoft's guide to CDC with availability groups](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability).

## Connecting

* Enter the AG listener's DNS name as the **Host**.
* To read from a readable secondary, check **Read-only application intent**. Artie connects with `ApplicationIntent=ReadOnly`, and the listener routes the connection to a readable secondary through [read-only routing](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server). Capture instances and change tracking only read from the database, so they work on a readable secondary.

## Synchronous and asynchronous replicas

A failover to a **synchronous-commit** replica doesn't lose committed transactions, so Artie replicates every committed change.

A **forced failover** to an **asynchronous-commit** replica can lose transactions that the old primary committed but hadn't sent yet. Those transactions don't exist on the new primary, so Artie can't replicate them. If Artie had already read them, they stay in the destination.

> [!WARNING]
>
> After a forced failover with possible data loss, [trigger a backfill](https://artie.com/docs/pipelines/backfill.md) of the replicated tables to bring the destination back in line with the new primary.

## Log backups in an availability group

With [transaction log backups](https://artie.com/docs/sources/microsoft-sql-server/transaction-log-backups.md), Artie reads files in name order from one folder. The log is the same on every replica, so Artie can read backups taken on any replica, as long as every replica:

* writes to the **same folder**, without per-server subfolders
* uses the **same file name prefix**, for example the AG name rather than the server name

With Ola Hallengren's maintenance solution, set `@AvailabilityGroupDirectoryStructure = NULL` and keep the default `@AvailabilityGroupFileName`, which starts with the cluster and AG name.

Set the AG's backup preference so that one replica takes log backups at a time.

> [!WARNING]
>
> If replicas write to different folders or with different prefixes, Artie can skip backup files after a failover, and their changes aren't replicated.

## Delivery guarantees

These apply to all three replication methods.

* **At least once.** Artie saves its position only after the changes it read are written to its Kafka buffer. A restart, crash or failover replays from the saved position, so changes can arrive twice but aren't skipped. The destination merges on the primary key.
* **Committed changes only.** Capture instances and transaction log backups publish committed transactions in commit order. Transaction log backups drop rolled-back transactions.
* **Retention.** If the pipeline is stopped for longer than the CDC or change tracking retention period, SQL Server deletes changes Artie hadn't read. Artie continues from the oldest change still available and logs a warning. Backfill the affected tables.

## Checklist

* [ ] Artie's **Host** is the AG listener
* [ ] The `artie` login exists on every replica, with the same SID
* [ ] CDC capture and cleanup jobs exist on every replica (capture instances)
* [ ] CDC or change tracking retention is longer than your longest expected outage
* [ ] Replicas Artie can fail over to use synchronous commit, or you have a plan to backfill after a forced failover
* [ ] Log backups from every replica go to one folder with one prefix (transaction log backups)


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