What is replication?
In its simplest
form, data in a master site are replicated to other sites in the form of snapshots.
A snapshot does not have to contain all the master data—it can, for example,
exclude certain columns from a table for security reasons.
What
are different types of replication?
1. Transactional Replication
Transactional replication
replicates the data on one server (the publisher) to another server (the
subscriber) with less latency than log shipping.
Transactional replication is implemented by
the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The
Snapshot Agent prepares snapshot files containing schema and data of published
tables and database objects, stores the files in the snapshot folder, and
records synchronization jobs in the distribution database on the Distributor.
The
Log Reader Agent monitors the transaction log of each database configured for
transactional replication and copies the transactions marked for replication
from the transaction log into the distribution database, which acts as a
reliable store-and-forward queue. The Distribution Agent copies the initial
snapshot files from the snapshot folder and the transactions held in the distribution
database tables to Subscribers.
Incremental
changes made at the Publisher flow to Subscribers according to the schedule of
the Distribution Agent, which can run continuously for minimal latency, or at
scheduled intervals. Because changes to the data must be made at the Publisher
(when transactional replication is used without immediate updating or queued
updating options), update conflicts are avoided. Ultimately, all Subscribers
will achieve the same values as the Publisher. If immediate updating or queued
updating options are used with transactional replication, updates can be made
at the Subscriber, and with queued updating, conflicts might occur.
2. Merge Replication
Merge was designed for more of
a client-server environment where the client sometimes syncs up with the server but is
disconnected quite often.Changes are tracked by triggers instead of by the log
reader agent, and latency isn't as low as it is with peer to peer.
3.
Snapshot Replication
Snapshot replication distributes data
exactly as it appears at a specific moment in time and does not monitor for
updates to the data. When synchronization occurs, the entire snapshot is
generated and sent to Subscribers.
Define below terms used in replication
·
Publisher
·
Subscriber
·
Distributor
Publisher:
Publisher is the server
or database that sends its data to another server or database.
Publisher contain
publication/publications. Publication is a collection of one or more articles
that is sent to a subscriber server or database. Article is the basic unit of
replication and can be a table or a subset of a table.
Subscriber
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.
Distributor
The
Distributor is a database instance that acts as a store for replication
specific data associated with one or more Publishers. Each Publisher is
associated with a single database (known as a distribution database) at the
Distributor. 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. In many cases, a single database server
instance acts as both the Publisher and the Distributor. This is known as a local
Distributor. When the Publisher and the Distributor are configured
on separate database server instances, the Distributor is known as a remote
Distributor.
What is the difference between Push and Pull Subscription?
Push and Pull,
as named in the title of this article,are the two methods available for moving data from the Distributor to
the Subscriber(s). Under the Push method, the Distributor is responsible
for queuing data from the Publisher,then propagating it to the
Subscriber(s). Under the Pull method, the Distributor is responsible for
queuing data from the Publisher, and it is the job of each Subscriber to
connect to the Distributor and grab all queued data ready for
replication. Selecting the incorrect method can lead to serious
performance issues, especially at peak times of database use.
No comments:
Post a Comment