Impacts on MS-Replication or MS-CDC to enable CDC solution

 

Configuration	DML Duration	Datafile (Allocated MB)	Datafile (Free MB)	Tlog (Allocated MB)	Tlog Size (Free MB)
No Replication	00:07:19	8	4.4	392	146.4
MS-Replication	00:07:22	8	3.2	392	214
MS-CDC	00:07:11	136	17.8	456	24.3
MS-Replication + MS-CDC	00:09:27	136	22.7	456	190

Instance Configuration

/*
To avoid error: Update mask evaluation will be disabled in net_changes_function because the CLR configuration option is disabled.
*/
EXEC sp_configure 'clr enabled';  
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;    

Database Creation

CREATE DATABASE [db_msrepll]
 ON  PRIMARY ( NAME = N'db_msrepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll.mdf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON ( NAME = N'db_msrepll_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll_log.ldf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB );
-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Table Creation

create table [db_msrepll].dbo.T (i int identity primary key, c text);
create table [db_mscdc].dbo.T (i int identity primary key, c text);
create table [db_msrepl_cdc].dbo.T (i int identity primary key, c text);
create table [db_norep].dbo.T (i int identity primary key, c text);

Enable CDC requirement with MS-Replication

For replication tool, it only requires additional information in the TLog, thus there is a filter 1=2, so no extra data writing to DistributionDB.

use [db_msrepl]
exec sp_replicationdboption @dbname = N'db_msrepl', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [db_msrepl]
exec sp_addpublication @publication = N'msrep', @description = N'Transactional publication of database ''db_msrepl'' from Publisher ''EC2AMAZ-PPQ3EA9''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'msrep', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db_msrepl]
exec sp_addarticle @publication = N'msrep', @article = N'T', @source_owner = N'dbo', @source_object = N'T', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'T', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboT', @del_cmd = N'CALL sp_MSdel_dboT', @upd_cmd = N'SCALL sp_MSupd_dboT', @filter_clause = N'1=2'

-- Adding the article filter
exec sp_articlefilter @publication = N'msrep', @article = N'T', @filter_name = N'FLTR_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'msrep', @article = N'T', @view_name = N'SYNC_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- repeat this for [db_msrep_cdc]

Enable CDC requirement with MS-CDC

In this case, MS-CDC automatically populates CDC related table (such as dbo_T_CT) along the DML changes, that explains why data file size increases.

USE [db_mscdc]  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'T',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO  
-- repeat this for [db_msrep_cdc]

Simulate DML workloads

use [db_msrepl];

DECLARE
	@Counter int= 1
WHILE @Counter< = 100000
BEGIN
	insert into T (c) values (replicate('x',100));
	update T set c=replicate('y',100);
	delete from T;
	SET @Counter= @Counter + 1
END;

-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Check the data file and Tlog size

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128 AS CurrentSizeMB,  
    size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);