AWS DMS w/ SQL Server: Specify FileGroup for sp_cdc_enable_table w/ SetUpMsCdcForTables

0

The SetUpMsCdcForTables in DMS is a nice touch. It helps manage the tables used for CDC in SQL Server w/o a lot of manual scripting. Microsoft recommends using a separate FileGroup to separate the I/O for CDC operations (link). Is there a way to specify a FileGroup to use for the sp_cdc_enable_table call managed by the SetUpMsCdcForTables configuration?

Kelly S
asked 23 days ago105 views
1 Answer
0

Hello,

Q: Is there a way to specify a FileGroup to use for the sp_cdc_enable_table call managed by the SetUpMsCdcForTables configuration?

Ans : Simply put, No. I can confirm that Microsoft recommends the usage of custom @filegroup_name when setting CDC for tables from the below doc shared [+] :

[ @filegroup_name = ] 'filegroup_name' The filegroup to be used for the change table created for the capture instance. @filegroup_name is sysname and can be NULL. If specified, @filegroup_name must be defined for the current database. If NULL, the default filegroup is used.

We recommend creating a separate filegroup for change data capture change tables. ---------> Its recommended by Microsoft.

[+] https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver16

However as this parameter is an optional one , Its not necessarily needed. As you can see in the sample example stated in above doc[+] :

Examples

A. Enable change data capture by specifying only required parameters ------> NO OPTIONAL parameters are added here as below parameters are enough to setup CDC

The following example enables change data capture for the HumanResources.Employee table. Only the required parameters are specified.

USE AdventureWorks2022;
GO

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Employee',
    @role_name = N'cdc_Admin';
GO

B : Enable change data capture by specifying additional optional parameters The following example enables change data capture for the HumanResources.Department table. All parameters except @allow_partition_switch are specified.

USE AdventureWorks2022;
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources',
    @source_name = N'Department',
    @role_name = N'cdc_admin',
    @capture_instance = N'HR_Department',
    @supports_net_changes = 1,
    @index_name = N'AK_Department_Name',
    @captured_column_list = N'DepartmentID, Name, GroupName',
    @filegroup_name = N'PRIMARY'; -------------------------------------------> OPTIONAL
GO

Hence, When you make use of DMS ECA "SetUpMsCdcForTables" , this will setup the CDC for table and database with required and default values only. No optional parameters are selected here.

[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.ConnectionAttrib : Using a Microsoft SQL Server database as a source for AWS DMS - Endpoint settings when using SQL Server as a source for AWS DMS

However, if you wish to use the optional parameter "@filegroup_name" then you should setup the CDC for that table manually . You can refer below doc[+] on how to set it up manually :

[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.CDC.MSCDC : Using a Microsoft SQL Server database as a source for AWS DMS - Setting up ongoing replication on a self-managed SQL Server

I hope the information helps !

AWS
answered 23 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions