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

# Change tracking

> Learn how to configure Microsoft SQL Server change tracking as a source in Artie. A lightweight CDC alternative that tracks row changes by primary key.

<Note>
  Change tracking is a lightweight alternative to CDC. It records the primary key of the row that has changed and the operation and Artie will then query the source tables to retrieve the row data.

  This method is recommended if you cannot enable CDC for your database.
</Note>

## Required settings

* Host name
* Port (default is `1433`)
* Service account
* Database name
* Change tracking enabled for database and tables to replicate

<Accordion title="Creating a service account">
  ```sql theme={null}
  USE [DATABASE_NAME];
  CREATE LOGIN artie WITH PASSWORD = 'PASSWORD';
  CREATE USER artie FOR LOGIN artie;
  ALTER LOGIN artie ENABLE;
  GRANT SELECT on DATABASE::DATABASE_NAME to artie;
  GO;
  ```
</Accordion>

<Accordion title="Permissions">
  To access the change tracking metadata, the service account must have the following permissions:

  * `SELECT` permission on at least the primary key columns on the change-tracked table to the table that is being queried.

  * `VIEW CHANGE TRACKING` permission on the table for which changes are being obtained.

  [Reference](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/manage-change-tracking-sql-server?view=sql-server-ver17#security)
</Accordion>

<Accordion title="Enabling change tracking">
  In order to enable Change Tracking for SQL Server, you will enable it at the **database and table** level.

  1. **Enable Change Tracking for your database**

  ```sql theme={null}
  USE [DATABASE_NAME];
  ALTER DATABASE [DATABASE_NAME]
  SET CHANGE_TRACKING = ON
  -- Specify the retention period of your Change Tracking tables.
  -- We recommend you setting this between 24h to 7 days
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
  ```

  2. **Enable CDC for your tables**

  ```sql theme={null}
  ALTER TABLE [TABLE NAME] ENABLE CHANGE_TRACKING;
  ```
</Accordion>

### Troubleshooting

<Accordion title="Change tracking auto-cleanup">
  [Microsoft Troubleshooting Documentation](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/cleanup-and-troubleshoot-change-tracking-sql-server)

  **Check Change Tracking space usage**

  ```sql theme={null}
  DECLARE @object_id INT;
  DECLARE @change_table_name NVARCHAR(MAX);
  DECLARE @sqlCommand NVARCHAR(MAX);

  SET @object_id = OBJECT_ID([TABLE NAME]);
  SET @change_table_name = (SELECT TOP 1 name FROM sys.internal_tables where parent_object_id = @object_id);
  SET @sqlCommand = N'EXEC sp_spaceused N''' + 'sys.' + @change_table_name + N'''';
  EXEC sp_executesql @sqlCommand;
  ```
</Accordion>