SQL Replication technology is designed to copy and distribute database data and objects among different databases and to perform synchronization among them. By using SQL replication, you can distribute your database data to different locations, as many as you need.
Why implement SQL replication?
There are many reasons why your organization may want to implement replication:
Redundancy. When your primary database fails for some reasons (hardware failure, software crash, etc), replication allows you to use another separate physical database server to take over.
Load balancing. Replcation allows you to share the access load among some identical database servers.
Offline processing. You may want to manipulate data locally when it is not connected to the network.
Replication main components
There are two replication terms:
Publisher is the database server which offers data for distribution.
Subscriber is the database server which consumes the data distributed from the Publisher.
MS SQL Server Replication
MS SQL Server supports 3 types of replication models.
Merge replication allows both publisher and subscriber make changes to their respective databases independently. The merge replication will merge the changes in both servers. If the changes conflict, the merge replication will use the predefined conflict plan to resolve the issue.
Transactional replication is more flexible if the databases have regular changes. The replication agent will watch the servers, if the publisher has changes, it will distribute them to the subscribers.
Snapshot replication is very time and resource consuming process, so it is fewly used. Snapshot replication simply takes a snapshot of the publisher at a certain time and distributed it to its subscribers.