Tuesday, January 1, 2013

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.


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.


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.


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.


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.


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


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.


Log Shipping in SQL Server

Log Shipping:- Log Shipping is a process of keeping the latest copy of the database of the primary database server on the secondary database server with the help of transaction logs backup. It is done on a regular basis to minimize the loss of the database in case of database fail over. It is a continuous process in which the backup of the transaction logs has been taken on the regular basics and then they are  transported to the secondary server. In the secondary server, the transactions backups are restored to make the database on the secondary database server as the exact replica of the primary database or near to the current primary database.

This process can have three components:-

Primary database Server:- This server contains the primary database .The backup of the transaction logs of the Primary database is taken on this server and send to the Secondary database server.

Secondary database Server:- This server contains the secondary database . There can be one or more than one secondary database server. Here backup of the primary database server transactions log are applied to the secondary database server databases individuality so that they have database which is close to the Primary server database.

Monitor Server:- This optional server, monitor the log shipping process by recording the history and the status of the the backups of the transaction logs. It also monitor the restore operations on the secondary server. It can also raise alarm if the process is not completed as per scheduled.

Rebuild And Reorganization of Indexes

Rebuild and  Reorganization of Indexes:- SQL Server has the ability of maintaining the indexes whenever we makes changes (update, Insert, Delete) in the tables. Over a period of time, the may causes the fragmentation on the table in which  the logical ordering based on the key value pairs does not match with the physical ordering inside the data files. This causes the degradation of the performance of the SQL Query.To solve this problem of fragmentation, we use rebuilding or reorganization of the indexes.

In case of Rebuilding, it drop the particular index and again recreate it.It removes fragmentation, reclaims the disk space by compacting the pages based on the specified or existing fill factor setting, and again reorders the index rows in those contiguous pages. We can rebuild all the indexes of the table within a single transaction by specifying the ALL with it.

ALTER INDEX [Indexname] ON [tablename] REBUILD


In case of Reorganization, it defragments the leaf level nodes of indexes by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.  It uses minimal system resources and also compact the index pages

ALTER INDEX [Indexname] ON [tablename] REORGANIZE


Rebuild should be used when the fragmentation index is greater than 30% and reorganization option should be used when the fragmentation index is between 5% and 30%. Rebuilding of an Index can be done online or offline. But to achieve the availability of the index, rebuilding should be done online. Reorganization can be done online.

Related Article

Fragmentation in SQL Server

Fragmentation:- Fragmentation can be defined as condition where data is stored in a non continuous manner. In can be defined into two types

1. Internal Fragmentation
2. External Fragmentation

Internal Fragmentation:- In this fragmentation, there exists a space between the different records within a page. This is caused due to the Insert, delete or Update process and due to this Index takes more space than it needs to and it result in more read operation during scanning. It can also be caused due to the low value of fill factor of the page which determine how much % of the page should be used for storing the records. 

External Fragmentation:- In this fragmentation, the extents of the table is not physically stored continuously on the disk which causes the jump from one extent to another extent which takes longer time. 

Both the fragmentation can be resolved by Rebuilding or Reorganization of the indexes of the tables. 

For Rebuilding or Reorganization of the indexes , please refers the below link: