Replication :
Replication is the process of copying and distributing the data between databases to different servers throughout the enterprise .
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.
Advantages of Replication
a. Each site maintains their copies.
b. Any failure of the centralized server will not be affected on the individual sites.
c. It moves the data closer to the users.
d. Users can define their own business logic on their own copiers.
e. Number of concurrent users will be less due to this less number of locking and blockings.
Types of Replications :
Snapshot Replication :
Snapshot Replication is very useful when the source data changes occasionally (Rarely). Snapshot replication takes a picture or a snapshot of the database and propagated(Circulated) to the subscribers . It reduces the overhead on publishers and subscribers because it doesn't monitor data updates.
Transacational Replication :
Transactional replication starts with a snapshot of the publisher database . With transactional replication , any changes made to the articles are captured from the transactional log and propagated to the distributor continuously and automatically . Using transactional replication we can keep the publisher and subscriber in almost exactly the same state .
Merge Replication :
Merge replication starts with a snapshot of the publisher database . Subsequent schema 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 .
Replication Terminologies
a. Article :
An article is the data , transactions or stored procedures that are stored with in a publication . This is the actual information that is going to be replicated .
b. Publisher :
The server which provides the source data is called as the publisher .
c. Publications :
The articles which are replicated are called as the publications .
d. Subscriber :
The subscriber which receives the source data from the publisher is called as the subscriber .
e. Subscriptions :
The articles which are receiving the replicated data are called as the subscriptions .
f . Distributor :
Distributor is the responsible for distributing the data from the publisher to subscrier ,
Distributor maintains the history of the replication process .
Subscription Types :
a. Push Subscriptions :
In Push subscriptions the publisher is the responsible for pushing the data from the publisher .
For Push Subscriptions ,the distribution agent and merge agent runs on the distributed server .
c. Pull Subscriptions :
In Pull Subscriptions the subscriber is the responsible of pulling the data from the publisher .
For Pull Subscriptions , the distribution agent and the merge agent runs on subscriber agent .
Replication Agents :
Replication Agents are responsible to implement the replication process .
Replication Agents are the .exe files which runs as jobs .
We can find 5 replication agents in the following folder .
Drive :\ Program Files \ Microsoft SQL Server \90 \COM
Types of Agents :
1. Snapshot Agent (snapshot.exe)
2. Distribution Agent (distrib.exe)
3. Log Reader Agent (logread.exe)
4. Merge Agent (replmerg.exe)
5. Queue Reader Agent (qrdrsvc.exe)
1. Snapshot Agent : (snapshot.exe )
Snapshot Agent is responsible for creating the snapshot of the publications .
The snapshot will be stored in the repldatafoler fo that instance in the distributor.
The snapshot is created by using the following files .
a. ,sch : Schema files which contains the articles structure .
b. .bcp : Contains the articles data.
c. .idx : Contains the indexes information.
d. .trg : Contains the triggers information
e. .pre : Contains the referential integrity constraints .
2. Distributor Agent : (distrib.exe)
DA is responsible for distributing the data from the publisher to subscriber .
The DA runs on distributed server for push subscriptions and on the subscriber for pull subscriptions.
3. Log Reader Agent : (logread.exe)
LA is used in transactional publications .
LA is responsible for reading the transactions from the publisher and will be replicated to the subscribers .
LA runs on distributor which will be created for each database on which the transactional publication is implemented.
4. Merge Agent : (replmerg.exe)
MA is used in Merge Replications .
MA is responsible to read the transactions from the publisher and will be replicated to the subscriber and also vice-versa.
Same as DA the MA runs on distributor server for push subscriptions and on the subscriber for pull subscriptions.
It creates one MA for each article participating in the merger replications.
5. Queue Reader Agent (qrdrsvc.exe )
QRA is used in transactional publication with updatable subscriptions .
QRA is responsible for reading the transactions from the log file of the publisher and will be kept in a queue.
These queue of transactions will be replicated to the subscriber .
The queue of transactions will be replicated to the subscriber.
The QRA runs on the distributor which will be created only once for a instance .
Points to Remember :
Once the Replication configured successfully we should able to notify the below points .
New system database with the name DISTRIBUTION will be created.
Six Jobs will be created under the agent services.
New Login with the name distribution_admin will be created .
To work with replication the recovery model should be either Full or Bulk Logged .
I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .
Replication is the process of copying and distributing the data between databases to different servers throughout the enterprise .
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.
Advantages of Replication
a. Each site maintains their copies.
b. Any failure of the centralized server will not be affected on the individual sites.
c. It moves the data closer to the users.
d. Users can define their own business logic on their own copiers.
e. Number of concurrent users will be less due to this less number of locking and blockings.
Types of Replications :
Snapshot Replication :
Snapshot Replication is very useful when the source data changes occasionally (Rarely). Snapshot replication takes a picture or a snapshot of the database and propagated(Circulated) to the subscribers . It reduces the overhead on publishers and subscribers because it doesn't monitor data updates.
Transacational Replication :
Transactional replication starts with a snapshot of the publisher database . With transactional replication , any changes made to the articles are captured from the transactional log and propagated to the distributor continuously and automatically . Using transactional replication we can keep the publisher and subscriber in almost exactly the same state .
Merge Replication :
Merge replication starts with a snapshot of the publisher database . Subsequent schema 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 .
Replication Terminologies
a. Article :
An article is the data , transactions or stored procedures that are stored with in a publication . This is the actual information that is going to be replicated .
b. Publisher :
The server which provides the source data is called as the publisher .
c. Publications :
The articles which are replicated are called as the publications .
d. Subscriber :
The subscriber which receives the source data from the publisher is called as the subscriber .
e. Subscriptions :
The articles which are receiving the replicated data are called as the subscriptions .
f . Distributor :
Distributor is the responsible for distributing the data from the publisher to subscrier ,
Distributor maintains the history of the replication process .
Subscription Types :
a. Push Subscriptions :
In Push subscriptions the publisher is the responsible for pushing the data from the publisher .
For Push Subscriptions ,the distribution agent and merge agent runs on the distributed server .
c. Pull Subscriptions :
In Pull Subscriptions the subscriber is the responsible of pulling the data from the publisher .
For Pull Subscriptions , the distribution agent and the merge agent runs on subscriber agent .
Replication Agents :
Replication Agents are responsible to implement the replication process .
Replication Agents are the .exe files which runs as jobs .
We can find 5 replication agents in the following folder .
Drive :\ Program Files \ Microsoft SQL Server \90 \COM
Types of Agents :
1. Snapshot Agent (snapshot.exe)
2. Distribution Agent (distrib.exe)
3. Log Reader Agent (logread.exe)
4. Merge Agent (replmerg.exe)
5. Queue Reader Agent (qrdrsvc.exe)
1. Snapshot Agent : (snapshot.exe )
Snapshot Agent is responsible for creating the snapshot of the publications .
The snapshot will be stored in the repldatafoler fo that instance in the distributor.
The snapshot is created by using the following files .
a. ,sch : Schema files which contains the articles structure .
b. .bcp : Contains the articles data.
c. .idx : Contains the indexes information.
d. .trg : Contains the triggers information
e. .pre : Contains the referential integrity constraints .
2. Distributor Agent : (distrib.exe)
DA is responsible for distributing the data from the publisher to subscriber .
The DA runs on distributed server for push subscriptions and on the subscriber for pull subscriptions.
3. Log Reader Agent : (logread.exe)
LA is used in transactional publications .
LA is responsible for reading the transactions from the publisher and will be replicated to the subscribers .
LA runs on distributor which will be created for each database on which the transactional publication is implemented.
4. Merge Agent : (replmerg.exe)
MA is used in Merge Replications .
MA is responsible to read the transactions from the publisher and will be replicated to the subscriber and also vice-versa.
Same as DA the MA runs on distributor server for push subscriptions and on the subscriber for pull subscriptions.
It creates one MA for each article participating in the merger replications.
5. Queue Reader Agent (qrdrsvc.exe )
QRA is used in transactional publication with updatable subscriptions .
QRA is responsible for reading the transactions from the log file of the publisher and will be kept in a queue.
These queue of transactions will be replicated to the subscriber .
The queue of transactions will be replicated to the subscriber.
The QRA runs on the distributor which will be created only once for a instance .
Points to Remember :
Once the Replication configured successfully we should able to notify the below points .
New system database with the name DISTRIBUTION will be created.
Six Jobs will be created under the agent services.
New Login with the name distribution_admin will be created .
To work with replication the recovery model should be either Full or Bulk Logged .
I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .
Thanks for providing short notes with nice explanation.
ReplyDeleteyou well come :) please keep in touch with the blog.
ReplyDelete