AWS recently announced a new feature which will help to enable CDC in RDS SQL server on user databases. Here this the detailed blog post that explains how to enable this CDC for DMS service. CDC is an enterprise edition feature. But RDS SQL Server Standard editions are supporting CDC. The most important thing is we can use this feature to achieve the below things.
- Reporting Server: Since the read replica is not supported for MSSQL and Oracle in RDS, so by using DMS ongoing replication we can make a real-time Reporting Server for this. Also you can replicate the data to any one of your data warehouse like RedShift.
- Cross Region DR: Inter-Region VPC peering is generally available now. So we can create a DR solution for your RDS SQL Server to another region.
Primary Key and Non-Primary Key tables:
AWS DMS will use SQL server’s native transaction replication method to capture the changes and replicate it to the Target. For non-primary key tables, AWS DMS will use MS-CDC feature to capture the changes.
How to enable CDC on RDS SQL Server:
As per AWS blog,
Enable CDC feature for a particular Database
exec msdb.dbo.rds_cdc_enable_db 'db_name'
Run the following for each table to enable MS-CDC:
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL, @supports_net_changes = 1 --for PK table 1, non PK tables 0 GO
Set the retention period for the CDC data(in seconds):
EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 86400
What are all the difficulties here?
- We have to run the sp_cdc_enable_table procedure on all the existing tables. If we have 500 table then we have to execute this procedure for 500 times.
- supports_net_changes – If the table has a primary key then we have to use 1, for non-primary key tables use 0.
- Manually execute the sp_cdc_enable_table after a table has been created. Before adding this table to CDC, DMS won’t replicate the data. So we must have to enable CDC before inserting any data in it.
- Multi-AZ won’t have CDC: In case if the RDS failover to the standby instance, then the CDC setting are won’t replicate. AWS recommends to use rds_set_configuration. But I’ll research and update later.
How did I fix these three difficulties?
I have written a stored procedure to automatically enables CDC on the existing tables. It’ll get the list of tables from the INFORMATION_SCHEMA.TABLES and compare it with INFORMATION_SCHEMA.CONSTRAINTS. So if the table has Primary key and it’ll pass 1 to the supports_net_changes else it will pass 0.
Enable CDC on existing tables in one- shot:
Download the procedure here and install the database where you need to enable CDC.
In this example, I have enabled CDC on DBAdmin database.
EXEC sp_add_cdc 'DBAdmin'
Automatically enable CDC on newly created tables:
For this, I have created a database trigger which will execute the sp_cdc_enable_table procedure once the table has been created. Once the table has been created then this trigger get the table schema and name from the EventData() and store it into the DBSchema_Change_Log table(We have to create this table before creating the trigger). Then it’ll get the schema name, table name, is primary key or not. Then it’ll pass the parameters to sp_cdc_enable_table .
Here you can get this create DBSchema_Change_Log table query and the trigger.
This store procedure and the trigger saved me a lot of time, So hopefully this will help if you are using RDS SQL Server with DMS for ongoing replication.
Limitations of the DMS on RDS SQL Server:
- It won’t replicate the following things.
- Foreign key
- stored procedure
- Initially, we have a execute a command to enable CDC for each and every table. (We have a solution for this)
- It won’t add newly created tables to replication(we have the solution)
- If Multi-AZ enabled, then the Secondary server won’t have the CDC information(Again we have to manually enable CDC, this might be lead to some data loss during multi AZ failover – I’ll work to overcome this).
To learn more about the limitations, refer this AWS Documentation.