In SQL Server, replication is the process of copying and distributing data and database objects from one database to one or more other databases. There are three types of replication supported in SQL Server:
## 1. Snapshot Replication:
Snapshot replication distributes a copy of the entire database or the specific objects at a specific moment in time to one or more subscribers. This type of replication is typically used for data warehouses or reference data that doesn’t change frequently. In snapshot replication, the entire snapshot of the published database is copied over to the subscriber(s) at scheduled intervals.
When a snapshot is generated and delivered to a subscriber, the snapshot represents a completely independent copy of the original database. After that, changes made to the original database are not propagated to the subscriber unless a new snapshot is created and delivered.
## 2. Transactional Replication:
Transactional replication is used to keep copies of the data synchronized between the publisher and subscribers continuously. This type of replication is used in cases where the subscriber data needs to be updated soon after the publisher data is changed, and the subscriber data may be cached in memory for fast access.
Transactional replication works by tracking the changes made to the publisher database and forwarding those changes to the subscribers. A transactional replication system has three main components:
- Publisher: The database that provides the data and tables to replicate.
- Distributor: An application that moves the data between the publisher and the subscribers.
- Subscriber: The database that receives the replicated data.## 3. Merge Replication:
Merge replication is used to replicate data between two or more SQL Server databases and it is suited for applications where both the publisher and subscriber require updating the same data frequently but independently. In Merge replication, data is modified at both the publisher and subscribers.
In the case of conflicts, the changes made at the subscriber will have priority, and changes made on both sides are combined during synchronization. Merge replication requires at least one column in each table to uniquely identify a row, and all changes to the primary key values must be propagated to all subscribers.
In summary, SQL Server supports three types of replication to distribute data and database objects between different databases: Snapshot Replication, Transactional Replication, Merge Replication. The choice of the type of replication is crucial in determining the performance, maintenance and scalability of the database.