Press "Enter" to skip to content

Category: Replication

Bidirectional Transactional Replication and Managed Instances

Published 2022-08-02 by Kevin Feasel

Holger Linke builds a transactional replication topology with a couple of twists:

Bidirectional transactional replication is a specific Transactional Replication topology that allows two SQL Server instances or databases to replicate changes to each other. Each of the two databases publishes data and then subscribes to a publication with the same data from the other database. The “@loopback_detection” feature ensures that changes are only sent to the Subscriber and do not result in the changes being sent back to the Publisher.

The databases that are providing the publication/subscription pairs can be hosted either on the same SQL instance or on two different SQL instances. The SQL instances can either be SQL Server on-premise, SQL Server hosted in a Virtual Machine, SQL Managed Instance on Azure, or a combination of each. You just have to make sure that the instances can connect to each other. If you add a subscription by using the fully-qualified domain name (FQDN), verify that the server name (@@SERVERNAME) of the Subscriber returns the FQDN. If the Subscriber server name does not return the FQDN, changes that originate from that Subscriber may cause primary key violations.

Read on for the scripts.

Comments closed

The Joys of Replication, Example 48,106

Published 2022-07-14 by Kevin Feasel

Garry Bargsley troubleshoots a transactional replication issue:

Use transactional replication to replicate similar data from four databases to one Azure DB.  This sounds pretty straightforward for anyone who has done any replication work.

However, once I had everything setup and working, things stopped working, and it was a head-scratcher as to why.  I made the proper settings configurations on each article, or so I thought.  Let me show you the scenario in more detail.

Read on for the answer.

Comments closed

Finding Articles in a SQL Server Publication

Published 2022-05-23 by Kevin Feasel

Kenneth Fisher disturbs the slumber of the forces of replication:

The other day I was asked to supply a list of all of the tables being replicated into a given database. Now, for those of you that aren’t aware, if I replicate a group of tables from database SourceDB into DestDB I can still have additional tables in DestDB that have nothing to do with the replication. So this wasn’t just a matter of getting a list of tables from the database.

Click through for queries which work for transactional replication as well as merge replication.

Comments closed

Replication in Azure DB for MySQL

Published 2022-02-16 by Kevin Feasel

Arun Sirpal explains how you can set up replication with Azure DB for MySQL:

No doubt there will be a need for you to split off your analytical queries from the main database for performance reasons.

If you have been following me in the past with Azure SQL DB you would use failover group read endpoints. With MySQL we would need to build a replica (read only) to another server. This uses MySQL’s native feature binlog replication which is great to hear. This form is asynchronous.

Read on to see how.

Comments closed

Bidirectional Transactional Replication and Server Names

Published 2022-01-20 by Kevin Feasel

Mousa Janini points out a requirement of bidirectional transactional replication:

The steps to create a Bi-directional replication is simple, and similar to the steps for configuring transnational replication with extra step to enable the @loopback_detection parameter of sp_addsubscription to ensure that changes are only sent to the Subscriber and do not result in the change being sent back to the Publisher.

The most common issue for the Bi-directional replication is when the loop back detection is not working as expected; which results in data conflicts and Primary Key Violations.

Read on to see what is the cause of this problem and what you can do to solve it.

Comments closed

Replication Error 20084 on SQL Server 2019

Published 2022-01-05 by Kevin Feasel

I ran into a weird issue:

Iwas helping out with a SQL Server upgrade recently, going from 2016 to 2019. We ran into a problem when trying to run replmerg.exe for a merge replication subscription. Specifically, we were getting error code 20084, which means that the replication process couldn’t connect to one of the instances. Interestingly, the process couldn’t connect to the local instance, and the failure was immediate—that is, within a couple of milliseconds. There was nothing in the management logs on either the distributor server or the subscriber server which indicated a problem. We were able to connect both sides together just fine—from the subscriber, we could connect to the distributor, and from the distributor, we could connect to the subscriber.

Click through for what error code 20084 typically means, as well as what turned out to be the problem here.

Comments closed

Marking Replication Transactions Complete

Published 2021-12-22 by Kevin Feasel

Andrea Allred spams the “burn it down” button:

Replication is not my favorite, it is kind of far from my favorite. No further than that. Little further.

When it breaks, it can cause havoc and it always seems to break at the worst time. Recently we noticed that our logfile was massive (like 3 times the size of the database) and that was making many of the other processes painful. We didn’t know how long the log hadn’t been clearing so we got to burn it all (kind of).

The first thing I did was tell replication that we were done with all the transactions that had been committed.

I’d say about 40-50% of the pain of replication is in how difficult it is to troubleshoot. Transactional replication is an order of magnitude easier than merge replication, too, especially on systems of non-trivial size and scale. The single most common question I get is “When will this row be replicated to the other side?” I can’t answer that with merge replication. The second-most common question is, “Why are things slower right now than before?” Can’t answer that either…

Comments closed

Learning Experiences from Transactional Replication

Published 2021-12-14 by Kevin Feasel

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

Published 2021-11-09 by Kevin Feasel

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

Published 2021-10-06 by Kevin Feasel

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