SQL Server Replication
Replication is one of the rich features available in MS SQL Server. It is the process to distribute data to various servers. Its advantage is high.This article describes SQL Server Replication step by step.
- What is Replication?
- Component of Replication
- Replication Agent
- Configuring Replication
- Replication Types
- Snapshot Replication
- Transactional Replication
- Transactional Replication with up Datable Subscriptions
- Merge Replication
- Advantage of Replication
What is Replication?
Replication is the process of copying data between two databases on the same server or different servers. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability. Someone may confused with replication and mirroring. Both have some difference.
Replication is the process of copying data between two databases on the same server or different servers. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability. Someone may confused with replication and mirroring. Both have some difference.
Component of Replication
Before working with SQL Server Replication we need to know some basic key terms involved with the replication system. Generally replication is based on Publisher/Subscriber analogy. The replication components are given bellow:
Before working with SQL Server Replication we need to know some basic key terms involved with the replication system. Generally replication is based on Publisher/Subscriber analogy. The replication components are given bellow:
- Article
- Publisher
- Publication
- Subscriber
- Subscription
- Distributor
What is Article?
Article is the information that is going to be replicated. It is the database object like table, view, function, stored procedure etc. We can set filter during subscriptions.
Article is the information that is going to be replicated. It is the database object like table, view, function, stored procedure etc. We can set filter during subscriptions.
What is Publisher?
Publisher is the server or database that sends its data to another server or database. It defines what will be publishing through a publication.
What is Publication?
Publication is the collection of database object which is published by the publisher.
Publisher is the server or database that sends its data to another server or database. It defines what will be publishing through a publication.
What is Publication?
Publication is the collection of database object which is published by the publisher.
What is Subscriber?
Subscriber is the server or database that receives data from another server or database.
Subscriber is the server or database that receives data from another server or database.
What is Subscription?
Subscription is a request by a subscriber to receive a publication. There are two types of subscriptions:
Subscription is a request by a subscriber to receive a publication. There are two types of subscriptions:
Push Subscription
With push subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes. It is created at the publisher server.
With push subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes. It is created at the publisher server.
Pull Subscription
With pull subscription, the subscriber is responsible for the replication process. Subscriber starts the process instead of publisher. It pulls the data from the publisher. It is created at the subscriber server.
With pull subscription, the subscriber is responsible for the replication process. Subscriber starts the process instead of publisher. It pulls the data from the publisher. It is created at the subscriber server.
What is Distributor?
Distributor is the server that manages the flow of data through the replication system. It is the middle man for publisher and subscriber. It brings the publications to the subscriber.
Distributor is the server that manages the flow of data through the replication system. It is the middle man for publisher and subscriber. It brings the publications to the subscriber.
Replication Agent
Replication process works in background with the help of jobs and these jobs are called agents. Agent’s information is stored in distribution database. Agent internally use respective .exe files present in …………….. \110\COM folder. Replication uses the following agents:
Replication process works in background with the help of jobs and these jobs are called agents. Agent’s information is stored in distribution database. Agent internally use respective .exe files present in …………….. \110\COM folder. Replication uses the following agents:
Snapshot Agent
Snapshot agent prepared the initial snapshot (i.e. complete schema and data) of the publication and stored the files in snapshot folder.
Snapshot agent prepared the initial snapshot (i.e. complete schema and data) of the publication and stored the files in snapshot folder.
Distribution Agent
Distribution agent moves the stored transaction from the distribution server to the subscribers. It is used in snapshot and transactional replication. It runs at either the publisher for push subscriptions or at the subscriber for pull subscriptions.
Distribution agent moves the stored transaction from the distribution server to the subscribers. It is used in snapshot and transactional replication. It runs at either the publisher for push subscriptions or at the subscriber for pull subscriptions.
Log Reader Agent
The log reader agent sits on the distribution server and continuously monitors the transactions logs of the publisher. Whenever a transaction happens, the log reader agent stores the transaction in its Distribution system database. Further these changes are forwarded to the respective subscribers via distribution agent or merge agent. Log reader agent is used in transactional replication.
The log reader agent sits on the distribution server and continuously monitors the transactions logs of the publisher. Whenever a transaction happens, the log reader agent stores the transaction in its Distribution system database. Further these changes are forwarded to the respective subscribers via distribution agent or merge agent. Log reader agent is used in transactional replication.
Merge Agent
It is used in merge replication. Merge agent sync all the changes across the publisher and subscribers. Every merge subscription has its own merge agent that connects to both the publisher and the subscriber and updates both.
It is used in merge replication. Merge agent sync all the changes across the publisher and subscribers. Every merge subscription has its own merge agent that connects to both the publisher and the subscriber and updates both.
Queue Reader Agent
It is used with transactional replication. Queue reader agent updates publishers or subscribers where all the changes are queued up at the distributor’s end and are reapplied on the publications.
It is used with transactional replication. Queue reader agent updates publishers or subscribers where all the changes are queued up at the distributor’s end and are reapplied on the publications.
Configuring Replication
The steps to configure replication are given bellow:
The steps to configure replication are given bellow:
- Configuring distributor
- Creating publication
- Creating subscription(s)
Replication Types
Microsoft SQL Server provides the following types of replication for use in distributed applications:
Microsoft SQL Server provides the following types of replication for use in distributed applications:
- Snapshot Replication
- Transactional Replication
- Transactional Replication with up Datable Subscriptions
- Merge Replication
Snapshot Replication
Snapshot replication is the simplest type of replication. With this kind of replication, the entire copy of the publisher is copied from the Publisher to the Subscriber’s. It is time consuming. Because the entire data is transferred. It is suitable for small volume of data and where the change occurs frequently. This replication is unidirectional. The subscriber can update or changes its local copy of subscribed data without causing any changes on the publisher’s data. The next time the data gets synchronized the entire snapshot gets overwritten.
Key facts of snapshot replication:
Snapshot replication is the simplest type of replication. With this kind of replication, the entire copy of the publisher is copied from the Publisher to the Subscriber’s. It is time consuming. Because the entire data is transferred. It is suitable for small volume of data and where the change occurs frequently. This replication is unidirectional. The subscriber can update or changes its local copy of subscribed data without causing any changes on the publisher’s data. The next time the data gets synchronized the entire snapshot gets overwritten.
Key facts of snapshot replication:
- Primary key is not mandatory.
- Subscribers can update or changes their local copy of subscribed data and this will not make any changes on the publisher’s data. During the next synchronization the subscriber’s data will be replaced by the publisher data. That means subscribers data will be same as like publisher data.
We can use snapshot replication in the following situations:
- Data changes infrequently.
- It is tolerable to have copies of data that are out of date with respect to the Publisher for a period of time.
- The volumes of data are mall.
- A large volume of changes happens over a short period of time.
Transactional Replication
Generally a Transactional replication starts with a snapshot of the publication database objects and data. When any schema or data changes made at the Publisher end it is delivered to the Subscriber almost in real time. After first snapshot, Instead of entire publication it only transfers the modifications. That’s why its latency is low. This type of replication is generally used in server-to-server environments.
Key facts of transactional replication:
Generally a Transactional replication starts with a snapshot of the publication database objects and data. When any schema or data changes made at the Publisher end it is delivered to the Subscriber almost in real time. After first snapshot, Instead of entire publication it only transfers the modifications. That’s why its latency is low. This type of replication is generally used in server-to-server environments.
Key facts of transactional replication:
- Must need a primary key column. Tables that have primary key are only eligible for this transaction
- Subscriptions are only read only
- Replication happens on a transaction, the response time of replication is very low
We can use transactional replication in the following situations:
- Changes should be replicated to the Subscribers as they occur
- It requires low latency
- Volume of insert, update, and delete operations is high
- The Publisher/Subscriber is a non-SQL Server database (Oracle)
Transactional Replication with up Datable Subscriptions
This type of replication is similar to the transactional replication. But here subscribers can update the existing data and latency is maintained at the same level. In most of the case this is the best solution. It uses the two phase commit process to keep the Publisher/Subscriber in sync. The commit process uses the MSDTC so that the changes are made simultaneously at the publisher-subscribers end. So before starting it is required to up and running MSTDC.
To activate the MSDTC: Windows Explorer -> Start -> Control Panel -> Administrative Tools -> Component Services.
Open Services and active DTC. Once the DTC is activated object explorer will show DTC as green and run able.
This type of replication is similar to the transactional replication. But here subscribers can update the existing data and latency is maintained at the same level. In most of the case this is the best solution. It uses the two phase commit process to keep the Publisher/Subscriber in sync. The commit process uses the MSDTC so that the changes are made simultaneously at the publisher-subscribers end. So before starting it is required to up and running MSTDC.
To activate the MSDTC: Windows Explorer -> Start -> Control Panel -> Administrative Tools -> Component Services.
Open Services and active DTC. Once the DTC is activated object explorer will show DTC as green and run able.
Key facts of transactional replication with up datable subscriptions:
- This is similar to the transactional replication (Must need a primary key column. Tables that have primary key are only eligible for this transaction).
- A unique identifier auto generated column named ‘MSrepl_tran_version’ is added to subscribers tables that do not have one. It is used for tracking changes to the replicated data.
- The subscribers can update the existing data and data will be synchronized in all the subscribers an publisher.
- MSDTC must be active and running.
- Need link server for every subscribers and publisher.
Merge Replication
Merge replication is the most difficult type of replication. It is bidirectional. It allows each of the subscribers to edit their piece of subscriptions independently and at some point these changes are merged together and synced amongst all the subscribers and publisher on the whole. If subscribers and publisher change the same value at the same time then publisher changes will be merged.
Key facts of merge replication with up datable subscriptions:
Merge replication is the most difficult type of replication. It is bidirectional. It allows each of the subscribers to edit their piece of subscriptions independently and at some point these changes are merged together and synced amongst all the subscribers and publisher on the whole. If subscribers and publisher change the same value at the same time then publisher changes will be merged.
Key facts of merge replication with up datable subscriptions:
- It is unidirectional
We can use transactional replication in the following situations:
- Various Subscribers might update the same data at different times and broadcast those changes to the Publisher and to other Subscribers.
- Subscribers need to change data offline, and later synchronize changes with the Publisher and Subscribers.
- Every Subscriber requires a different portion of data.
- The application requires net data change rather than access to intermediary data states. Example: if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only one time at the Publisher end to reflect the net data change (the fifth value).
Advantage of Replication
The advantages of using replication are given bellow:
The advantages of using replication are given bellow:
- It improved performance.
- It improved availability.
- It move data closer to the user.
- It reduce locking conflicts when multiple users are working.
- It allows sites work independently. That means each terminal can set its own rules for better service.
It is very important to choose appropriate replication.
No comments