SQL Server Replication: Types + An Easy Way to Do It

In real-world applications, distributing data across multiple locations helps you enhance data availability, performance, and maintenance. It also reduces the need for your organization to rely on a centralized location to access data, which, if corrupted, can disrupt your daily workflow. In this context, SQL Server replication is crucial as it enables you to replicate data without manually performing copy/paste.

This article highlights the SQL Server Replication process with two easy-to-use methods for replicating data from one SQL Server to another.

What Is SQL Server Replication?

SQL Server replication is the process of transferring data and specific database objects from one database to another in the SQL Server environment. This migration helps maintain data consistency and integrity between the source and destination databases.

With SQL Server replication, you can effortlessly create identical copies of your database and synchronize any changes between the original and the new database. Synchronizing data helps you to manage data distribution efficiently, especially if your organization heavily relies on SQL Server for database management.

Use Cases for SQL Server Replication

Here are a few use cases of SQL Server replication:

• SQL Server replication can help you distribute data across multiple servers, improving its scalability and availability. This is useful when dealing with high data access and modification requirements.
• It allows you to load data from disparate sources into a centralized repository, simplifying data access and reliability.
• Although SQL Server Replication differs from the data backup process, it supports restoring replicated databases to the same server or database.
• SQL Server replication allows replicating data from various databases to be replicated in a data warehouse. Loading data into a data warehouse enables you to perform reporting and analytical functions on your data to produce impactful business insights.

Components Involved in Replicating SQL Server

Let’s learn about the components/terminologies involved in replicating SQL Server to better understand the data replication procedure.

Publisher

The Publisher is the original database instance that makes the data available for other MS SQL servers to access through SQL Server replication. A single publisher can have more than one publication, each representing a set of logical relations between objects and the data to replicate.

Distributor

The Distributor is an intermediate storage location that manages the distribution process of one or more Publications. Here are the two different ways to configure the Distributor:

• Local Distributor: Acting as a single database, the Distributor can work as a Publisher. This single database is also known as a local distributor.
• Remote Distributor: If the Distributor is already configured on a different server, it is known as a remote distributor. For a remote Distributor, each Distributor is associated with a single database, known as a distribution database.

Subscriber

A Subscriber is a database that receives and consumes the replication data from the Publisher. It can receive data from one or more publishers or publications. In addition, a Subscriber can pass data changes back to the Publisher or republish data to other Subscribers according to the replication design.

Publication

A Publication represents the logical collection of articles. It allows you to define and set up article characteristics at a higher level so that the article can inherit properties in a group.

Articles

Articles are the most basic unit involved in the SQL Server replication process, which includes tables, functions, procedures, and views. Based on the available filter options, articles can be scaled horizontally or vertically based on the available filter options. While replicating SQL Server, you can associate multiple Articles to a single object with some restrictions and limitations.

Subscription

A Subscription is a request for the replication data to be copied from the Publications to the Subscriber, defining the what, where, and when of the data transfer. It can be of two types:

• Push Subscription: Direct update of data from the Publisher to the Subscriber.
• Pull Subscription: A schedule according to which the Subscriber regularly monitors the Publisher for updates and changes in the subscription database.

Types of SQL Server Replication

According to the replication direction, SQL Replication can be one-way, one-to-many, many-to-one, or bidirectional. Let’s explore the SQL Server Replication types to understand what SQL Server offers.

Snapshot Replication

Snapshot replication allows you to copy the data as it is when you take the database snapshot, not allowing change tracking. This type of replication is helpful, especially when dealing with data that doesn’t update frequently. Here are the cases where snapshot replication can be a beneficial choice:

• When a database replica is older than the master database, it does not matter.
• When a large volume of data changes occur in a small period.

Transactional Replication

Transactional replication involves periodically automating data transfer from a master database to a database replica. This type of replication transfers all the transactions made and the final state of the database, making it possible to monitor the transaction history on the replica.

In transactional replication, a snapshot applies to the Subscriber, updating the database schema as changes are made to the data in the master database.

Merge Replication

In merge replication, specific triggers track the data changes made to the Publisher and the Subscriber. These changes exchange when a connection is established between the Publisher and the Subscriber, synchronizing all the rows in them. Merge replication can be helpful in many situations, including when:

• Each Subscriber requires a different data partition.
• Subscribers try to receive data, make changes offline, and synchronize changes with the Publisher.

Bidirectional

Bidirectional replication is a specific type of transactional replication that allows you to exchange changes between two servers. In this replication, each server publishes data and Subscribes to the publication from another server.

Updatable Subscriptions

Updatable Subscriptions are built on top of transactional replication, involving updating changes at the Publisher before transmitting/reflecting to other Subscribers. There are two types of Updatable Subscriptions:

