Microsoft SQL Server
Microsoft SQL Server source: choosing a replication method
Compare Artie's three ways to replicate Microsoft SQL Server (CDC capture instances, change tracking, and transaction log backups) by what they capture, latency, and load on the database.
Artie reads changes from SQL Server in one of three ways. Pick one per pipeline in Source > Replication method.
Reads SQL Server CDC change tables. The default method.
Reads SQL Server change tracking, then the current row.
Reads your .trn log backup files instead of the database.
Compare the methods
| Capture instances | Change tracking | Transaction log backups | |
|---|---|---|---|
| What Artie reads | CDC change tables, through cdc.fn_cdc_get_all_changes_<capture_instance> | CHANGETABLE(CHANGES …), joined to the table for the row’s current values | Log backup files on an SMB share |
| SQL Server interface | Documented and supported by Microsoft | Documented and supported by Microsoft | Artie parses the log backup files |
| Every change, including intermediate updates | Yes | No. One change per primary key per poll, with the row’s values at read time | Yes |
| Before values on updates | Yes | No | Yes |
| Event timestamp | Commit time | Time Artie read the change | Commit time |
| Latency | Polls every 2.5 seconds | Polls every 2.5 seconds | Your log backup interval, plus a 5-minute poll |
| Load on the database | CDC capture job and change tables | Change tracking side tables | Lowest. Artie reads backup files, and queries the database only for metadata, off-row values, and updates to tables without supplemental logging. |
| Needs timestamp columns | No | No | No |
| Needs a primary key | Captured by the capture instance | Yes | Yes |
Compare the methods
Pick a method to see its column of the table.
Capture instancesDefaultReads SQL Server CDC change tables.
Every change, including intermediate updates
Yes
- What Artie reads
- CDC change tables, through
cdc.fn_cdc_get_all_changes_<capture_instance> - SQL Server interface
- Documented and supported by Microsoft
- Before values on updates
- Yes
- Event timestamp
- Commit time
- Latency
- Polls every 2.5 seconds
- Load on the database
- CDC capture job and change tables
- Needs timestamp columns
- No
- Needs a primary key
- Captured by the capture instance
Which method to use
- Use capture instances when every change matters, for example financial or audit data. It captures each insert, update and delete with before and after values, through an interface Microsoft supports.
- Use transaction log backups when you can’t add load to the production database. You already take log backups for recovery, and Artie reads those files. This method is in early access.
- Use change tracking only when you can’t enable CDC. It tells Artie which rows changed, not how they changed. If a row is updated three times between polls, the destination gets only the last version.
All three methods deliver changes at least once and never skip a change because Artie restarted. See Delivery guarantees. For Always On availability groups, see High availability and failover.
Advanced
Single tenant databases
Artie can unify tables across multiple databases into a single destination table to support single tenant databases.
- Contact Artie to turn on database unification for your workspace.
- In the Databases tab, choose Unify across databases and select the databases to unify.
- In the Tables tab, turn on Unify across databases for each table to merge. This triggers a backfill.
- If your primary key isn’t unique across databases, add the full source table name (
dbName.schemaName.tableName) to it. In the Destination tab’s Advanced settings, check Add ‘full source table name’ column, then Include ‘full source table name’ column as part of primary key.