Aws Rds Sqlserver

Follow the steps in the sections below to prepare a Microsoft SQL Server on AWS RDS 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. {{< /note >}}

- [ ] [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.

- [ ] [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:

   sqlCopy

   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.

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

   sqlCopy

   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.

- [ ] [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:

   sqlCopy

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

   Replace <database> with the name of your database.

2. <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:

   sqlCopy

   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. {{< /note >}}

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

   sqlCopy

   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.