Press "Enter" to skip to content

Category: Replication

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

GTIDs for Replication in MySQL

Published 2023-08-09 by Kevin Feasel

Aisha Bukar continues a series on replication in MySQL:

Welcome back to another replication series! As a quick reminder, we explored various methods of using MySQL’s replication capabilities in our previous discussions. Initially, we employed the traditional binary-log-based replication approach to set up our replication servers. This involved tracking the binary log file and its positions to facilitate replication.

In this article, we will dive into a more recent and acceptable approach to creating replication – using the Global Transaction Identifiers (GTID) based replication.

Click through to understand how they work and the trade-offs you’ll need to make regarding them.

Comments closed

Multi-Source Replication in MySQL

Published 2023-07-19 by Kevin Feasel

Aisha Bukar continues a series on replication in MySQL:

MySQL’s multi-source replication allows a replica server to receive data from multiple source servers. Let’s say you have a replica server at your workplace, and there are multiple source servers in different locations, you need a way to directly receive data from these source servers to your replica server. This is where the multi-source replication technique comes into play. It allows you to efficiently gather data from various sources and consolidate it on your replica server.

Note that this is quite different from merge replication or peer-to-peer replication in SQL Server and there are some limits to its capabilities. That said, I could see this being really useful for performing ELT into a warehouse: use replication to keep the staging tables in sync and then run a job to perform transformations into facts and dimensions periodically.

Comments closed

Data Syncs between Azure SQL DB and Amazon RDS

Published 2023-07-19 by Kevin Feasel

Joey D’Antoni crosses clouds:

A while back, a client, who host user-facing databases in Azure SQL Database, had a novel problem. One of their customers, had all of their infrastructure in AWS, and wanted to be able to access my client’s data in an RDS instance. There aren’t many options for doing this–replication doesn’t work with Azure SQL Database as a publisher because there’s no SQL Agent. Managed Instance would have been messy from a network perspective, as well as cost prohibitive compared to Azure SQL DB serverless. Even using an ETL tool like Azure Data Factory would have worked, but would have required a rather large amount of dev cycles to check for changed data. Enter Azure Data Sync.

Read on to see what Azure Data Sync is and how it helps solve this problem.

Comments closed

Azure DataSync: Cannot Insert NULL Value

Published 2023-07-07 by Kevin Feasel

Jose Manuel Jurado Diaz does some sleuthing:

In this blog article, we will delve into a common error encountered when synchronizing data with Azure SQL DataSync. We’ll explore the error message “Error #1: SqlException Error Code: -2146232060 – SqlError Number: 515, Message: Cannot insert the value NULL into column ‘ID’, table ‘dbo.Customers’; column does not allow nulls. INSERT fails. SqlError Number: 3621, Message: The statement has been terminated.” We will provide a detailed explanation of the error and its possible causes, followed by a T-SQL code snippet that reproduces the error scenario.

Click through for four possible causes.

Comments closed

Replication from 2000 to 2012

Published 2023-01-12 by Kevin Feasel

Deepthi Gogrui pulls a fast one:

The scenario that I faced was little challenging. We had SQL Server 2012 production server replicating data to a Server 2000 which is used for reporting purposes. Subscriber SQL Server 2000 used by the reporting team were not ready to upgrade the Server as they need to rewrite their entire application as it was using vb6 code. They need a strategy where the data can still be replicated without upgrading the Server.

As I researched, I found that it is not compatible version but planned to test the replication to see if somehow it works. I tested the replication between SQL Server 2012 as a publisher and SQL Server 2000 as subscriber. I was able to setup the transactional replication between the servers for the database but found during the initial initialization snapshot, the ANSI_PADDING setting in the snapshot generated .sch files caused the issue while the distribution job runs. 

Read on for the solution. This turned out to work despite Microsoft’s official guidance that they only support replication between SQL Server instances within two versions of each other.

Comments closed