Replication
Replication is the way to distribute data from one database to one or more other databases. It is the set of technologies for copying data from one database to another. Recipient database or databases can be SQL Server database or any other server databases. The basic purpose of replication is to distribute data to other databases so that they are available to users. It will reduce workload on central database and distribute to other databases. Changes are synchronized with central database so that there are identical databases available for user.
Replication provides facility for users to use local database and make changes into it and later these changes will be synchronized with central database. In this way database availability can be high because users work with their local databases instead of remote database and they have all the data available to work with.
Replication helps to solve many problems that you can face when you have one central database. It is helpful for recovery in case of disaster. If one database fails, users can shift to other identical database.
Replication Terminology
Replication is often described with publisher/subscriber terminology. The central database which is responsible to provide synchronized data to other database is called the publisher and other databases that use this data are called subscribers. Database objects that are distributed to other databases are called publications. An article is the basic unit of replication. It can be a table, stored procedure or view. A publication is collection of more than one article. A publisher is responsible to maintain the original copy of the publication. A subscriber is subscribed to publications and receives publications. A distributer is responsible to run the replication agents.
Types of Replication
There are three types of replication.
Snapshot Replication
Snapshot Replication takes the snapshot of the data in publisher database and sends it to one or more subscribers. In Snapshot replication, entire data is copied to other database. This type of replication is very useful for small volume databases. Snapshot replication is done when data in database has changed infrequently and at large extent. Normally in Snapshot replication, data is overwritten to already available data in the subscriber database. So each time Snapshot applied, it overwrites the existing data. Changes are not tracked in Snapshot Replication.
Transactional Replication
Transactional Replication initially starts with snapshot replication and then transactions are distributed to subscribers as changes occur in publisher database. Transactional Replication is used when changes must have to spread immediately or publisher has very high rate of insert, update and delete activates. Transaction Replication is applied to applications that require immediate changes from publisher to subscribers. Snapshot replication is also scheduled at regular interval with Transactional replication to insure data consistency in publisher and subscribers. SQL Server transaction log is used to track changes.
Merge Replication
Merge Replication is used when multiple subscribers wants to merge changes into publisher database. It provides various subscribers to work separately and later merge updates to publisher. Subscribers receive data from publisher, make changes in it and later spread these changes to publisher and other subscribers. Merge replication does not use transactions and it relies on conflict resolution rules. Conflicts can occur in Merge replication because same data can be merged into publisher by more than one subscriber. Merge Replication has many ways to handle conflicts. Merge Replication tracks changes through triggers and metadata tables.
