Replication in SQL Server
Introduction
ReplicationĀ in SQL Server is a way of distribution of database and their objects from one master database to one or more recipient databases which can be on the same server or on the remote server. ItĀ 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. Using replication, we can distribute data to different locations and to remote users over local and wide area networks.It is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users so that they can make changes to their local copy of the database.
Replication Components
SQL Server replication consists of three components: The Publisher, the Distributor and the Subscriber. These components act on articles that are defined within publications and subscriptions.
Article
For each SQL Server object that should be replicated, a replication article needs to be defined. Each article corresponds to a single SQL Server object, or a subset of an object. The objects that are replicated most often are tables, views and stored procedures. The properties of an article determine whether the article contains the entire object, or if a filtered subset of the objects makes up the replicated article.
Publication
A group of articles that logically belong together can be combined into a publication. The publication has options defined that apply to all the articles in that publication. The main option defined by a publication is the type of replication that is to be used.
Publisher
The SQL Server instance that makes a publication available for replication is called the publisher.
The publisher monitors all articles for changes, and makes information about those changes available to the distributor.
Distributor
The distributor is the SQL Server instance keeps track of all subscribers and all published changes and makes sure that each subscriber gets notified of each change. Most of the changes are tracked in a distribution database. The distributor can be a separate SQL Server instance, but often the distribution service runs on the same machine as the publisher.
Subscriber
The subscriber is the SQL Server instance that receives all the published information through subscriptions.
Subscription
A subscription is the counterpart of the publication. A subscription defines which server (subscriber) is to receive the updates published in a publication. Each subscription creates a link between one publication and one subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the distributor directly updates the data in the subscriber database. In a pull subscription, the subscriber asks the distributor regularly if any new changes are available, and then updates the data itself.
Types of replication:-
- Ā Ā Ā Ā Ā Snapshot Replication
- Ā Ā Ā Ā Ā Transactional Replication
- Ā Ā Ā Ā Ā Merge Replication
Snapshot Replication:-
SnapshotĀ replicationĀ is also known as staticĀ replication. SnapshotĀ replicationĀ copies and distributes data and database objects exactly as they appear at the current moment in time. Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements.Ā Therefore this replication is advisable only when the size of the database is small.
Transactional Replication:-
Transactional replication works on a transaction basis. In Transactional replication only the committed changes are logged into the subscribers. Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. It is the best way of replication to work one way only.
Merge Replication:-
Merge replication was designed to allow the changes to the data to be made on the publisher as well as the subscriber side. In Merge Transaction, a subscriber might not be connected during the day. That subscriber would synchronize after reconnecting in the evening. If a row gets updated in two different places at the same time, a conflict occurs. Merge replication have many built in options to resolve those conflicts.
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Hi,
I am from Virat Bharat I Saw This Blog. This Is very Informative to our Youth. Really Good If you Want to see our Site You can easily Visit
http://www.viratbharat.com/Technology/Technology_News.aspx
Thanking You
Puneet Kardam
Thanks:-)
Thanks:-)
please cite some examples as illustrations?
Vivek, your articles are very good and so help full. Thanks a lot
What's up it's me, I am also visiting this website regularly, this web site is truly fastidious and the visitors are genuinely sharing good thoughts.
Flex developer London
Hello, its fastidious paragraph on the topic of media print, we all be aware of media is a
enormous source of facts.
Flash developer London
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
http://sqlservermasters.com/
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Find More Information here on that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] There you can find 92698 additional Information to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Read More on on that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] There you can find 74419 additional Information to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Read More Info here to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] There you can find 43201 more Info to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]
… [Trackback]
[…] There you can find 31402 additional Information on that Topic: techmixing.com/2012/12/replication-in-sql-server.html […]