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

# Capture instances

## Required settings

* Host name
* Port (default is `1433`)
* Service account
* Database name
* CDC 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="Enabling CDC">
  In order to enable CDC for SQL Server, you will enable it at the **database and table** level.

  1. **Enable CDC for your database**

  ```sql theme={null}
  USE [DATABASE_NAME];
  EXEC sys.sp_cdc_enable_db;
  -- If you're using RDS:
  EXEC msdb.dbo.rds_cdc_enable_db [DATABASE_NAME];

  -- Now specify the retention period of your CDC logs, retention is specified as mins.
  -- We recommend you setting this between 24h to 7 days
  EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080; -- 7 days
  GO;
  ```

  2. **Enable CDC for your tables**

  ```sql theme={null}
  -- You can specify the service_account as the @role_name to restrict access
  EXEC sys.sp_cdc_enable_table @source_schema = 'SCHEMA_NAME', @source_name = 'TABLE_NAME', @role_name = null;
  ```
</Accordion>

### Troubleshooting

<Accordion title="Altering the table's schema">
  When you alter the table's schema, you will need to rotate the capture instances so that changes continue to be captured.

  ```sql theme={null}
  -- If you modify an existing table:
  ALTER TABLE customers ADD phone_number VARCHAR(32);

  -- You'll want to rotate the capture instances so that changes continue to be captured.
  EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'customers', @role_name = NULL, @supports_net_changes = 0, @capture_instance = 'dbo_customers_v2';
  GO

  -- After some time, you can then drop the old capture instance by running `sys.sp_cdc_disable_table`.
  EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'customers', @capture_instance = 'dbo_customers';
  GO
  ```
</Accordion>

<Accordion title="Unique index override">
  By default, `sys.sp_cdc_enable_table` will use the primary keys of the table as the unique identifiers.
  If you are running into issues with this, you can optionally set `@index_name` to an unique index of your choice.

  For example, if you had a table that looked like this

  ```sql theme={null}
  CREATE TABLE orders (
      id INTEGER IDENTITY(1001,1) NOT NULL PRIMARY KEY,
      order_date DATE NOT NULL,
      purchaser INTEGER NOT NULL,
      quantity INTEGER NOT NULL,
      product_id INTEGER NOT NULL,
      FOREIGN KEY (purchaser) REFERENCES customers(id),
      FOREIGN KEY (product_id) REFERENCES products(id)
  );
  ```

  If you don't want to use `id`, you can do something like this:

  ```sql theme={null}
  ALTER TABLE orders ADD COLUMN prefix VARCHAR(255) DEFAULT 'orders' NOT NULL;
  -- Create unique index
  CREATE UNIQUE INDEX cdc_index ON dbo.orders (id, prefix);
  -- When enabling CDC, use cdc_index instead of the primary key
  EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'orders', @role_name = NULL, @index_name = 'cdc_index';
  ```
</Accordion>