Subscriber bloating when VarDecimal Compressed settings not transferred

When using compressed “VarDecimal Storage Format Enabled” on Transactional or Merge published tables you’ll discover this setting is not applied to subscribers leading to table bloating.

Database Properties

Table Properties

Try This: Publish table with varDecimal

USE [AdventureWorks2017] GO

CREATE TABLE [dbo].[DecZero](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[F1] [decimal](23, 2) NOT NULL,
[F2] [decimal](23, 2) NOT NULL,
[F3] [decimal](23, 2) NOT NULL,
[F4] [decimal](23, 2) NOT NULL,
[F5] [decimal](23, 2) NOT NULL,
CONSTRAINT [pkDecZero] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[DecZero] ADD CONSTRAINT [DF_DecZero_F1] DEFAULT ((0)) FOR [F1]
GO

ALTER TABLE [dbo].[DecZero] ADD CONSTRAINT [DF_DecZero_F2] DEFAULT ((0)) FOR [F2]
GO

ALTER TABLE [dbo].[DecZero] ADD CONSTRAINT [DF_DecZero_F3] DEFAULT ((0)) FOR [F3]
GO

ALTER TABLE [dbo].[DecZero] ADD CONSTRAINT [DF_DecZero_F4] DEFAULT ((0)) FOR [F4]
GO

ALTER TABLE [dbo].[DecZero] ADD CONSTRAINT [DF_DecZero_F5] DEFAULT ((0)) FOR [F5]
GO

EXEC sp_db_vardecimal_storage_format 'AdventureWorks2017', 'ON'

Note: Only Enterprise edition of SQL Server supports vardecimal.

EXEC sp_tableoption 'DecZero', 'vardecimal storage format', 1
-- Insert new rows into DecZero table
-- Create a transactional, snapshot, or merge publication.
-- Initialize subscriber using the default snapshot settings.

Observe the subscriber is not created with compressed vardecimal storage resulting in additional storage requirements on subscriber for the same data on publisher.

Workaround:

1. Pre-create the tables on Subscriber with identical schema as Publisher.
2. Change publication setting under Article properties from “Drop and Create” to “truncate only”, then push down a new Snapshot. This ensure new data is pushed down when Subscriber is initialized or re-initialized however table schema is left unchanged.

However, having a table set to not replicate schema requires you to manually change Subscriber’s scheme should published table schema change. Another option for Transactional Replication is to setup the subscribers using Backup\Restore. This ensure the Subscribers have identical schema settings as Publisher. This comes with its own trade-offs. Lean more on www.ReplTalk.com and at https://docs.microsoft.com/en-us/sql/relational-databases/replication/initialize-a-transactional-subscription-from-a-backup