What is MS SQL Server Replication and How Its Works

Summary: in this tutorial, you will learn what is Microsoft SQL Server (MS SQL Server) Replication and an overview of how its works. In this article, you will understand the total SQL Server replication topography together with parts, internals, and therefore the SQL to bind it all together. I hope when you complete reading this article, then you‘ll understand in a better way.

OVERVIEW: What is MS SQL Server replication?

SQL Server Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing b/w the databases to keep up consistency. Use replication to distribute data to completely different locations and to remote or mobile users local and wide area networks (WAN), wireless connections, dial-up connections, and the Internet, etc.

MS SQL Server is a database management software that can be installed on Windows Server operating systems. Databases are very important for many software solutions and all types of companies use databases that can be centralized and distributed. Availability of databases and data relevance is critical for businesses, replication of databases a necessity and making the backup. With MS SQL Server replication, it is possible to create an identical copy of your primary database or synchronize changes between multiple databases and integrity and maintain data consistency.

Transactional replication is frequently utilized in server-to-server situations that need high output, together with up availability and measurability information depositing and reporting group action information from multiple sites, group action heterogeneous information, and offloading execution. Merge replication is primarily designed for mobile applications or distributed server applications that have attainable information conflicts.

In common situations include exchanging information with mobile users, client purpose of sale (POS) applications, and integration of knowledge from multiple sites. photograph replication is employed to supply the initial information set for transactional and merge replication. it also can be used once complete refreshes of knowledge are applicable.

With these 3 types of replication, SQL Server provides a robust and versatile system for synchronizing information across your enterprise. Replication to SQLCE 4.0 and SQLCE 3.5 is supported on each Windows Server 2012 and Windows 8.

Types of MS SQL Server Replication

Microsoft SQL Server provides the following types of replication to be used in distributed applications:

Type Description
Transactional Replication Changes at the Publisher are delivered to the Subscriber as they occur (in close to real-time). The data changes are applied to the Subscriber in the same order and within the similar transaction boundaries as they occurred on the publisher.
Merge Replication Data also can be changed on each the Publisher & Subscriber and are tracked with triggers. The Subscriber synchronizes with the Publisher once connected to the network and exchanges all rows that have modified between the Publisher and Subscriber since the last time synchronization occurred.
Snapshot Replication Applies a snapshot from the Publisher to the Subscriber, that distributes specifically because it appears at a specific moment in time, and doesn’t monitor for updates to the data. once synchronization happens, the whole snapshot is generated and sent to Subscribers.
Peer-to-peer Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in close to real-time between multiple server instances.
Bidirectional Bidirectional transactional replication is a specific transactional replication topology that enables two servers to exchange changes with each other: every server publishes data and then subscribes to a publication with similar data from the other server.
Updatable Subscriptions Built on the foundation of transactional replication, one data is updated at a Subscriber for an updatable subscription, it’s initial or first propagated to the Publisher and then propagated to alternative or other Subscribers.

MS SQL Server replication is a technology for synchronizing and copying data between databases regularly or continuously at scheduled intervals. As for the replication direction, MS SQL Server replication can be unidirectional, one-to-many, many-to-one, and bidirectional. There are basically 4 types of MS SQL Server replication such as transactional replication, merge replication, snapshot replication, and peer-to-peer replication.

Transactional Replication

Transactional replication in MS SQL Server is the periodic automated replication once the data is distributed from a master database to a database replica in near-real-time or real-time. Transactional replication is a lot of advanced or complex than snapshot replication. Not only is the final state of a database replicated, but however all made transactions are also replicated, but also makes it possible to observe the whole transaction history on the database replica. At the start of the transactional replication process or method, a snapshot is applied to the Subscriber and then data is continuously transferred from a master database to a database replica after being changed or modified. Transactional replication is widely used as one-way or unidirectional replication.
MS SQL Server Transactional Replication
Examples and use cases for transactional replication:

  • Creating a database server with a database replica is used to perform the failover if the main database server fails.
  • Getting reports concerning operations performed in branch offices by using the multiple publishers in branch offices and one subscriber in the headquarters or main office.
  • Changes ought to be replicated as soon as possible after they happen.
  • Data on a source database is modified or changed frequently.

Merge Replication

Merge replication is a type of bidirectional replication that’s sometimes or usually used in server-to-client environments for synchronizing data across database servers once they can’t be connected continuously. When the network connection is established or settled between both database servers, then merge replication agents detect changes created or made on both databases and modify or changed databases to synchronize and update their state. the Merge replication is similar or identical to transactional replication, but data is replicated from the Publisher to the Subscriber and inversely.
merge replication
This type of database replication is the most complicated of all MS SQL Server replication types and is very rarely used. For instance, merge replication can be utilized by multiple peer stores that work with a shared warehouse. Every store is allowable to change the information in the warehouse database and at a similar time, all stores must have the updated state of their databases after the shipment of products or delivery of supplies to the warehouse. furthermore, the Merge replication can be utilized in those cases where the updated information or data must be available for the central (or main) database and branch databases simultaneously.

Snapshot Replication

