Press "Enter" to skip to content

Category: Replication

Synchronous and Asynchronous Replication in Postgres

Published 2024-05-20 by Kevin Feasel

Semab Tariq takes us through a pair of replication options:

In the world of database replication, choosing between synchronous and asynchronous methods can have a big impact on how reliable, consistent, and fast your data is.

This blog dives into what these methods are, how they work, and when you might want to use one over the other. Whether you’re trying to keep your data super safe or just want it to move quickly, we’ll break down everything you need to know about synchronous and asynchronous replication in PostgreSQL.

Read on for a quick overview of streaming replication and the differences between asynchronous and synchronous options.

Comments closed

A Primer on Transactional Replication

Published 2024-04-30 by Kevin Feasel

Steve Stedman talks transactional replication:

Ensuring that your databases are synchronized across different locations with minimal delay is not just a convenience—it’s a necessity. This is where transactional replication in SQL Server shines, making it a pivotal strategy for systems that require real-time data replication with high consistency. Our latest video, “Transactional Replication in SQL Server”, dives deep into this topic, offering insights and visual walkthroughs that are invaluable for database administrators and developers.

Click through for the video and how the pieces fit together for transactional replication at a high level.

Comments closed

Transactional Replication Slow: Check Missing Indexes

Published 2024-04-12 by Kevin Feasel

Lori Brown provides a good tip:

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.

Comments closed

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