SQL Replication term describes a group of technologies allowing information distribution and mirroring between different databases. SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few.
Why use SQL Replication?
There are many reasons why SQL replication exists. In many scenarios replication is done to ensure redundancy. For example if a business runs a mission-critical database, it may be wise to have a replication of this database on a separate physical database server, which can take over in case of failure in the primary database (software corruption, hardware failure, etc.). Another valid reason to use SQL replication is load balancing. By using replication you can share the access load between several database server having identical databases. There are many other reason to use SQL replication, however we won’t discuss them here.
Replication Publisher and Subscriber
To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.
MS SQL Server Replication
MS SQL Server has 3 main replication models – Merge Replication, Transactional Replication and Snapshot Replication.
The Merge replication is used when both Publisher and Subscriber need to make changes to their respective databases. In this case both databases might have been changed between runs of the Merge SQL replication, and the replication merges the changes in both locations. Of course when using merge replication you should be aware that there might be conflicts, for example duplicated primary keys. If there is a conflict, then the merge replication follows predetermined conflict resolution plan to correct the issue.
The Transactional replication is usually used with databases where data changes frequently and there’s need for constant refreshing of the data. The replication process watches the publisher’s database for any changes, if there are changes, it distributes them over to the replication subscribers.
The Snapshot replication is relatively rarely used, and as its name implies it does a snapshot of the publisher database at certain point of time and distributes it to the subscribers. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other 2 SQL replication types after the initial copy.
Popular Posts
-
The SQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column(s). For example if you want to select all t...
-
STRINGS: The way a group of integers can be stored in an integer array, similarly a group of characters can be stored in a character array. ...
-
Microsoft SQL Server SQL Server is one of the most popular and advanced database systems currently available. SQL Server is provided by Micr...
-
Java Server Pages JavaServer Pages (JSP) technology is the Java platform technology for delivering dynamic content to web clients in a por...
-
A JSP expression is used to insert Java values directly into the output. It has the following form: The Java expression is evaluated, conver...
-
To whom is this tutorial directed? This tutorial is for those people who want to learn programming in C++ and do not necessarily have any pr...
-
Introduction: In this exercise, you are going to build and run a sample Java program called Homework using NetBeans. The sample program can...
-
1 Introducing the .NET Framework with C# The .NET Framework is such a comprehensive platform that it can be a little difficult to descr...
-
Arguments passed by value and by reference. Until now, in all the functions we have seen, the arguments passed to the functions have been pa...
-
Inheritance A key feature of OOP is reusability. It's always time saving and useful if we can reuse something that already exists rather...
No comments:
Post a Comment