Skip to main content
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: the CDC capture and cleanup jobs on every replica that can become primary
  • For transaction log backups: 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.

​
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:
For how the jobs behave on secondary replicas, see Microsoft’s guide to CDC with availability groups.

​
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. 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.
After a forced failover with possible data loss, trigger a backfill 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, 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.
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)