• Immediate Updating: In this subscription, the connection between the Publisher and Subscriber updates the changes at the Subscriber.
• Queued Updating: In Queued Updating, a connection between the Publisher and the Subscriber is not required. Here, you can see the updates reflected while the Publisher and the Subscriber are offline.

SQL Server Replication Agents (Explain What Are Replication Agents)

SQL Server synchronizes data across various servers using automated pre-defined programs called replication agents. SQL Server replication agent runs as scheduled jobs under SQL Server Agent. 

Other ways of executing replication agents are using a command line or applications that use Replication Management Objects (RMO). You can use Replication Monitor or SQL Server Management Studio to monitor these replication agents.

Replication Snapshot Agent

The Replication Snapshot Agent provides a dataset for initial data synchronization between the Publisher and Subscriber databases. It captures the structure and initial content of the published articles, snapshot files, and records synchronization type in the distribution database.

Replication Distribution Agent

The Replication Distribution Agent applies the initial replication snapshot to the Subscriber database to track and record data changes in the Subscriber database. It is applicable only when you use replication snapshots and Transactional SQL Server replication.

Replication Log Reader Agent

The Replication Log Reader Agent reads the logs and moves transactions from the publication database's online transaction logs to the distribution database. The Distributor then delivers these transactions to each Subscriber.

Replication Queue Reader Agent

The Replication Queue Reader Agent moves any changes occurring at the Subscriber's end to the Publisher’s. It runs at the Distributor and uses transactional replication with queued updating options. Only one queue reader agent exists to serve all the Publishers and publications for a distribution database.

Replication Merge Agent

The Replication Merge Agent applies the initial snapshot to the Subscriber and establishes incremental data changes. It usually works with merge replication, connecting the Publisher and Subscriber and updating both as per the requirements. It can run at the Publisher to push subscriptions or at the Subscriber to pull subscriptions, synchronizing data bi-directionally.

How to Replicate SQL Server: 2 Easy Methods

Method 1: SQL Server Management Studio (SSMS)

In this method, you will explore how to perform database replication using SQL Server Management Studio (SSMS). SSMS is a software used to configure, manage, and administer all components of an SQL Server. Before getting started, ensure that you satisfy the prerequisites.

Prerequisites

• You must install Microsoft SQL Server with versions above 2008 on your system.
• SQL Server Management Studio (SSMS) is set up and running.

Step 1: Configure SQL Server Distributor

The first step in this process involves configuring SQL Server Distributer. Follow the steps below to do so:

• Establish a connection to your SQL Server instance in SSMS.
• On the Object Explorer, select Replication and click on Configure Distribution.

• A Distribution Configuration Wizard will appear on your screen. On that pop-up, click Next.

• Set the current instance as the Distributor or choose the existing instance that works as a Distributor. Click Next.

• Select the Snapshot folder where you want to store replicated data and click Next.

• Configure the replication distribution database by giving it a name and storage location. Click the Next button. A Publishers page will appear.
• Mention the Publishers that can access your data and click on Next.
• In the Wizard Actions popup, you can either run immediately or create a script for later execution.

• Review the settings and configuration options and click Finish to configure the Distributor.

Step 2: Configure SQL Server Publisher

• On the Object Explorer, expand the Replication folder. Click on Local Publications and select New Publication.

• Click Next on the New Publication Wizard.
• Select AdventureWorks2016 on the new Publication Database page and click Next.

• On the Publication Type window, select Transactional Replication and click Next.

• An Articles page will pop up. Select the articles you must include in the publication and click Next.
• You can review all the selected objects by selecting “Show only the checked articles in the list.” Click Next.

• The Filter Table Rows page will appear. On that page, you can specify filters that you want to appear for your articles. Click Next.

• Specify when the Snapshot Agent must start. Click Next.

• Specify the account to run the Snapshot Agent and click on Ok.

• On the Wizard Actions page, select the Create the publication option.

• Enter the name of the publication and click Finish. For example, “AdvWorks_Pub.”

Step 3: Configure SQL Server Subscriber

• Like the first two steps, expand the Replication option on the Object Explorer option.
• Select Local Subscriptions > New Subscriptions. The following window will showcase the general subscription details. Click Next.

• Select the publication on the next window and click Next. A Distribution Agent Location page will appear.
• Select “ Run all agents at the Distributor” and click Next.

• On the Subscribers window, click Add Subscriber, and select Add SQL Server Subscriber from the drop-down.

• Enter the name of the Subscriber instance on the Connect to Server dialog box and click Connect.
• After adding the Subscriber instance, the next step is to select the drop-down menu next to it. Select New Database, give it a name (AdventureWorks2016), and click Ok.

• A new subscription database will be created and registered to the Subscriber. You must ensure that the database has db_owner permission.
• Click the ellipsis(...) button on the Distribution Agent Security page. Type in all the process account details, then click OK.

