Replication in SQL Server

Replication  is a way of distribution of database and their objects from one master database to one or more recipient databases which can be on the same server or on the remote server.It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, we can distribute data to different locations and to remote users over local and wide area networks.It is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database.

Replication Components

SQL Server replication consists of three components: The Publisher, the Distributor and the Subscriber. These components act on articles that are defined within publications and subscriptions.

Article

For each SQL Server object that should be replicated, a replication article needs to be defined. Each article corresponds to a single SQL Server object, or a subset of an object. The objects that are replicated most often are tables, views and stored procedures. The properties of an article determine whether the article contains the entire object, or if a filtered subset of the objects makes up the replicated article.

Publication

A group of articles that logically belong together can be combined into a publication. The publication has options defined that apply to all the articles in that publication. The main option defined by a publication is the type of replication that is to be used.

Publisher

The SQL Server instance that makes a publication available for replication is called the publisher.

The publisher monitors all articles for changes, and makes information about those changes available to the distributor.

Distributor

The distributor is the SQL Server instance keeps track of all subscribers and all published changes and makes sure that each subscriber gets notified of each change. Most of the changes are tracked in a distribution database. The distributor can be a separate SQL Server instance, but often the distribution service runs on the same machine as the publisher.

Subscriber

The subscriber is the SQL Server instance that receives all the published information through subscriptions. 

Subscription

A subscription is the counterpart of the publication. A subscription defines which server (subscriber) is to receive the updates published in a publication. Each subscription creates a link between one publication and one subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the distributor directly updates the data in the subscriber database. In a pull subscription, the subscriber asks the distributor regularly if any new changes are available, and then updates the data itself.

Types of replication:-

1.          Snapshot Replication
2.          Transactional Replication
3.          Merge Replication

Snapshot Replication:-

Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time. Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements. Therefore this replication is advisable only when the size of the database is small.

Transactional Replication:-

Transactional replication works on a transaction basis. In Transactional replication only the committed changes are logged into the subscribers. Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. It is the best way of replication to work one way only.

Merge Replication:-

Merge replication was designed to allow the changes to the data to be made on the publisher as well as the subscriber side. In Merge Transaction, 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 have many built in options to resolve those conflicts.