What is SQL Server Replication ?

1- Reviewing SQL Server Replication (Historical)
Replication distributes data from the central database to one or more target databases, and merges changes from a target database into the central one. Both the source database and the destination database can be implemented as a SQL Server database or as any other data source, as long as an OLE DB provider is available to handle the nitty-gritty details of the particular data source you choose (such as ORACLE, Access, and so on).
The practical reasons to perform replication are to distribute workload and to
synchronize data among remote databases that you want to keep in sync. By maintaining identical data sets across multiple databases you can provide better performance.
Local users can connect to and use their own local databases, instead of
connecting to a remote central server. Your sales will be able to send sales leads back to a central database. Or you can use replication to keep a standby server in sync so you can switch to it should your primary server fail.
The replication architecture is quite complex, as it was designed to meet a variety of needs. Here are the basic concepts you ought to understand :

1.1 Basic replication terminology
Replication consists of two major components:

A - Replication components—SQL Server components used in replication:
Publisher
Distributor
Subscriber. (n) .

B- Replication Agents—Utility programs that assist in the replication process:
Snapshot Agent
Distribution Agent
Log Reader Agent
Queue
Reader Agent,
Merge Agent.


1.2 The replicated data are organized into the following categories:

1- Publication : wrapper for distributed data; a collection of one or more
Articles scheduled for publication (replication).

2- Article: basic unit of replication. It can be a table, certain columns in
The table, certain rows in the table, a view, or even a stored procedure.
More than one article together comprises a publication.

3- Subscription: Request to receive a publication. The two basic types of
Subscription are the Pull subscription (initiated by the Subscriber) and the
Push subscription (initiated by the Publisher).

1.3 Each server participating in the replication is assigned one or more of the following
Roles:

Publisher A source server for the distributed data. It maintains all the
Information about data specified for publishing.

Distributor An intermediary between the Publisher and the Subscriber;
it can also be both Publisher and Subscriber. Its role varies according to
the type of replication.

Subscriber The final destination of the distributed data. It is a recipient
of the publications it has subscribed to; depending on the type of replication,
it may also be able to propagate changes to its own set of data onto
the Publisher.

1.4 Replication Types:
Snapshot replication—This type of replication takes a snapshot of the
data in the Publisher database and replaces it with the entire data set of
one or more subscribers; subsequent replication again replaces the complete
data set in the subscriber database(s). Though notable for being virtually
foolproof in providing synchronous data sets, this type of replication
increases network traffic and, as intervals increase, data sets become less
synchronized.


Transactional replication—This type of replication is all about changes.
It propagates changes only to subscribers. It starts with an initial snapshot
Replication and then distributes selected transactions in the Publisher
database transaction log (marked for replication) to the target servers.
Snapshot replication is also regularly scheduled to ensure consistency of
the data. Its major advantages are more timely updates and much lighter
network traffic than you get with pure snapshot replication.

Merge replication—This type of replication allows subscribers who make
changes to their local copies of the data to merge these changes into the
source database. Merge replication is not transactional and relies on conflict
resolution to determine the precedence of the changes.

2- Selecting a Replication Model
2.1Central publisher—The most common replication model. It maintains
Publisher and Distributor databases on the same server, with subscribers
configured somewhere else.

2.2 Central publisher with remote distributor
The Publisher database is
on one server and the Distributor database on another; subscribers are by
default placed on remote servers (you do not need to maintain a Subscriber
on the same server as a Publisher).

2.3 Central subscriber—One Subscriber collects data from several publishers;
the data can then be republished, as nothing prevents the server from
Wearing several hats—it can be a Subscriber, a Publisher, and a Distributor
at the same time.

2.4 Publishing subscriber—Republishes received data to other Subscribers;
see the preceding description of the central-subscriber model.


3-Preparing for Replication
3.1 Snapshot replication:
1- Consider space requirements as because the data will be moved as a whole.
2- Timing, because replication is a strain on network resources as well as on database resources
3- Importance to replicated data.

3.2 Transactional replication:

Increase the size of transaction Log on publication Server to make sure that all replicated data are published to Subscribers before they are purge.

3.3 Merge replication:

1- Every table must have its Primary Key.
2 - Foreign key must includes referenced tables.