• Click Finish, accepting default values on the remaining pages. A “Creating Subscription(s)...” page will appear, where you can check the connection's status. Click Close.

• Finally, use the Object Explorer option to select the Replication folder. In that folder, click Launch Replication Monitor to check the progress of the replication process.

Method 2: Using Airbyte (Recommended)

Another method of performing SQL Server replication is using no-code ELT platforms like Airbyte. It is a data integration and replication platform that allows you to effortlessly perform data integration within minutes. Airbyte provides 350+ pre-built connectors, including SQL Server, to extract data from and load it into a destination of your choice.

If the connector you seek is not present in the existing options, you can create your custom connector using the Connector Development Kit (CDK). To perform SQL Server replication by leveraging Airbyte, follow the steps below:

Step 1: Configure SQL Server as a Source

• First, you must log in to your Airbyte account.
• After logging in, a dashboard will appear.

• Click on the Sources option from the left navigation pane. In the search connector box of the new page, search for SQL. Click on the Microsoft SQL Server (MSSQL) option.

• A new source page will appear. On that page, enter all the details of the SQL Server database from which you want to replicate the data, configuring the source.

Step 2: Configure SQL Server as a Destination

• After configuring the source, you can select the Destinations option from the left navigation pane. In the search connector box, search for “SQL” and click on MS SQL Server.

• A New destination page will pop up. On that page, mention all the details of your destination SQL server, where you want to replicate the data and configure the destination.

SQL Server Replication Security Best Practices

Professionals in the real world follow multiple database replication SQL Server best practices that you can follow to enhance your data replication process. This section highlights the prominent ones.

Use Windows Authentication

It is preferable to run each replication agent under a different Windows account. You can use Windows authentication for all replication agent connections, adding a local Windows account for each agent at the appropriate nodes.

For example, you can allocate a Windows account for the distribution agent performing push subscriptions to the Distributor and the Subscriber.

Implement Least-Privilege Principle

Grant Publication Access List (PAL) accounts only for the permissions required to perform replication tasks, following the principle of least privilege. You must ensure that logins are not added to any fixed server roles that are not required to perform replication tasks.

Secure Communication Channels with SSL/TLS

Establishing secure communication channels between computers in a replication topology is one of the most essential steps to secure your data during replication. It involves encrypting connections using industry-standard methods, such as IP Security (IPSEC) and Transport Layer Security (TLS), also referred to as Secure Sockets Layer (SSL).

Use Strong Passwords for SQL Accounts

To ensure data security, you must have a strong SQL account password. A strong password can have more than eight characters, combining letters, numbers, and special characters, and must not be found in the dictionary, among other things. This password can contain up to 128 characters in total.

Encrypt Sensitive Data

Encrypting sensitive data is essential to protecting your information from compromise. If you use TLS to secure connections between computers in the replication topology, mention the -EncryptionLevel parameter as “1” or “2.” Unlike ”2,” value “1” signifies that the data is encrypted but not TLS/SSL certified by a trusted user. For this reason, selecting “2” is often recommended.

Grant Only the Required Permissions to Each Agent

You must grant only the permissions each agent requires to perform the database replication SQL Server tasks. For example, a Snapshot Agent must be a member of the db_owner role in the distribution database and have read, write, and modify permissions on the snapshot share.

Implement Row-Level Security

Row-level security (RLS) lets you use execution context or group membership to control access to the rows of your database table. It helps you restrict users from accessing certain rows containing sensitive information you want to secure. You can use CREATE SECURITY POLICY T-SQL statement with inline functions to implement RLS.

Summary

Replicating data from one SQL Server to another makes managing your data and distributing workloads easier. You can use SQL Server Management Studio (SSMS) to replicate SQL Server. However, for practical use cases, you might face multiple challenges performing the steps involved in SQL Server replication using SSMS.

This is where SaaS-based data replication tools like Airbyte can help you enhance your data transfer journey. With its pre-built connector functionality, you can configure any source and destination in minutes and perform data replication.

FAQs

Q. What Are the Different Types of Replications in an SQL Server?

There are five types of SQL Server replications, including

• Snapshot Replication.
• Transactional Replication
• Merge Replication
• Bidirectional Replication
• Updated Subscriptions

Q. What is Mirroring vs Replication in SQL Server?

In SQL Server, mirroring and replication are techniques for managing data, but both serve different purposes. Mirroring is useful for ensuring high availability and disaster control, while replication focuses on data distribution and scalability.

Q. How to Speed up SQL Server Replication?

Here are some of the ways you can speed up the SQL Server Replication process:

• Prevent memory shortages and disk paging by allocating minimum and maximum memory for the SQL Server Database Engine.
• To reduce the write time, utilize separate disk drives for database and transaction log files.
• While managing many Subscribers, use pull subscriptions instead of push to enhance performance by offloading agent processing from Distributors to Subscribers.