The replication feature in MS SQL Server moves the data
from a remote server to our local server boxes via publications and
subscriptions mechanism. There are various reasons and scenario where
replications can be considered a very strong tool for data relay.
While a subscription is configured they are either created as PULL or PUSH subscriptions. Push subscription means the control for syncing the changes falls on the shoulders of the centralized distributor. This configurations helps when the intentions is receive the changes whenever there is a change on the publisher side. Moreover since this is a centralized way it’s helpful as all the subscribers to be updated by the distributor itself.
These are basic terms in replication are
Article:
The article is the information that is
going to be replicated. It could be a table, a procedure or a filtered table
etc.
Publisher:
The publisher is the database on the
source server which is actually replicating the data. The publication which
is collection of articles (various objects in the database) is published by the
publisher.
Distributor:
The distributor can be considered as
the delivery boy who brings the publications to the subscriber. The distributor
could himself be a publisher or a subscriber.
Subscriber:
Subscriber is the end receiver of the
publication who gets the data in the form of subscriptions. The changes
published are propagated to all the subscribers of the publications through the
distributor. The subscriber simply has to create a subscription on the
publication from its end to receive the data.
There are various types of replication:
Configuring
a Distributor:
Before trying to get insights about each of
the replications and how to configure it, it’s important to setup a
distributor.
Select the server which is to act as the
distributor and the right click on the replication folder and then click
configure Distribution.
This will lead to the below screen, click next.
The next screen as below will as to either
configure the current server as the Distributor or connect to the different
desired server and configure it to be a distributor. Let’s select the current
server and click next.
The Next screen configures the Distribution
database its data (.MDF) and Log (.ldf) files. Bear in mind once the
distribution has been configured on a server, the system databases will have an
additional database added to it “Distribution”. Click next.
On clicking next it brings you to the
screen where you can add all the servers which will be the publishers and use
the currently being configured distributor to distribute its publications. By
default the current server will be added as the publisher once could add more
servers. Click next.
Click next on the below screen and proceed.
This will bring you to the last screen
which will have the summary of the configurations. Click finish to complete the
Distributor configuration.
While a subscription is configured they are either created as PULL or PUSH subscriptions. Push subscription means the control for syncing the changes falls on the shoulders of the centralized distributor. This configurations helps when the intentions is receive the changes whenever there is a change on the publisher side. Moreover since this is a centralized way it’s helpful as all the subscribers to be updated by the distributor itself.
Pull subscriptions are designed to be triggered from
the subscriber’s side. The subscriber pulls the data from the publisher on need
basis or can conveniently schedule the job to run on its own discretion without
depending on the publisher.
This is also a good way of doing things for system
which are not constantly connected to the system. Hence it helps do away from
unnecessary push jobs even when the subscriber is not connected. Rather whenever
the subscriber gets connected to the system it simply pulls the data and syncs
up.
For either of the subscriptions there are few involved
agent jobs which take care of syncing things up. They are,
Log reader Agent
The log reader agent sits on the
distribution server with the entrusted job to constantly monitor the
transactions logs of the published databases which uses this distributor.
Whenever there a transaction happens on the database which acts as the
publisher, the log reader agent stores the transaction in its Distribution
system database and further these changes are forwarded to the respective
subscribers via distribution agent or merge agent.
Distribution Agent
The distribution agent is
responsible for moving the stored transaction from the distribution server to
the subscribers.
Snapshot Agent
For any replication type this
agent is responsible for copying the initial snapshot (i.e. complete schema and
data) of the publication from the publisher to the subscribers. This forms the
base after which the depending upon the kind of replication configured further
changes are copied to the subscribers.
Merger Agent
This agent is responsible for syncing all
the changes across the subscribers and publisher.
Queue Reader Agent
This agent is used for updating
publishers/subscribers replication where all the changes are queued up at the
distributor’s end and are reapplied on the publications.
These are the SQL Server agent job which gets created
upon configuring replication.
No comments:
Post a Comment