SQL Server Replication is a technology that is used to distribute and sync data from one server/database to maintain consistency.
The type of replication you choose for a given application will vary according to a number of factors. This MSDN article http://msdn.microsoft.com/en-us/library/ms152565.aspx details what type of replication is best suited for a given scenario.
Let’s first see the components of Replication:
The Distributor: It is a link between the components involved in replication.
Publisher: A publisher is the source database that allows its data available to the Subscribers.
Subscriber: A subscriber stores the replicated data.
Article: It is a grouping of data to replicate.
There are several agents which are standalone programs that play a key role in replication:
- Snapshot Agent
- Log Reader Agent
- Queue Reader Agent
- Distribution Agent
- Merge Agent
SQL Server supports three types of replications:
Snapshot Replication: A snapshot of the database is taken at a point in time and the whole data set is applied to the subscriber. This is not a continuous process so the subscribing database always has some latency when compared to the publishing database.
Transactional Replication: Transactional replication allows incremental changes to be applied to the subscribing databases. These changes can either be applied continuously or incrementally. This kind of replication is usually used when there is huge number of DMLs against the database.
Merge Replication: All DML and DDL modifications are tracked with triggers and applied correspondingly at the Subscribers and Publisher. The Subscriber synchronizes with the Publisher when connected to the network updates are committed since the last time synchronization occurred.
In next article we will see the information about the stages involved in implementing replication