What is Replication?
Replication is the process of distributing data from one database to another on
the same server or servers connected through LAN or the internet.
Replication is used to synchronize data between databases that may be at remote
location.
Using this, you can maintain copies of database at more than one locations.
Maintaining copies of data at different location provide a standby server.
It can be used for load balancing of server by maintaining area specific data
independently and replicate later to have single version of data.
What are the types of Replication?
Snapshot Replication
Merge Replication
Transactional Replication
Snapshot Replication
In this kind, snapshot of entire data is copied from publisher to the
subscriber's database on regular interval.
This kind of replication is used for replicating data that doesn’t change
frequently.
It is used where the source can manage with out of date data.
This replication is considered when amount of data to be replicated is small.
Merge Replication
This allows both publisher and subscriber to work independently, online or
offline and merges the changes later.
Here changes are track on both publisher and subscriber and then merged.
Transactional Replication
This kind is used when changes are frequent.
The replication agent monitors the changes at the publisher and distributes the
changes to the subscribers.
Transactional Replication is required where up to date data is required.
Define the terms used in Replication.
Publisher is the database that transmits its data to another
database.
Subscriber is the database that receives data from another
database.
Distributor manages the flow of data during data replication.
Push subscription is the subscription when data is pushed from
publisher database to subscribing database.
Pull subscription is subscription when the subscribing
database pulls the data from distribution database.
Distribution database is the system database that stores
snapshot jobs to be distributed to subscribers.
Describe the replication agents that SQL Server
supports.
Snapshot Agent
Log Reader Agent
Distribution Agent
Merge Agent
The Snapshot Agent creates snapshot files and stores on the distribution
database. It also keeps track of synchronization status in the distribution
database. This is used in all kinds of replication.
The Log Reader Agent moves transactions marked for replication from the
transaction log on the Publisher to the distribution database. This is used in
transactional replication.
The Distribution Agent moves the snapshot jobs from the distribution database to
Subscribers. This is used in Merged and transactional replication.
The Merge Agent is an agent of merge replication that merges incremental data
changes that have occurred since the initial snapshot was created.
Describe in brief working of Replication.
At first data and object is synchronized between publisher and subscribers.
The snapshot is created on the publisher and transmitted to subscribers.
All the subsequent changes on the publisher are stored in distribution
database.
Subscriber receives the data either using push or pull mechanism as configured
from distribution database.
Interview questions on Replication
-
Why do we consider Replication as the best way to update data between SQL
Server?
-
Define the term publisher, distributor and subscriber in “Replication”.
-
Explain in brief “Push” and “Pull” subscription.
-
Is is possible for a publication to support push and pull at one time?
-
What are different types of replication?
-
Define each types of replication.
-
What are the advantages and disadvantages of using Snapshot replication?
-
What type of data will qualify for “Snapshot replication”?
-
What is the actual location where the distributor runs?
-
What are advantages and disadvantages of Merge replication?
-
Can you explain in detail how transactional replication works?
-
What are data type concerns during replications?
|