Types of Server Data Replication Tools
Replication is a set of technologies that are used for copying and distributing data and database objects from one database to another and then synchronizing between databases to ensure consistency. Replication can be used in several ways to distribute data to different locations and mobile or remote users – over local and wide area networks, wireless connections, the Internet, and dial-up connections. So what is the difference between replication and mirroring since both are closely linked to copying data in a DBMS? The main difference is that mirroring refers to copying database to another location while replication includes copying of data and database objects from one database to another database. Both replication and mirroring have their advantages and increase the performance of the data or the database. There are primarily three types of SQL server replication tools Snapshot Replication – It is a simple process whereby a “snapshot” of the data on one server is taken and the data is moved to another server or another database on the same server. After the first synchronization snapshot, replication refreshes the data in published tables over fixed pre-programmed periods. This technology is the easiest to set up and maintain but on the flip side, all data has to be copied each time a table is refreshed. In between scheduled refreshes, data on the publisher might be different from that on the subscriber. In a nutshell, snapshot replication is emptying the destination tables and importing data from the source using a DTS package.
Transactional Replication – This tool copies data from the publisher to the subscriber/s once and then delivers transactions to the subscriber/s as and when they occur on the publisher. The first copy of the data is transmitted through the same process as snapshot replication. Subsequently, when database users insert, update, or delete records on the publisher, transactions are forwarded to the subscriber/s. Further, the main advantage here among other SQL server replication tools is that by making a simple configuration change, transactions can be delivered continuously. Typically, database servers on transactional publications do not modify data and use it for read-only purposes. Merge Replication – This technology merges data from multiple sources into a single central database. As in transactional replication, this tool also synchronizes data initially by taking a snapshot on the publisher and moving it to subscribers. But unlike transactional replication, merge replication allows changes of the same data on publishers and subscribers, even when the subscribers are not connected to the network. When they connect to the network, replication will notice and combine changes from all subscribers and change data on the publisher accordingly. This tool is useful when data on remote computers have to be modified and when subscribers are not assured of continuous connection to the network. These are some of the most-used SQL server replication tools.
