Walkthrough of Merge Replication with AlwaysOn High Availability

Chris Skorlinski Microsoft SQL Server Escalation Services I’ve been working with a customer to setup and test Merge Replication Publisher in an AlwaysOn High Availability Group. To quickly spin up the scenario, I used our Microsoft Azure AlwaysOn Template for SQL Server 2014 and within a few minutes had a complete AlwaysOn environment with 2…


ReplTip – Foreign Key Check Constraints

Impact of Foreign Key Check Constraints on SQL Transactional Replication Impact of Foreign Key Check Constraints on SQL Transactional Replication –Chris Skorlinski, Microsoft SQL Server Escalation Services I recently worked with a customer seeing very slow transfer rate with Distribution Agent. Looking at RPC:Completed events we saw each sp_Msdel_ took .5 second generating over 200,000…


ReplTip – Publishing to Subscriber Alias

Chris Skorlinski – Microsoft SQL Server Escalation Services Should the need arise, you can publish to a Transactional Replication subscriber alias provided you setup the subscriber using scripts and not SQL Server Management Studio Replication Wizard. First, verify client alias is configured to redirect connectivity to alias name. In example below SQL Server traffic to…

0

ReplTip – Publishing 1 Article into 2 Publications bloats Distribution DB

Chris Skorlinski – Microsoft SQL Server Escalation Services While visiting a customer site, we discussed consequences of publishing common article\tables into multiple Publications. For this customer, each Publication contains same set of core or common tables used by all subscribers, but then some subscribers had tables unique to just that subscriber. For example, all Publications…


ReplTip – Measuring Latency in Change Data Capture (CDC)

By Sateesh Yele, Microsoft SQL Escalation Services This DMV sys.dm_cdc_log_scan_sessions is very useful in determining the latency with Change Data Capture (CDC). I inserted one record in the database and started the CDC capture job after around 2 minutes. It showed the latency as 165 seconds. It is difference in seconds, between end_time and last_commit_cdc_time…


ReplTip – Distribution cleanup Job running but shows zero rows deleted

  By Taiyeb Zakir, Microsoft SQL Escalation Services Consider the following scenario: Distribution cleanup Job is running and you see that number of rows in msrepl_commands and msrepl_transactions are reducing but the Cleanup Job history says 0 rows deleted: For example: Removed 0 replicated transactions consisting of 0 statements in 12961 seconds (0 rows/sec). [SQLSTATE…


Troubleshooting ‘The process could not execute ‘sp_repldone/sp_replcounters’

Chris Skorlinski, SQL Server Escalation Services I worked on customer’s Transactional Replication issue today where LogReader was failing, restarting, then failing again.  We used link below to enable logging to get a clearer picture of failure.  http://blogs.msdn.com/b/repltalk/archive/2010/03/04/kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx 16:02:45 OLE DB DISTOLE: sp_MSget_last_transaction 16:02:45 Publisher: {call sp_repldone ( 0x000a35e7000315e7005b, 0x000a35e7000315e7005b, 0, 0)} 16:32:45 Status: 2, code:…


@alt_snapshot_folder for PULL Subscribers

–Chris Skorlinski, SQL Server Escalation Services To improve SQL Distribution agent performance when applying Snapshot, first ZIP then copy the snapshot files locally to the subscriber. This is a great solution for subscribers on WAN international networks as ZIP can be smaller and faster to copy then Replication can move uncompressed Snapshot files.  Testing shows…


Merge metadata cleanup changes in SQL Server 2012 SP2 and SQL Server 2014 SP1

  Content provided by: Hugo Nunes, Microsoft Support Escalation Engineer TITLE: Merge metadata cleanup changes in SQL Server 2012 SP2 and SQL Server 2014 SP1 Abstract: This article briefly describes the change in the merge metadata cleanup logic introduced in SQL Server 2012 SP2 and SQL Server 2014 SP1, and provides some recommendations Terms and…


ReplTip – Breakout by Article

–Chris Skorlinski, Microsoft SQL Escalation Services My friend Brendan Odwyer gave me great query to show Distribution database breakout by article for Transactional Replication publications.  We used this to identify 1 table that had +90 million rows  pending in the Distribution database as result of a large bulk update.  It would have taken Replication days…