Category: Inside Replication

Reset topology using “Replication Support Only”

Share this PostIf you need to reset your Replication topology you can quickly rebuild subscribers using the “no initialize” also called “Replication Support Only (RSO)”.  WARNING: These steps are designed to remove ALL of your Replication configuration and settings, recreating with new metadata tables. The RSO option skips the Snapshot data reinitialization phase. It only pushed
Read More »

Example of troubleshooting Distribution Agent errors

Share this PostI thought it would be helpful to post a Replication Distribution Agent troubleshooting case to show more about replication components and troubleshooting approaches. Problem: SQL Server Distribution Agent reported “Failed” in Replication Monitor. To capture text of the message we added the following parameters to the Distribution Agent job and restarted the job. 
Read More »

Subscriber bloating when VarDecimal Compressed settings not transferred

Share this PostWhen 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)
Read More »

Enabling Transactional Subscriber with Identity Ranges for Updates

Share this PostTransactional Replication published tables containing Identity Ranges are updated on subscriber via Replication Distribution Agent.  This processes does not increment the subscriber’s table Identity Seed starting value. If you drop a Subscriber and want to bring it online as an updatable user database, first execute CHECKINDENT reseeding the table to highest value +
Read More »

Inside Distribution Cleanup Watermark

Share this PostThe Distribution database is a cache of undistributed commands that the Distribution SQL Agent jobs are responsible to deliver to their subscribers.  This cache is populated by the Publisher LogReader Agent job as new data changes are detected. The primary tables holding the cache of undistributed data is MSrepl_transactions and MSrepl_commands. Share this
Read More »