Replication Overview

1- What is SQL Replication?

SQL replication is a technology designed to allow storing identical data in multiple locations. First lets examine why replication may be useful and how it solves common data distribution problems. There are several classical examples in which SQL replication solves business problems. One of the most popular ones is the case when a business has mobile employees, who need to access data from their portable computers while they are away from they office. Another example is when the workforce of a business is distributed around the world and all employees need to access one and the same set of data, but network connectivity has poor quality. In both the above examples using SQL replication is the right thing to do. Replication is used in many other scenarios as well for example as a backup solution, and for offloading database intensive processing like reporting and data mining from main live databases.

2- Types of SQL Replication:

• Snapshot replication

The publisher simply takes a snapshot of the entire replicated database and shares it with the subscribers.  Of course, this is a very time and resource-intensive process.  For this reason, most administrators don’t use snapshot replication on a recurring basis for databases that change frequently

• Transactional replication

With transactional replication, the replication agent monitors the publisher for changes to the database and transmits those changes to the subscribers.  This transmission can take place immediately or on a periodic basis.

• Merge replication

Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

3- Replication Overview

• Publisher

Publisher Is database instance that’ allowed the Replicate data to the Subscribers Databases instances Publisher can sent to one or more Subscriber Database

• Distributor

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers

• Subscribers

A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

• Article

database object that is included in a publication this Publication Contain difference type like (table , View , Stored ).

********************************************************************************************************************************

I hope that this will be useful topic

Regards

Mostafa Elmasry

References

http://technet.microsoft.com/en-us/library/ms151314(v=sql.105).aspx