Professional SQL Server High Availability and Disaster Recovery: Implement tried-and-true high availability and disaster recovery solutions with SQL Server

In the previous lesson, we were introduced to replication and its types. We also took a deep dive into how to configure snapshot replication. In this lesson, we will continue our tour of replication and look at transactional replication and peer-to-peer transactional replication.

This lesson teaches you how to configure, troubleshoot, monitor, and optimize transactional replication. Transactional replication is the most common type of replication used to synchronize data between two databases. It is often used to load-balance read and write operations and is also a cost-effective solution for high availability.

In this section, we'll look at how transactional replication works. The following diagram describes how transactional replication works:

Figure 2.1: Transactional replication example

This diagram shows the AdventureWorks database being replicated using transactional replication. Once transactional replication is configured, the snapshot agent, log reader agent, and distribution agent are created as part of the replication. The agents then work together, as described here, to replicate the transactions.

The snapshot agent is used once to initialize the subscriber database schema with that of the publisher. This isn't shown in the diagram, however, it's the first thing that happens when the transactional replication starts.

The subscriber database can be initialized using the publisher database backup, which is covered later in this lesson.

Once initialized, the transactions are replicated as follows:

We'll now look at how to configure or set up transactional replication between two databases using T-SQL.

An Azure SQL database is a database-as-a-service offering from Microsoft Azure. An Azure SQL database can be configured as a subscriber to an on-premises SQL Server publisher. This is one of best methods to use for migrating an on-premises database to an Azure SQL database. Moreover, the Azure SQL database subscriber can also be used as a backup or to offload reads from the publisher.

Peer-to-peer replication, also referred to as master-master replication, is built upon transactional replication in which changes at the subscriber are also replicated to the publisher.

A peer-to-peer topology consists of two or more databases where a change in each node (database) gets replicated at every other node (database) in the topology. Peer-to-peer replication therefore provides a scalable high availability solution.

A typical peer-to-peer transactional replication topology is shown in the following diagram:

Figure 2.36: P2P replication example

Node A and Node B are in a peer-to-peer replication. Any changes made at Node A are replicated to Node B and any changes made at Node B are replicated to Node A.

This topology has the following benefits:

In this section, we'll configure peer-to-peer transactional replication. The topology is as follows:

Figure 2.37: P2P Replication example

The preceding diagram illustrates peer-to-peer topology with two nodes with a push subscription:

Configuring P2P replication consists of the following steps:

The following steps show how the publication properties can be changed, as discussed for the standard transactional replication. We'll now look at how to remove and add an article in a P2P replication.

In this lesson, we have learned how to configure transactional replication and peer-to-peer transactional replication.

We learned how the different agents, that is, snapshot agent, log reader agent, and distributor agent, work together to replicate the data from the publisher database to the subscriber database.

We then saw how to modify the properties of an existing publication and subscription and how to add and remove articles to/from an existing publication.

In the next lesson, we'll learn how to monitor and troubleshoot transactional replication.