# Prepare Microsoft SQL Server on AWS RDS for RDI

```json metadata
{
  "schema_version": 2,
  "title": "Prepare Microsoft SQL Server on AWS RDS for RDI",
  "description": "Enable CDC features in your source databases",
  "categories": ["docs","integrate","rs","rdi"],
  "group": "di",
  "tableOfContents": {"sections":[{"id":"create-the-debezium-user","title":"Create the Debezium user"},{"id":"enable-cdc-on-the-database","title":"Enable CDC on the database"}]}

,
  "codeExamples": [{"id":"connect-to-sql-server-as-an-admin-user"},{"id":"grant-the-user-the-necessary-permissions"},{"id":"enable-cdc-for-the-database"},{"id":"enable-cdc-for-each-table-you-want-to-capture"},{"id":"add-the-debezium-user-to-the-cdc-role"}]
}
```


Follow the steps in the sections below to prepare a [Microsoft SQL Server on AWS RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html) database to work with RDI.

> [!NOTE]
> Change Data Capture (CDC) is not supported on SQL Server Express Edition. Only the Standard, Enterprise, and Developer editions support CDC and are supported by RDI.

```checklist {id="rds-sqlserver-list" nointeractive="true" }
- [ ] [Create the Debezium user](#create-the-debezium-user)
- [ ] [Enable CDC on the database](#enable-cdc-on-the-database)
```

## Create the Debezium user

The Debezium connector needs a user account to connect to SQL Server. This
user must have appropriate permissions on all databases where you want Debezium
to capture changes.

```checklist {id="rds-sqlserver-create-debezium-user" nointeractive="true" }
- [ ] [Connect to SQL Server as an admin user](#connect-to-sql-server-as-an-admin-user)
- [ ] [Grant the user the necessary permissions](#grant-the-user-the-necessary-permissions)
```

1. <a id="connect-to-sql-server-as-an-admin-user"></a>
    Connect to your database as an admin user and create a new user for the connector:

    ```sql
    USE master
    GO
    CREATE LOGIN <username> WITH PASSWORD = '<password>'
    GO
    USE <database>
    GO
    CREATE USER <username> FOR LOGIN <username>
    GO
    ```

    Replace `<username>` and `<password>` with a username and password for the new user and replace `<database>` with the name of your database.

1. <a id="grant-the-user-the-necessary-permissions"></a>
    Grant the user the necessary permissions:

    ```sql
    USE master
    GO
    GRANT VIEW SERVER STATE TO <username>
    GO
    USE <database>
    GO
    EXEC sp_addrolemember N'db_datareader', N'<username>'
    GO
    ```

    Replace `<username>` with the username of the Debezium user and replace `<database>` with the name of your database.

## Enable CDC on the database

Change Data Capture (CDC) must be enabled for the database and for each table you want to capture.

```checklist {id="rds-sqlserver-enable-cdc" nointeractive="true" }
- [ ] [Enable CDC for the database](#enable-cdc-for-the-database)
- [ ] [Enable CDC for each table you want to capture](#enable-cdc-for-each-table-you-want-to-capture)
- [ ] [Add the Debezium user to the CDC role](#add-the-debezium-user-to-the-cdc-role)
```

1. <a id="enable-cdc-for-the-database"></a>
    Enable CDC for the database by running the following command:

    ```sql
    EXEC msdb.dbo.rds_cdc_enable_db '<database>'
    GO
    ```

    Replace `<database>` with the name of your database.

1. <a id="enable-cdc-for-each-table-you-want-to-capture"></a>
    Enable CDC for each table you want to capture by running the following commands:

    ```sql
    USE <database>
    GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'<schema>',
    @source_name   = N'<table>', 
    @role_name     = N'<role>',
    @supports_net_changes = 0
    GO
    ```

    Replace `<database>` with the name of your database, `<schema>` with the name of the schema containing the table, `<table>` with the name of the table, and `<role>` with the name of a new role that will be created to manage access to the CDC data. 

    > [!NOTE]
    > The value for `@role_name` can’t be a fixed database role, such as `db_datareader`. 
    > Specifying a new name will create a corresponding database role that has full access to the
    > captured change data.

1. <a id="add-the-debezium-user-to-the-cdc-role"></a>
    Add the Debezium user to the CDC role:

    ```sql
    USE <database>
    GO
    EXEC sp_addrolemember N'<role>', N'<username>'
    GO
    ```

    Replace `<role>` with the name of the role you created in the previous step and replace `<username>` with the username of the Debezium user.