SQL Server Replication Upgrade Strategies

In the recent past, we have seen questions on the strategy/steps on upgrading a replication topology especially from customers using SQL Server 2008/2008 R2 or running SQL Server on Windows Server 2008 and 2008 R2. We have outlined the most common topologies for replication and the possible upgrade paths for all releases from SQL Server…


Clarification on MSDN Initialize a Transactional Subscription from a Backup

Chris SkorlinskiMicrosoft SQL Escalation Services I hope this blog posting will provide some clarification into the MSDN article providing steps on how to initialize a transactional subscription from a backup. https://msdn.microsoft.com/en-us/library/ms147834.aspx First I want to clarify the @backupdevicename parameter. The article reads like the “DISK = “should be included in the parameter, it should not….


Repltalk – Replication Tuning Knobs – LogReader

Chris SkorlinskiMicrosoft SQL Server Escalation Services If you’re familiar with Microsoft SQL Server Replication technology you already know each Replication Agent has various parameters or knobs you can set via Agent Profile or SQL Agent job parameter. This series of posts provide insight into tuning Replication performance using these knobs. Following the 80-20 rule, 80%…


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