Thursday, November 25, 2010

Replication in SQL Server

Replication is one of typical functionality in SQL Server. Basically Replication lets you to copying and distributing data and database objects like table, stored procedure, view and UDF to one publisher database to multiple subscription database/server on different location.


There are three types of replication

1. Snapshot
2. Transactional
3. Merge.

Replication need a special database called distribution to copying data to another server.
Main components in Replication

Distributor : Main functionality is distributing data between Publisher and Subscriber.

Publisher : Publisher is main Database Server which has database to replicating data to subscriber
You can create many publications from Publisher. There is limit for publishing articles. Maximum number of articles can be published under one publication is 256.

Subscriber : Always receiving data from publisher and in merge replication subscriber also sending back data changes to publisher

Articles : Articles nothing but Tables

There are five agents are there

1. Snapshot agent
2. Log Reader agent
3. Queue Reader agent
4. Distribution agent
5. Merge agent