SQL Server Transactional Replication: Part 1, Introduction and Basics

“I want to look at my data, and I want to look at it IN REAL TIME!”

No matter how irrational or unreasonable this request is, it’s a very common one in today’s tech world.  It often leads to a lot of pain as business users with basic SQL knowledge write poor queries that grind the performance of the main application to a halt.  One of the ways to solve for that is to set up a “reporting copy” of your database and keep it in sync.

There are several ways to do this, but in this series, we’re going to focus on transactional replication, since it’s a tool that allows for a lot of flexibility in accommodating these reporting needs without spending gobs of money on enterprise edition licenses, or copying the whole database when just a fraction of it is needed.

SQL Server transactional replication is an extremely powerful tool.  But just like a framing hammer, if you use this tool without keeping some safety tips in mind your, neighbors may hear your exclamations when things go wrong.  If you’re like me, these exclamations may come in the form of four-letter words, often at higher-than-appropriate volume.  

The goal of this series of blog posts is to help you move away from those four-letter exclamations and develop the confidence to analyze your scenario and create the approach that best fits your needs.  We’ll also help you find the right approach when you run into one of those rage-inducing error messages that may pop up.  Whether you’ve been using replication for a while or are brand new, you’ll find valuable info here to help you take your replication skills to the next level.

Limitations

First, we need to outline the major limitations of SQL Server transactional replication.  These are deal-breakers.

Primary Keys — Gotta Have ‘Em

Each table you decide to replicate must have a primary key defined on it.  The primary key is what allows replication to affect one and only one row. It’s how it maintains data integrity. If you don’t have a primary key, the table cannot be replicated using transactional replication.

Truncate Table — Not Allowed

Because SQL Server transactional replication works on a row-by-row basis (something we will cover later), you cannot truncate a table that is a published. The truncate table command operates not by removing rows from a table, but at a much deeper level that transactional replication is not able to track. This limitation is a major gotcha, because it requires an understanding of your applications and processes first. There’s no way for replication to know that a table you’ve added will get truncated. Instead, your application will throw an error when the truncate is executed against the replicated table, and it will be prevented.  This can cause major issues, so you must ensure this table is not the target of any truncate command. You can use extended events or a trace session to monitor the table over several weeks to verify your assumption.

Temporal Tables — Tricky

While you can replicate temporal tables, it is a very tricky area and can’t be done as easily as with normal tables. Because the history table has no primary key, there is no direct way to replicate a temporal table and include all of its history. Instead, the subscriber will maintain its own set of history based on the replicated transactions, and the timestamps on the historical records will not match.

Topology

Next, we need to define the basic topology using the terms found in the official documentation so that if you do go search for help, you can find the right terms. Let’s start with the three roles in a replication topology.

Publisher

The publisher is the server that houses the database where all the interesting data is stored. It’s the source of the data you want to make a copy of. The term was coined back when print publications were much more popular than they are now, so the publisher was analogous to companies like The New York Times or AutoWeek. Here in the present, there are tons of newer content platforms that fit the analogy, like YouTube or TikTok. A publication contains the itemized list of objects (tables, views, stored procedures, etc.) that are to be replicated from the publisher.

Subscriber

The subscriber describes any server that is set up to receive the data that the publisher is providing. When you subscribe to a YouTube channel, a podcast or a magazine, the content is delivered to you, and just like a YouTube channel can have multiple subscribers, so can a publication. Each publication can have multiple subscribers, meaning there are multiple copies of the data.

Distributor

It’s the distributor’s job to get the data from the publisher and deliver it to the subscriber.  Trucking companies often hauled newspapers from the printing presses to the distribution centers, and from there they would get delivered to stores, homes and those funny-looking newspaper dispensers. Companies like YouTube and TikTok typically act as their own distributors, pushing content over the Internet to subscribers all over the world. The server designated as the distributor will have a special database created on it, the distribution database. 

Each of these roles can be on an independent server or in nearly any combination. One server could be a publisher and distributor, sending out data to multiple remote subscribers. Or a subscriber could act as a distributor, preventing additional overhead on an already burdened application database server. It’s even possible for the publisher, distributor and subscriber all to be in the same place. Boom! I know, that’s the sound of your brain exploding with all the possibilities.

Article

An article is any object you have identified for replication. It can be a table, view, stored procedure or function. 

Publication

A publication is a group of one or more articles. All of the transactions that apply to articles in one publication will be kept consistent with each other, so if you have tables with foreign key relationships. it is best to keep them in the same publication. The same article can be placed in multiple publications, but if this is done the publications should not have the same subscriber, as it would receive the data twice and result in errors.

Agent

An agent is a process that handles replication tasks. The three main ones are snapshot agent, log reader agent and distribution agent, as described below.

Snapshot Agent

The snapshot agent is responsible for creating the scripts that will handle the initial load of data from the publisher to the subscriber. To do this, it takes a “snapshot” of the data and exports the object definitions (i.e., CREATE TABLE) and the data itself to somewhere on your filesystem. There will be one snapshot agent per publication.

Log Reader Agent

The log reader agent is responsible for reading your database transaction log, identifying the commands to be replicated and storing them in the distribution database on your distributor. There will be one — and only one — log reader agent per published database (not per publication). 

Distribution Agent

The distribution agent is the agent responsible for taking the commands stored in the distribution database, identifying which subscribers should receive them and then distributing the commands amongst the subscribers. 

So there you have it, the basic definitions used within a SQL Server transactional replication topology. There’s a lot more to come, and many avenues and opportunities for new expletives to be discovered as you begin to set up your topology and work within its limitations.

SQL Tailor Consulting

Need more information on SQL Server transactional replication? Have another SQL Server question? Contact us for a free consultation today!