Replication In SQL Server: Part One

Overview

Earlier, most of the applications like standalone applications were running on a single centralized server responding to multiple users, working in different locations areas.

With the help of replication in SQL Server we can achieve replication of data on different servers keeping in mind the data availability all the time when a user connects to a database or to a particular table. Using SQL Server replication, you can distribute data to wide area network, mobile users, and remote, dial up connection and so on.

WHAT is SQL SERVER REPLICATION?

Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds).

WHY SQL SERVER REPLICATION?

SQL Server replication allows database administrators to distribute data to various servers throughout an organization. You may wish to implement replication in your organization for a number of reasons, such as:

• Load balancing: Replication allows you to disseminate your data to a number of servers and then distribute the query load among those servers.
  
  
• Offline processing: You may wish to manipulate data from your database on a machine that is not always connected to the network.
  
  
• Redundancy:. Replication allows you to build a fail-over database server that’s ready to pick up the processing load at a moment’s notice.
  

SQL SERVER REPLICATION ARCHITECTURE



This is a simple plain architecture of sql server replication architecture, here an publisher in which the database resides. Distributor is which takes ann DML,DDL statements and pass on to subscribers and execute on subscriber . Advisable, to keep publisher, distributor and subscriber on different different servers in order to achieve better performance as database size increases in TB you will see the difference by keeping these components on different servers.

REPLICATION COMPONENTS

SQL Server replication is based on publisher i.e. sender and subscriber i.e. receiver. Let’s take a look on various components that are used in replication:

• ARTICLE: Includes actual database objects like tables,views,indexes etc. .
  
  
• PUBLISHER: It’s a source database where actual replication starts. It makes data available for replication. It tells what they are going to publish throughout the publication.
  
  
• PUBLICATION: A group of articles are called publication. An article can’t be distributed individually. Required publication.
  
  
• DISTRIBUTOR: It’s an intermediate between an publisher and subscriber. It receives transactions stores and forwards it to subscriber as you can see in the architecture below.
  
  
• SUBSCRIBER : It is an destination database where actual replication ends or happens
  
  
• SUBSCRIPTION: It is a request by subscriber to receive a publication.

REPLICATION TYPES

• Snapshot Replication
  
  Snapshot replication creates a complete copy of the replicated objects and their data each time it runs.The snapshot folder is a shared folder location that has to be set up on the distributor when enabling replication. Each participant in a replication setup needs to have access to the snapshot folder.
  
  Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements. All other types of replication use - by default - a single replication snapshot to sync up all subscribers with the distributor only during the initial setup.
  
  
• Transactional Replication
  
  Transactional replication works, as the name suggests, on a transaction basis. Every committed transaction gets scanned for changes applied to replication articles. Scanning of the changes is done by a log reader agent, which reads through the transaction log of the publisher database. If there are changes affecting a published object, those changes get logged on the distributor in the distribution database. From there they make their way to the subscribers.
  
  Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. While there are several options to allow for bidirectional data movement, transactional replication was originally designed to work one way only.
  
  
• Merge Replication
  
  Merge replication was designed from the beginning to allow for changes to the data to be made on the publisher as well as the subscriber side. Merge replication also allows for disconnected scenarios, where a subscriber might not be connected during the day. That subscriber would synchronize after reconnecting in the evening. If a row gets updated in two different places at the same time, a conflict occurs. Merge replication comes with several built in options to resolve those conflicts.
  
  
• Peer to Peer Replication
  
  Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transaction ally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.