Category: Replication

Transactional Replication Slow: Check Missing Indexes

Published 2024-04-12 by Kevin Feasel

Have you ever had transactional replication mysteriously start showing significant latency at a subscriber server? If so, check to see if the primary keys from the publisher database are missing on the subscriber database!
Replication was showing long latency while the publisher and subscriber servers were not heavily utilized. Microsoft generated stored procedures that are used to send INSERT, UPDATE and DELETE operations from the publisher database to the subscriber database had no indexes to help query performance to the subscriber tables. Since there were no indexes that matched the keys of the statements pushing through data changes, tables were being fully scanned and replication statements were piling up. In other words, the subscriber was missing indexes.

Read on to see how you can check this and also how you can quickly script those missing indexes.

Setting up Replication with dbatools

Published 2024-03-13 by Kevin Feasel

Jess Pomfret continues a series on replication in dbatools:

This post is focusing on how to setup replication with dbatools. We support all three flavours – snapshot, transactional and even merge replication!
In this article I’ll be creating a transactional publication, but the steps for setup are very similar no matter which flavour you’re implementing.
I’ll walk through and demonstrate all the steps to setup replication in this article as dbatools allows us to complete them all. However, I won’t go into a lot of details on why or how replication works, or provide guidance on best practices. If you need more information on replication as a technology then I recommend visiting the Microsoft Docs.

Read on for a demonstration of how the cmdlets work for adding a publication, articles, subscriptions, and more.

Comments closed

Get- cmdlets in dbatools Replication

Published 2024-03-01 by Kevin Feasel

Jess Pomfret continues a series on replication in dbatools:

This post will show off all the Get- commands that are available within dbatools for replication. When you’re using PowerShell, and especially if you’re new to PowerShell, exploring the Get- commands for a certain module, or area is a great way to get started. As it says in the name, these commands get information about something, they aren’t going to change anything, which means they are pretty safe to run in your environment. Of course, I’m always going to say, you should still run these in your test environment first to make sure you understand what they are doing, and how they behave in your specific environment.

Read on to see some of what you can do with it.

Comments closed

Supporting Replication in dbatools

Published 2024-02-23 by Kevin Feasel

Jess Pomfret makes an announcement:

Replication, everyone loves to hate it, but it’s been our most requested feature as far as adding commands to dbatools. For ages we’ve said ‘sounds great’ and ‘we would love that’, but when we started looking into it the energy soon fizzled away, due to it’s dependency on RMO – Replication Management Objects, as opposed to SMO – SQL Server Management Objects, things weren’t as easy as we had hoped.

That said, it’s now there as of version 2.1.1. It supports snapshot, transactional, and merge replication. No peer-to-peer, but is anyone actually surprised at that?

Comments closed

Transactional Replication in SQL Server on Linux

Published 2024-02-21 by Kevin Feasel

I finish up a series on SQL Server on Linux:

In this video, we will briefly cover the various forms of replication available in SQL Server, as well as what is in SQL Server on Linux. Then, we will create a simple publication and subscription using T-SQL.

As I joke about in the video, this is the video I expect to get the least traction on, if only because DBAs tend to run away from replication. If I were 20% more inclined toward Quixotic endeavors, I’d create an entire series on replication and show that it’s not magic and it’s only 70% as painful as most DBAs think, and even that’s because there’s a relatively limited amount of information out there on how things work.

Comments closed

Selective Column Replication in Postgres

Published 2024-02-19 by Kevin Feasel

Semab Tariq shows how to replicate a limited number of columns:

Selective column replication, a feature of logical replication in PostgreSQL, enables the selective transfer of data changes from one database to another. This feature offers flexibility by addressing bandwidth and storage optimization concerns. It allows users to choose specific columns to replicate rather than replicating entire tables, ensuring that only essential data is transferred. This selective approach reduces network traffic and storage space required for replication while maintaining data consistency across databases.
In this blog, I will demonstrate the process of replicating specific columns from a table using logical replication. Additionally, we will see how you can create multiple subscribers connected to a single publisher to replicate data across multiple locations.

Replication seems to be a lot easier to set up in Postgres than SQL Server, though check out the FAQ at the end for a few kicks in the pants around Postgres replication.

Comments closed

Merge Replication: Publisher Failed to Allocate a New Set of Identity Ranges

Published 2024-01-16 by Kevin Feasel

Taiyeb Zakir brings back bad memories for me:

Message: The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit

Click through for one solution. I’ve seen the error pop up in other cases where the column was already a bigint or the ranges were nowhere near the max and have had to mess around with range values to get things working again. My simple advice with merge replication and identity integers is to include something in addition to the identity column that guarantees uniqueness–for example, ID plus a column for subscriber name, if that makes sense in your situation–and don’t auto-assign ranges. Yeah, you may end up with five rows with ID = 1, but it doesn’t matter because there’s something making that row unique.

Comments closed

Cross-Database Sync in Azure SQL Database

Published 2024-01-15 by Kevin Feasel

Jose Manuel Jurado Diaz goes through a lot of work to replicate replication:

This week, we addressed a service request from a customer who wanted to keep two tables synchronized across different databases on the same Azure SQL Database server. Unfortunately, in Azure SQL Database, it’s not possible to directly call the database in the operation, for instance using the command select * from master.sys.sys_databases. For this reason, I’d like to share an alternative to tackle this limitation.

This is a way to do it, and it’s fine if there’s only one entry point for the data and you’re okay with writes taking more than twice as long. But this is a perfect use case for transactional replication. The only problem is, Azure SQL Database doesn’t support being a transactional replication publisher or distributor, only a (push) subscriber.

Comments closed

Logical Replication in Postgres

Published 2024-01-11 by Kevin Feasel

Muhammad Ali takes us through replication in Postgres:

PostgreSQL provides two main types of replication: Physical Streaming Replication and Logical Replication. In this blog post, we explore the details of Logical Replication in PostgreSQL. We will compare it with Physical Streaming Replication and discuss various aspects such as how it works, use case, when it’s useful, its limitations, and key points to keep in mind.

Logical replication is the Postgres equivalent to SQL Server replication. Read on to see how it works.

Comments closed

Group Replication in MySQL

Published 2023-08-29 by Kevin Feasel

Aisha Bukar continues a series on replication in MySQL:

MySQL Group replication is a remarkable feature introduced in MySQL 5.7 as a plugin. This technology allows you to create a reliable group of database servers. One of the most important features of MySQL’s group replication is that it allows these servers to store redundant data. This allows the database state to be replicated across multiple servers making it efficient in the situation where there is a server breakdown, the other servers in the cluster can agree to work together.
This technology is built on top of the MySQL InnoDB storage engine and employs a multi-source replication approach which we discussed in part 3 of the replication series. In this article, we’d be looking at an overview of the group replication technique, configuring and managing group replication, and also best practices for group replication. So, let’s get started!

Read on to see how it works and some recommendations around using it.

Comments closed