Menu

Database Replication – Part I

10. February 2018 - Ashwini Tripathi
This post was originally published on this site

One of my reader had requested to write post on this topic. I have gone through some posts and prepared a step by step process to demonstrate how we can configure Database Replication in SQL.

I will be covering the topic in couple of posts, this is first post in this series.

Today I will be covering basic theory behind the topic and then move to practical approach in my next post.

Brief extract of Topic

Database replication can be done in at least four different ways:

A distributed database management system ensures that changes, additions, and deletions performed on the data at any given location are automatically reflected in the data stored at all the other locations. Therefore, every user always sees data that is consistent with the data seen by all the other users.

REPLICATION ENTITIES

SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.

Publisher

 

Article

 

Publication

 

Distributor

 

Subscriber

Subscription

 

Push Subscriptions

 

Pull Subscriptions –

 

 

Detailed Description on Types of Replication

Snapshot Replication

Snapshot replication simply takes a “snapshot” of the data on one server and moves that data to another server (or another database on the same server). After the initial synchronization snapshot, replication can refresh data in published tables periodically—based on the schedule you specify. Although snapshot replication is the easiest type to set up and maintain, it requires copying all data each time a table is refreshed.

Between scheduled refreshes, data on the publisher might be very different from the data on subscriber. In short, snapshot replication isn’t very different from emptying out the destination table(s) and using a DTS package to import data from the source.

Transactional Replication

Transactional replication involves copying data from the publisher to the subscriber(s) once and then delivering transactions to the subscriber(s) as they occur on the publisher. The initial copy of the data is transported by using the same mechanism as with snapshot replication: SQL Server takes a snapshot of data on the publisher and moves it to the subscriber(s). As database users insert, update, or delete records on the publisher, transactions are forwarded to the subscriber(s).

To make sure that SQL Server synchronizes your transactions as quickly as possible, you can make a simple configuration change: Tell it to deliver transactions continuously. Alternatively, you can run synchronization tasks periodically. Transactional replication is most useful in environments that have a dependable dedicated network line between database servers participating in replication. Typically, database servers subscribing to transactional publications do not modify data; they use data strictly for read-only purposes. However, SQL Server does support transactional replication that allows data changes on subscribers as well.

Merge Replication

Merge replication combines data from multiple sources into a single central database. Much like transactional replication, merge replication uses initial synchronization by taking the snapshot of data on the publisher and moving it to subscribers. Unlike transactional replication, merge replication allows changes of the same data on publishers and subscribers, even when subscribers are not connected to the network. When subscribers connect to the network, replication will detect and combine changes from all subscribers and change data on the publisher accordingly. Merge replication is useful when you have a need to modify data on remote computers and when subscribers are not guaranteed to have a continuous connection to the network.

REPLICATION AGENTS

Replication process works in the background with the help of jobs.

These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. 110COM folder.

All the agents’ information is present in Distribution db in the following tables.

dbo.MSxxx_agents

dbo.MSxxx_history

Snapshot Agent

 

Distribution Agent

Log Reader Agent

Merge Agent

 

Queue Reader Agent

 

 

We will continue on same topic in my next post.