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);
No comments:
Post a Comment