Replication in SQL Server
December 31, 2012
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.
IN clause in SQL Server
Indexes in Sql server
SQL Script to find the tables created from a given date
About The Author
vivekjohari
I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com
19 Comments
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/