Replication
Replication
• Allows you to make duplicate copies of your data
• Move the copies to different locations, and
synchronize the data automatically so that all the
copies have the same data values.
• Replication can be implemented between
Components of Replication
• Publisher , distributor and subscriber
• Publication
• Push and Pull subscriptions
Publisher
• Is a server that makes data available for
replication to other servers.
• Identifies which data is to be replicated
• Detects any changed data and maintains
Subscriber
• Are servers that store replicated data and
receive updates.
• Subscribers can make updates to data .
Distributor
• The Distributor server
– contains the distribution database
– stores metadata, history data, and (for
transactional publications)
Publication
• Is a collection of articles, and an article is a
grouping of data to be replicated.
• An article can be an entire table, only certain
columns (using a vertical filter), only certain rows
(using a horizontal filter), or even a stored
procedure (in some types of replication).
Push Subscription
• Publisher propagates the changes to a Subscriber without
a request from the Subscriber to do so.
• Typically, used in applications that are required to send
changes to Subscribers whenever and as soon as they occur.
• Best for publications that require near real-time movement
of data .
• Used in cases when the higher processor overhead at the
Publisher does not affect performance.
• Changes can also be pushed to Subscribers on a
Pull Subscription
• With a pull subscription, the Subscriber asks for
periodic updates of all changes at the Publisher.
• Pull subscriptions are best for publications having a
large number of Subscribers eg.Subscribers using the Internet.
• Pull subscriptions allow the user to determine when
the data changes are synchronized.
• A single publication can support a mixture of push
Agents
• Snapshot agent
• Log reader agent
• Distribution agent
Snap shot Agent
• Prepares schema and initial data files of published
tables and stored procedures.
• Stores the snapshot on the Distributor and records
information about the synchronization status in the distribution database.
• Each publication has its own Snapshot Agent that runs
on the Distributor and connects to the Publisher.
• The Snapshot Agent is run typically under SQL Server
Log reader Agent
• The Log Reader Agent moves transactions
marked for replication from the transaction log
on the Publisher to the distribution database.
Distribution Agent
• The Distributor receives all changes to published data,
stores the changes in its distribution database, and transmits them to Subscribers.
• The Distributor may or may not be the same computer as the Publisher.
• Push subscriptions to either snapshot or transactional
publications have Distribution Agents that run on the Distributor.
• Pull subscriptions to either snapshot or transactional
publications have Distribution Agents that run on the Subscriber instead of the Distributor.
Merge Agent
• It is the agent in merge replication, that applies initial
snapshot jobs held in publication database tables to Subscribers.
• It also merges incremental data changes that have
occurred since the initial snapshot was created.
• Push subscriptions to merge publications have Merge Agents that run on the Publisher.
• Pull subscriptions to merge publications have Merge
Types of replication
• Snapshot
• Transactional
Snapshot replication
? Simplest type of replication
? A picture, or snapshot, of the published data in the
database at a moment in time .
? All this data is sent to the Subscriber instead of
sending just the changes in data.
? Hence subscribers are updated by a total refresh of the
data set.
? Guarantees transactional consistency between the
? Snapshot replication is carried out by the Snapshot Agent and the Distribution Agent.
? The Snapshot Agent prepares snapshot files containing
schema and data of published tables and stores the files in the snapshot folder on the Distributor, and records
synchronization jobs in the distribution database on the Distributor.
? The Distribution Agent moves the snapshot jobs held in the
distribution database tables to the destination tables at the Subscribers.
? The distribution database is used only by replication and
Transactional replication
? Used to replicate two distinct types of objects: tables and stored procedures.
? Uses the transaction log to capture changes that were made to data in an article.
? The modifications made to the data like
Insert,Delete etc are monitored and stores these changes in the distribution database.
The Snapshot Agent
? prepares snapshot files containing schema and data of
published tables,
? stores the files in the snapshot folder on the Distributor
? records synchronization jobs in the distribution database on
the Distributor.
The Log Reader Agent
? monitors the transaction log of each database set up for
replication
? copies the transactions marked for replication from the
transaction log into the distribution database.
The Distribution Agent
? moves the transactions and initial snapshot jobs held in the
Merge replication
? Merge replication tracks changes in a source
database and synchronizes the values between the Publisher and Subscribers, all of whom may
update data.
Replication is carried out by the Snapshot Agent and Merge Agent.
The Snapshot Agent
? prepares snapshot files containing schema and data of
published tables,
? stores the files in the snapshot folder on the Distributor,
? records synchronization jobs in the publication
database.
The Merge Agent
? applies the initial snapshot jobs held in the publication
database tables to the Subscriber.
? merges incremental data changes that occurred at the
Publisher after the initial snapshot was created
? reconciles conflicts according to rules you configure or
Steps in replication :
I. Configuring replication
(a)
Done by replication wizards, available from the SQL Server Enterprise Manager Tools menu. You can use these wizards to enable, modify, and disable servers as Distributors, Publishers, and Subscribers.
Or
(b) Using the GUI of Enterprise manager Click on the server name
2. Create Publication Wizard to create a publication
(a) Using the Enterprise Manager Tools menu
Tools >>Replication >>Create or manage publication or
(b) Use the GUI of Enterprise manager
3. Use the push or pull subscription Push Subscription Wizard
Push Subscription Wizard to create one or more push subscriptions to each publication on a Publisher.
Using the wizard, you can specify:
?One or more servers or server
groups to receive the
publication as Subscribers.
?The destination database,
which must exist on all Subscribers.
?Subscription properties
Tasks to be performed before the subscription can be created:
•Create the publication
•Create a destination database at each subscribing server
•If selecting more than one server or server group, use the same
How to start the Push Subscription Wizard (Enterprise Manager)
To start the Push Subscription Wizard
•Expand a server group; then expand the Publisher. •On the Tools menu, point to Replication, and then
click Push Subscription to Others.
•Expand the database containing the publication in
which to create a subscription.
•Click the publication; then click Push New
Subscription.
Pull subscription wizard
Using the Pull Subscription Wizard, you can specify:
? The publication to which you want to subscribe.
? The name of the destination database to be used for
the subscription.
? Subscriber properties, such as the synchronization
schedule with the Publisher, the visibility of the subscription to other Subscribers, whether the
How to start the Pull Subscription Wizard (Enterprise Manager)
To start the Pull Subscription Wizard
• Expand a server group; then expand the Subscriber.
• On the Tools menu, point to Replication, and then click Pull Subscription To....
• Click Pull New Subscription.
