> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/how-to-guides-1/connectors/enable-cdc/microsoft-sql-server.md).

# Microsoft SQL Server

This article guides you through the process of enabling CDC on your SQL Server database.

## Prerequisites for SQL Server

Upsolver currently supports the following SQL Server set-up:

* Amazon RDS for SQL Server
* SQL Server 2019 (15x) 
* SQL Server Standard or Enterprise Edition
* SQL Server Agent must be running

{% hint style="info" %}
Please refer to the Amazon RDS article [Using change data capture](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.CDC.html) for more information.
{% endhint %}

### Enable CDC on your database

Ensure you are logged in to SQL Server using an account with sysadmin privileges. From your query window, run the following to enable change data capture on the database:

```sql
USE Sales
GO
EXEC sys.sp_cdc_enable_db  
GO  
```

For Amazon RDS for SQL Server, enable change data capture as follows:

```sql
// Enable CDC on the Sales database 
EXEC msdb.dbo.rds_cdc_enable_db 'Sales' 
GO  
```

### Enable CDC on your tables

After CDC is enabled for a database, any user with **db\_owner** privileges of the database can enable or disable CDC at the table level.

To check if a table has already been enabled for change data capture, run the following:

```sql
// Check if the Customer table is enabled for CDC
SELECT  [name], is_tracked_by_cdc 
FROM    sys.tables
WHERE   [name] = 'Customer'
```

If **is\_tracked\_by\_cdc** returns **0**, enable change data capture for each table you want to include in the capture:

```sql
// Enable CDC on the Customer table
USE Sales
GO  
  
EXEC sys.sp_cdc_enable_table  
  @source_schema = N'dbo',  
  @source_name = N'Customer',  
  @role_name = N'cdc_role',  
  @filegroup_name = N'fg_sales_cdc_data',  
  @supports_net_changes = 1  
GO  
```

The input parameters should be specified as follows:

<table><thead><tr><th width="236">Parameter</th><th>Description</th></tr></thead><tbody><tr><td>@source_schema</td><td>This is the name of the schema to which the table belongs, for example, <strong>dbo</strong>.</td></tr><tr><td>@source_name</td><td>The name of the source table you want to enable for change data capture, e.g. <strong>Customer</strong>.</td></tr><tr><td>@role_name</td><td>To control access to the changed data, you can optionally specify an existing fixed server or database role. If the database role does not exist, SQL Server creates it. <br><br>Users must have <code>SELECT</code> permissions on all captured columns in the source table and be added to the new role if they are not members of the <strong>db_owner</strong> or <strong>sysadmin</strong> roles.<br><br>Alternatively, if you don't want to use a gating role, set this parameter to NULL, e.g. <code>@role_name = NULL</code>.</td></tr><tr><td>@filegroup_name</td><td>It is best practice to store your change data table separately from the source table. You can optionally specify the name of a pre-existing filegroup to store the CDC data, e.g. <strong>fg_sales_cdc_data</strong>. <br><br>If you don't specify an alternative filegroup, CDC data is stored in the default filegroup of the database.</td></tr><tr><td>@supports_net_changes</td><td>Set this value to <strong>1</strong> if you want a net changes function to be generated for the capture instance. The net changes function will return one change for each distinct row that was changed in the specified interval in the call. For more information, please refer to <a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-net-changes-capture-instance-transact-sql">cdc.fn_cdc_get_net_changes_&#x3C;capture_instance></a>. <br><br>The net changes function requires the source table to include a primary key or unique index. If using the latter, you must include the <code>@index_name</code> parameter to specify the name of the unique index.</td></tr></tbody></table>

#### Specifying capture columns

By default, when you enable CDC on a table, all columns are identified as captured columns. If you don't need to track all columns or want to exclude specific columns for privacy reasons for example, you can use the `@captured_column_list` parameter:&#x20;

```sql
// Enable CDC on the Customer table and limit the captured columns
USE Sales
GO  
  
EXEC sys.sp_cdc_enable_table  
  @source_schema = N'dbo',  
  @source_name = N'Customer',  
  @role_name = N'cdc_role',  
  @captured_column_list = 'CustomerID, Title, FirstName, LastName, CompanyName'
  @filegroup_name = N'fg_sales_cdc_data',    
  @supports_net_changes = 1  
GO  
```

If you have set `@support_net_changes` to **1** to switch on net change tracking, you must include either the primary key column or the columns defined in the unique index in the `@captured_column_list` parameter.

***

{% hint style="success" %}
**Learn More**

For more detailed information on CDC, please refer to the [Enable and disable change data capture](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server) guide from Microsoft.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/how-to-guides-1/connectors/enable-cdc/microsoft-sql-server.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.