Snapshot replication is used to replicate data precisely as it seems at the moment when the database snapshot was created. This replication type will be used when data is changed or modified infrequently once it’s not crucial to have a database replica that is older than a master database, or a large volume or an outsized of changes is made within a short period of time. furthermore, No modification tracking is performed for snapshot replication. therefore, for example, snapshot replication will be used when exchange rates or price lists are updated once per day and should be distributed from the main server to servers in branch offices.
Microsoft SQL snapshot replication

Peer-To-Peer Replication

Peer-to-peer replication is basically used to replicate database data to multiple subscribers at a constant or same time. This MS SQL Server replication kind is often used when your database servers are distributed across the world. furthermore, Changes are often created on any of the database servers. Therefore, the changes are propagated to all database servers. Peer-to-peer replication will facilitate scale out an application that uses a database. The most working principle or rules is based on transactional replication.
Peer-To-Peer Replication
Furthermore, you can see, below how MS SQL Server peer-to-peer replication can be used between the database servers that are distributed across the world.
MS SQL Server peer-to-peer replication

Terminology Used for MS SQL Server Replication.

Before diving into configuration and setting up MS SQL Server replication, let’s 1st in brief go over the main important terms and the replication models.
Articles are the basic units to be replicated, like tables, functions, procedures, and views. Articles can be scaled horizontally or vertically by using filters. Multiple articles can be created for the identical object.

A publication is a logical collection of articles. This is the final set of entities from the database directed for replication.

A filter is a set of circumstances for an article. MS SQL Server replication permits you to use filters and selects custom entities for replication, which, as a result, redundancy, reduces traffic, and also the amount of data stored in a database replica. For Instance, you can select only the most delicate tables and fields by using filters and replicate only this data.
MS SQL Server Replication
There are three main important roles in MS SQL Server database replication: distributor, publisher, and subscriber.

A distributor is an MS SQL database instance configured or designed for collecting transactions from publications and for distributing them to subscribers. A distributor database can be recognized as the Publisher and the Distributor at once. furthermore, a distributor behaves like a database for storing replicated transactions. In the local distributor model, only a single MS SQL instance runs both the Publisher & Distributor. A remote distributor model can be utilized when you need subscribers to be configured to use a single Microsoft SQL Server instance to get different publications. during this model, the Publisher & Distributor run on different servers.

A publisher is that the main database copy on that publication is configured, making data available to other MS SQL servers that are configured to be utilized in the replication process. The Publisher can have more than one(1) publication.

A subscriber is also a database that receives the replicated data from a publication. One(1) Subscriber can receive data/information from more than one publisher and publication. A single-subscriber model is utilized when there’s one Subscriber. A multi-subscriber model is utilized when multiple subscribers are connected to a single publication.

Agents are MS SQL Server components that can act as background services for the relational database management systems(RDBMS) and are used to schedule automated execution of jobs, like MS SQL database backup and replication. There are 5 types of agents: Distribution Agent, Snapshot Agent, Log Reader Agent, Queue Reader Agent, and Merge Agent.

Subscription is a request for a copy of a publication that should be delivered to the Subscriber. The subscription is utilized to define the publication data that must be received, and when and where this data will be received. There are 2 kinds of subscriptions — push and pull.

Push subscription: changed or modified data is forcibly transmitted from a Distributor to the Subscriber database. No request from the Subscriber is required.

Pull subscription: the changed or modified data made on the Publisher is requested by a Subscriber. The Agent can run only on the side of the Subscriber.
A subscription database is a target database in the MS SQL Server replication model.

Metadata is that the data utilized to describe entities of the database. There’s a wide range of built-in metadata functions that allow you to return information regarding the MS SQL Server instance, database instances, and database entities.

In the multiple publishers–multiple subscribers model, the Publisher will act because of the Subscriber one of the MS SQL servers. ensure, you avoid any potential update conflicts when utilized this MS SQL Server replication model.

Requirements for MS SQL Server Replication

The following ports should be opened for Inner or inbound traffic:
TCP 1433, 1434, 2382, 2383, 135, 443, and 80;
UDP 1434.
Don’t forget to configure or set up the Windows firewall and enable the appropriate ports for inner or inbound traffic on every host before installing MS SQL Server. therefore, the hosts attended in MS SQL server replication must resolve each other by a hostname.
Before configuring MS SQL replication, the following software should be installed for MS SQL Server:
.NET Framework – its a collection of libraries or a set of libraries
MS SQL Server – the database SQL Server software
MS SQL Server Management Studio (SSMS) – software for managing MS SQL databases with the interface GUI (graphical user interface).

Basic Factors Recommendations for MS SQL Database Replication

Before configuring the environment or setting for MS SQL Server, here are few factors to consider:

  • There are limitations to Identity fields and triggers.
  • Publications will contain only tables with the primary key.
  • It is suggested that creating snapshots scheduling not be used for large databases due to the consumption of computing resources.
  • Be careful when changing data in the database replica residing on the Subscriber, because as a result when a transaction that modifies data is coming and that data has been edited or deleted, replication can stop until you resolve this issue.