Queries to build START and STOP commands for PUSH Distribution Agents

Chris Skorlinski, Microsoft SQL Server Escalation Services I had need other day to STOP and START all Distribution Push Agent.  Using the syssubscriptions and syspublications I can easily build list of SQL commands to either stop or start all the Distribution Push Agents.  With output to text, I can copy and execute from SQL Agent…


Script to show Replication Topology

Chris Skorlinski, Microsoft SQL Server Escalation Services I came across this forgotten but very handy script from my friends Suhas and Gaurav to display your Replication topology. The output below is from SQL 2016 sample publications using AdventureWorksLT.   https://gallery.technet.microsoft.com/Display-Replication-b15e730c ============================================================= REPLICATION TOPOLOGY ============================================================= SELECT THE PUBLICATION-SUBSCRIPTION PAIR FOR SCOPING THE CASE =============================================================   SQL2016…


Challenges running Continuous Merge Agents

Chris SkorlinskiMicrosoft SQL Server Escalation Services I wanted to cover in this blog challenges you’ll encounter running Merge Agent on continuous schedule.  These are Merge Agents whose SQL Server Agent jobs schedule are set for “Start automatically when SQL Server Agent starts”.     Each time a Merge Agent starts it logs start time in…


Suppported SQL Server versions in Replication Topology

Chris Skorlinski, Microsoft SQL Server Escalation Services Customer planning for migration to SQL Server 2016 asked for clarification on running mixed version of SQL Servers in their Replication Topology.  I searched for “mixed version”, but was unable to find our posted topic. My colleague George Mason tracked it down under “upgrade”.  I’m posting key paragraph…


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…