# CDC setup in SQL Server

## SQL Server CDC setup on Linux

* Create a User for Sprinkle using your own password

```
Use <databasename>
CREATE LOGIN sprinkle WITH PASSWORD = 'password';
CREATE USER sprinkle FOR LOGIN sprinkle; 
```

* Give select permission on the database

```
GRANT SELECT on DATABASE::<database> to sprinkle;
```

* Enable Change Data Capture. To turn on Change Data Capture first, you need to enable it at the database level.

```
USE [<database>];
EXEC sys.sp_cdc_enable_db
```

* Next, you need to enable it for each table you would like to integrate

```
EXEC sys.sp_cdc_enable_table
@source_schema = '<schema>',
@source_name   = <table>,
@role_name     = 'MyRole',
@supports_net_changes = 0
```

* Test by listing tables enabled with CDC (the below command should be run with the same user as used to connect)

```
EXEC sys.sp_cdc_help_change_data_capture;
```

* Start Sql server agent if not already running

Command definitions used above:

* USE- Changes the database context to the specified database or database snapshot in SQL Server.
* CREATE LOGIN - Creates a login for SQL Server, SQL Database, Azure Synapse Analytics, or Analytics Platform System databases.
* CREATE USER - Adds a user to the current database.
* EXEC - Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR stored procedure, scalar-valued user-defined function, or extended stored procedure.
* GRANT - Grants permissions on a database in SQL Server.

## AZURE SQL Server CDC Setup

&#x20;**Run the following commands to enable CDC in Azure:**

* Enable Change Data CaptureTo turn on Change Data Capture first, you need to enable it at the database level.

```
USE [<database>];
EXEC sys.sp_cdc_enable_db
```

* Next, you need to enable it for each table you would like to integrate.

```
EXEC sys.sp_cdc_enable_table
@source_schema = '<schema>',
@source_name   = <table>,
@role_name     = 'MyRole',
@supports_net_changes = 0
```

* Test by listing tables enabled with CDC (the below command should be run with the same user as used to connect):

```
EXEC sys.sp_cdc_help_change_data_capture;
```

## Column updates with CDC enabled

### Adding new column

Adding a new column to table requires recreating cdc capture instance. Explaining the steps required with below example

We have a table named sample contains id and name as columns,

This block of code executes only when CDC is not enabled, if enabled then we don’t have to write this block(make sure capture\_instance is there if it’s already enabled.)&#x20;

```
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
 @source_name = N'sample',
  @role_name = NULL, @capture_instance = 'dbo_sample'
```

Query to add column

```
ALTER TABLE dbo.sample ADD TestColumn int
```

Code Block to create new capture instance for same table&#x20;

```
EXEC sys.sp_cdc_enable_table 
     @source_schema = N'dbo',
     @source_name = N'sample', 
     @role_name = NULL,
     @capture_instance = 'dbo_sample2'
```

&#x20;Shifting all data from older instance to newer instance&#x20;

```
INSERT INTO cdc.dbo_sample2_CT 
($start_lsn, $end_lsn,$seqval,$operation,__$update_mask,id,name) 
   SELECT __$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, id, name 
   FROM cdc.dbo_sample_CT
```

&#x20;Disabling the older instance, as 1 table can have only 2 capture instance&#x20;

```
EXEC sys.sp_cdc_disable_table 
     @source_schema = N'dbo', 
     @source_name = N'sample', 
     @capture_instance = 'dbo_sample'
```

### Dropping a column

Follow the above steps and reset the ingested table and run ingestion again, it will drop the column.(No boundation to follow above steps).&#x20;

OR&#x20;

Users can simply drop the column, using simple sql query and user can reset the ingested table and run ingestion again, it will drop the column. If not reset, then the values for column will come as null.

### Updating column type

Users can simply update the data-type and reset the table, on running ingestion again,it will update the data type.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.sprinkledata.com/product/ingesting-your-data/pipelines/databases/features/cdc-setup/cdc-setup-in-sql-server.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.