Press "Enter" to skip to content

Category: Replication

Learning Experiences from Transactional Replication

Ned Otter shares war stories:

I’ve dealt with SQL replication for decades, and in a sense, not a lot has changed. I mean this from a basic configuration and troubleshooting perspective, though it has in some ways been extended a bit through the years, for new SQL Server features (like In-Memory OLTP, Azure, etc.).

Many refer to replication as the the Swiss Army Knife of SQL Server, and I can understand why, but with this “extreme flexibility” comes “extreme shortcomings”, and this post will delve into some of the issues you should be aware of.

Click through for plenty of useful tips.

Comments closed

Show All Merge Replication Articles

Steve Stedman prods the demons of merge replication:

At Stedman Solutions, we do a lot of work with SQL Server replication, mostly transactional and merge replication.

The other day I needed a query to show all the merge replication publication on a SQL Server, not just a single database, but to see it for all databases on the SQL Server.

Here is the query that I came up with.

Merge replication can be really great if you know what you’re doing. But it can also turn into a train wreck easily, and it’s really tough to get a good understanding of why something’s going wrong or how long it will take to be fixed (if at all).

Comments closed

Replication Updates in SQL Server 2019 CU13

Kevin Farlee surprises us all with some changes to replication:

Starting with CU13 of SQL Server 2019 , we are including new functionality which facilitates peer-to-peer replication with conflict detection & resolution using a last-writer-wins strategy.  This enables many scenarios for multi-write configurations which may not have been possible or practical before.

Traditionally, customers configuring peer-to-peer replication are advised to implement a sharded solution which ensures that each row is updated on only one replica server (key values A-M are updated on server 1 while key values N-Z are updated on server 2, etc.).  If it should happen that the same row is updated within a small time window by two servers in a replication environment, a conflict will be detected, which will either stop replication after raising an error, or it may optionally be resolved using the originator ID of the replica sending the update.  (i.e. if you have replicas with Originator IDs 1 and 2, then updates coming from the replica with originator ID 2 would always win, and be persisted.  See Conflict Detection in peer-to-peer replication – SQL Server | Microsoft Docs for a detailed explanation.

Read on for more details.

Comments closed

Migrating a Replicated Database without Reinitializing

Jonathan Kehayias provides some tips on migrating (transactional) replicated databases when the cost of reinitializing is just too high:

The first part of building any plan is to know what specifically is being migrated or upgraded. This is important because the steps will be slightly different if you are only migrating or upgrading one part of the environment vs. the entire environment. It is also important to know where the distributor is for the configuration and whether or not the distribution database is going to be affected by the changes being made. In most cases it is a full environment migration to newer hardware or upgrade to newer release of SQL Server and the steps below will work. This is not the only way of migrating/upgrading large replicated databases but this is the one I have used for over 15 years at the date this post was written and it has been flawless.

Read on for more tips and a step-by-step process. With merge replication, of course, things are 40% more difficult.

Comments closed

Kafka Replication with MIrrorMaker

Paul Brebner starts a new series:

In this new two part blog series we’ll turn our gaze to the newest version of MirrorMaker 2 (MM2), the Apache Kafka cross-cluster mirroring, or replication, technology. MirrorMaker 2 is built on top of the Kafka Connect framework for increased reliability and scalability, and is suitable for more demanding geo-replication use cases including migration, backup, disaster recovery and fail-over.  In part one we’ll focus on MirrorMaker 2 theory (Kafka replication, architecture, components and terminology) and invent some MirrorMaker 2 rules. Part two will be more practical, and we’ll try out Instaclustr’s managed MirrorMaker 2 service and test the rules out with some experiments. 

Go check out part 1.

Comments closed

Checking on Transactional Replication Status

Steve Stedman wants to see how far behind transactional replication is:

Working with Transactional Replication with many of our SQL Server clients, one of the big questions that we are always asking is replication keeping up? Recently when deleting a few hundred million rows from a large table that is synced with transactional replication, I needed to programmatically check how the distribution was keeping up. The one measure that indicated that things were behind was the subscriber undistributed commands tab in the replication monitor. When the number of commands waiting to be applied was too large, I had to slow down my delete process.

Click through for an easy script. The really frustrating part is that transactional replication is easy but answering the same question with merge replication is a royal pain.

Comments closed

Injecting a Tracer Token into Each Transactional Replication Publication

Steve Stedman has a script for us:

When working with SQL Server transactional replication, one of the common checks to see if things look healthy is to insert a tracer token into the replication data stream and see how long it takes to get to the subscribers.

This is a process that can be very tedious if you have multiple publications to check, as you need to go to the replication monitor, click on the publisher, then go to tracer tokens tab and insert a tracer token. Then wait to see the result. If you need to do this more than a couple times, you may be waiting for a while.

As a result, Steve has created a way to simplify this. Click through for that script.

Comments closed

SQL Server Replication Requires Actual Server Names

Steve Stedman walks us through a pain point when using replication:

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name. (Replication.Utilities).

You might be thinking to yourself that you had a typo in the server name, but no, after checking the server name it matches what you can connect with.

When I’ve seen this error, often it will even tell me the server name it’s expecting, which then makes me ask why I have to type it in if it knows already.

Comments closed

Transactional Replication Error: Remote Server Does Not Exist

Garland MacNeill takes us through a replication issue:

For the past couple of days, I’ve been working on getting transactional replication set up between a couple of servers in between other projects I’ve been working on. For the last day I kept running into the following error:

“The remote server <“server name”> does not exist, or has not been designated as a valid Publisher, or you may not have permissions to see available Publishers. ” 

Click through for the solution.

Comments closed

Merge Replication: Subscriber and Publisher Versions

Steve Stedman gives us the proper order for upgrading SQL Server when you’re using merge replication:

Working on a recent SQL Server merge replication project we needed to update some of the servers in a merge replication scenario without upgrading all of them. Consider a merge replication setup with a publisher, a distributor and 2 or more subscribers all on the same version of SQL Server, and you need to upgrade the SQL Server version on the subscriber to a newer version like SQL Server 2019.

Before doing any type of upgrade, I wanted to confirm that things would or would not work. First checking some Microsoft documentation it appears that replication from a SQL 2012, SQL 2014, SQL 2016, or any older version of a publisher is not supported to a subscriber running on SQL Server 2019. Or more specifically the subscriber needs to be on the same ore older version than the publisher.

Read on for a demo, as well as an interesting caveat.

Comments closed